How to get the partition name older than 12 hrs in 11gr2.

[shoan]

Hi Team,

I want get all the partition name from a table which is older than 12 hrs in 11gr2 database.
I know in 10g , As I am comparing the PARTITION_NAME column in the table.

Code: Select all

SQL> Select PARTITION_NAME from dba_tab_partitions
  2        where table_owner = 'SCOTT' and table_name = 'EMP'
  3                          and replace(partition_name,'EMP_') < TO_CHAR(SYSDATE-12/24,'YYYYMMDDHH24')
  4                          and replace(partition_name,'EMP_') <> 'DEFAULT'
  5                         order by partition_name ;


But in 11g the partition name like " SYS_ ".So it is not possible to compare the PARTITION_NAME column.
Can you please help me, how to compare the high values column to get the partition name which is older that 12 hrs ?

Thanks in advance
Shoan

[Tim...]

Hi.

Check out my answer to this thread.

viewtopic.php?f=1&t=5584

You can also look at this script.

http://www.oracle-base.com/dba/script.p ... o_date.sql

Cheers

Tim...