Home » RDBMS Server » Performance Tuning » Taking more time to Drop interval Partitions (Oracle Enterprise,11.2.0.4,Solaris)
Taking more time to Drop interval Partitions [message #649861] Sun, 10 April 2016 05:22 Go to next message
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
Re: Taking more time to Drop interval Partitions [message #649862 is a reply to message #649861] Sun, 10 April 2016 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65384
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please help us ASAP.


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

Re: Taking more time to Drop interval Partitions [message #649863 is a reply to message #649861] Sun, 10 April 2016 08:04 Go to previous message
John Watson
Messages: 7259
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?

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


Current Time: Mon Feb 19 22:39:41 CST 2018

Total time taken to generate the page: 0.11531 seconds