Oracle|Oracle 检查 DATE 列 RANGE 分区表已有分区的最大日期时间 Oracle检查DATE列RANGE分区表已有

Oracle 检查 DATE 列 RANGE 分区已有分区的最大日期时间

-- 方法1：SYS.TABPART$ 计算时间日期 WITH THIS_PART AS (SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE (TABLE_OWNER, TABLE_NAME, PARTITION_POSITION) IN (SELECT B.TABLE_OWNER, B.TABLE_NAME, MAX(B.PARTITION_POSITION) - 1 POSITION FROM DBA_TAB_PARTITIONS B JOIN DBA_USERS DU ON B.TABLE_OWNER = DU.USERNAME AND DU.ACCOUNT_STATUS = 'OPEN' WHERE B.TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'MGMT_VIEW', 'SYSMAN', 'SH') GROUP BY B.TABLE_OWNER, B.TABLE_NAME)), ALL_PART AS (SELECT U.NAME USERNAME, O.NAME TABLENAME, O.SUBNAME PARTNAME, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 3, 2), 'XX') - 100 Y1, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 5, 2), 'XX') - 100 Y2, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 7, 2), 'XX') M, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 9, 2), 'XX') D, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 11, 2), 'XX') - 1 HH, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 13, 2), 'XX') - 1 MI, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 15, 2), 'XX') - 1 SS FROM SYS.TABPART$ TP, SYS.OBJ$ O, SYS.USER$ U WHERE TP.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND U.NAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'MGMT_VIEW', 'SYSMAN', 'SH')), TMP AS (SELECT T2.USERNAME, T2.TABLENAME, T2.PARTNAME, TO_DATE(TRIM(TO_CHAR(T2.Y1 * 100 + T2.Y2, '9999') || '-' || TO_CHAR(T2.M, 'FM09') || '-' || TO_CHAR(T2.D, 'FM09') || ' ' || TO_CHAR(T2.HH, 'FM09') || ':' || TO_CHAR(T2.MI, 'FM09') || ':' || TO_CHAR(T2.SS, 'FM09')), 'YYYY-MM-DD HH24:MI:SS') MAX_RANGE FROM THIS_PART T1 JOIN ALL_PART T2 ON T1.TABLE_OWNER = T2.USERNAME AND T1.TABLE_NAME = T2.TABLENAME AND T1.PARTITION_NAME = T2.PARTNAME) SELECT * FROM TMP WHERE MAX_RANGE <= SYSDATE + 365 ORDER BY USERNAME, TABLENAME;

-- 方法2：取值long字段转换后截取字符串，依赖日期写全了如2021-09-16 00:00:00，或2021/09/16 00:00:00 WITH XML AS (SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE (TABLE_OWNER, TABLE_NAME, PARTITION_POSITION) IN (SELECT B.TABLE_OWNER, B.TABLE_NAME, MAX(B.PARTITION_POSITION) - 1 POSITION FROM DBA_TAB_PARTITIONS B WHERE TABLE_OWNER NOT IN (''SYS'', ''SYSTEM'', ''DBSNMP'', ''MGMT_VIEW'', ''SYSMAN'',''SH'') AND TABLE_OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS=''OPEN'') GROUP BY B.TABLE_OWNER, B.TABLE_NAME) ORDER BY 1,2') AS X FROM DUAL), MAXPART AS (SELECT EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/TABLE_OWNER') TABLE_OWNER, EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/TABLE_NAME') TABLE_NAME, EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/PARTITION_NAME') PARTITION, EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/HIGH_VALUE') HIGH_TEXT FROM XML X, TABLE(XMLSEQUENCE(EXTRACT(X.X, '/ROWSET/ROW'))) RWS ORDER BY EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/TABLE_NAME')), DT AS (SELECT TABLE_OWNER, TABLE_NAME, PARTITION, TO_DATE(SUBSTR(M.HIGH_TEXT, INSTR(M.HIGH_TEXT, '20', -3), 19), 'YYYY-MM-DD HH24:MI:SS') RANGE_DT FROM MAXPART M) SELECT * FROM DT WHERE DT.RANGE_DT <= SYSDATE + 365;