OraFAQ Forum: Performance Tuning » Taking more time to Drop interval Partitions

Home » RDBMS Server » Performance Tuning » Taking more time to Drop interval Partitions (Oracle Enterprise,11.2.0.4,Solaris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Taking more time to Drop interval Partitions [message #649861]

Sun, 10 April 2016 05:22

eswararaodba@gmail.com
Messages: 3
Registered: July 2015

Junior Member

we are trying to drop the partitions form one of the interval Partition table which is occupied more space. To drop the partition its taking 6 to 7 mins for each partition.
We have to drop lot of partitions to reclaim the space However its taking lot of time for each partition. Please help us ASAP.

ALTER TABLE PRODTBL12 DROP PARTITION SYS_P12345;
Table altered
Elapsed: 00:06:33.68

It has 3 LOCAL index partitions also on same table. Please find below output for one sample partition.

SQL> SELECT TABLE_NAME "TABLE/INDEX_NAME",PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PRODTBL12' and PARTITION_NAME='SYS_P12345'
UNION
SELECT INDEX_NAME "TABLE/INDEX_NAME",PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME like 'PRODTBL12%' and PARTITION_NAME='SYS_P12345'

TABLE/INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
PRODTBL12 SYS_P12345
PRODTBL12_IDX1 SYS_P12345
PRODTBL12_IDX2 SYS_P12345
PRODTBL12_IDX3 SYS_P12345

Report message to a moderator

Re: Taking more time to Drop interval Partitions [message #649862 is a reply to message #649861]

Sun, 10 April 2016 05:28

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Please help us ASAP.

Please feedback and thank people who helped you in your previous topics ASAP.

Report message to a moderator

Re: Taking more time to Drop interval Partitions [message #649863 is a reply to message #649861]

Sun, 10 April 2016 08:04

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

It is taking you about six minutes to drop a partition, so unless your partitioning uses an interval of less than six minutes, you are indeed releasing space by dropping partitions faster than you are creating them. Where is the problem?

Report message to a moderator

Previous Topic:	How to calculate Index creation time
Next Topic:	ASH data to identify CPU 100%

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 05:43:38 CDT 2025