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
Shoan
shoan
Senior Member
 
Posts: 122
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

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...
Tim...
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: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
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