-- 办法 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;