Dec 312012
 

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.

  4 Responses to “Last Chance to Avoid Starting 2013 with an Emergency Support Call”

  1. Chris,

    thanks for this useful query !

    However, it also delivers interval-partitioned tables, which we don’t have to worry about, since Oracle automatically maintains them for us.

    So, I added a

    ....
    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
    )

    to your query

    Happy New Year and keep your good work in 2013 !

    Regards,
    Matthias

    • Excellent point Matthias, we’ve not migrated to interval partitions so I’d forgotten about them. I’ll update the post by the weekend with this.

      However, we do have many “date” partitioned tables linking to a date dimension, so the partition key is a number, which this query doesn’t help us with either =/

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)