Ensure your day/month/year partitions for the 1st Jan 2013 are present!:
with date_partitions as (select dbms_xmlgen.getxmltype(' select p.table_owner, p.table_name, p.high_value from all_part_key_columns k, all_tab_cols c, all_tab_partitions p where k.owner = c.owner and k.column_name = c.column_name and k.name = c.table_name and k.owner = p.table_owner and k.name = p.table_name and (c.data_type = ''DATE'' or c.data_type like ''TIMESTAMP%'')') as xml from dual) select t.owner, t.table_name from all_tables t where t.partitioned = 'YES' -- exclude interval partitoned tables, -- thanks to Matthias Rogel and not exists ( select null from dba_part_tables dpt where dpt.owner = t.owner and dpt.table_name = t.table_name and dpt.interval is not null ) and exists (select null from all_part_key_columns k, all_tab_cols c, all_tab_partitions p where k.owner = c.owner and k.column_name = c.column_name and k.name = c.table_name and k.owner = t.owner and k.name = t.table_name and (c.data_type = 'DATE' or c.data_type like 'TIMESTAMP%') ) and not exists ( select x.* from date_partitions p, xmltable('/ROWSET/ROW' passing p.xml columns table_owner varchar2(30) path '/ROW/TABLE_OWNER', table_name varchar2(30) path '/ROW/TABLE_NAME', high_value varchar2(30) path '/ROW/HIGH_VALUE' ) x where x.table_owner not like '%SYS%' and (x.high_value like '%MAXVALUE%' or to_date(substr(high_value, instr(high_value, '''')+2, 19), 'yyyy-mm-dd hh24:mi:ss') > date'2013-01-01') and x.table_name = t.table_name and x.table_owner = t.owner );
Somehow I’ve not managed to see a query to do this before; probably in part because the HIGH_VALUE is annoying stored in a long. Note this query may take some time to execute if you’ve got a large number of (date) partitioned tables in your database.
Thanks to Adrian Billington’s article on long conversions for providing the inspiration for converting this to XML.