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

All posts relating to Oracle database administration.

Moderator: Tim...

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

Postby shoan » Sat Jun 30, 2012 4:56 pm

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
Senior Member
Posts: 123
Joined: Thu Mar 17, 2011 6:27 am

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

Postby Tim... » Sat Jun 30, 2012 7:46 pm


Check out my answer to this thread.


You can also look at this script. ... o_date.sql


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:
Site Admin
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 2 guests