| 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
|
|
|
|
|
|
| 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: 8960
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
