Drop partition (big tables) elapsed time
Hello DBAs,
I was wondering if you could help me with this free space issue.
Currently we have some big partitioned tables, that have started to being recycled. Each partition has +2GB/+0.5GB of data/index.
When we started to recycle partitions three weeks ago, the elapsed time to drop one was 15 to 20 minutes. But, since we have continued recycling space, now its taken more than 5 hours.
The big problem is, since this is taking that long, it has started to affect other processes because of RBS or TEMP segment.
I have noticed that it's not particular of an specific tablespace, because it's now affecting the whole database. Also, it does seem to affect more to index tablespaces than data's.
Do you have any ideas about what can I do to solve this?
Where do you think we might have the first issue to solve?
Where could be the main problem? tablespaces, smon or dictionary?
Thanks, Joe.