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;