You need to check the high_value from dba_tab_partitions.
Or you you could metadata.
With metadata, it is not a long, it either clob or clob-xml.
SELECT t.table_name,
MAX (
TO_DATE (
REGEXP_SUBSTR (
EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
'YYYY-MM-DD HH24:MI;:SS'))
high_value
FROM user_tables t,
TABLE (
XMLSEQUENCE (
EXTRACT (
xmltype (DBMS_METADATA.get_xml ('TABLE', t.table_name)),
'//ROWSET/ROW/TABLE_T/PART_OBJ/PART_LIST/PART_LIST_ITEM/HIBOUNDVAL'))) x
WHERE partitioned = 'YES'
HAVING MAX (
TO_DATE (
REGEXP_SUBSTR (
EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
'YYYY-MM-DD HH24:MI;:SS')) < DATE '2015-01-02'
GROUP BY t.table_name
ORDER BY 1;
TABLE_NAME HIGH_VALUE
---------- ------------------------------
T1 2015-01-01
T11 2000-01-01
T20 1436-03-09
T6 2000-01-01
T7 2014-12-21
T8 2015-01-01
Ok, out of xml, I get all HIBOUNDVAL and hazardously try some regular expression to find a pattern.
It already returned most of my table that could have caused me New Eve headache. But partitioned is not that simple. Line 3 for instance is not in a gregorian format (it's a bug). And it does not cover index partition, subpartitions, interval partitions, neither partition with more than one date column as key.
This sounds a lot, and there is only one HIGH_VALUE for multiple key, and the High_value is something like 0,TO_DATE(' 3543-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),MAXVALUE
Okay, if you forget about non-gregorian calendars, and ignore about MAXVALUE, we could use something like
curid := DBMS_SQL.open_cursor;
txt :=
'select '
|| REPLACE (g.high_value, 'MAXVALUE', 'NULL')
|| ' from dual';
DBMS_SQL.parse (curid, txt, DBMS_SQL.NATIVE);
Then from dba_PART_KEY_COLUMNS (pkc) or dba_SUBPART_KEY_COLUMNS, joined with dba_tab_columns, we could retrieve the datatype (date and timestamp with or without time zone) and the pkc.column_position.
DBMS_SQL.DEFINE_COLUMN (curid,
f.column_position,
'date',
12);
Casting to date should be fine.
Then we fetch and retrieve the Nth column
IF DBMS_SQL.FETCH_ROWS (curid) > 0
THEN
DBMS_SQL.COLUMN_VALUE (curid, f.column_position, namevar);
END IF;
If you read me that far, you probably can write the rest of the code for yourself.
Could be useful to do this before end-of-year 😉