This forum is currently locked. You can't register or post questions at this time. (read more)

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

All posts relating to Oracle database administration.

Moderator: Tim...

Senior Member
Posts: 123
Joined: Thu Mar 17, 2011 6:27 am

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

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

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:

Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 3 guests