Database DataWarehousing (MOSC)

MOSC Banner

Partition Drop while table is online executes for the operation for longer periods

edited Sep 18, 2015 10:00AM in Database DataWarehousing (MOSC) 7 commentsAnswered

DB version 12.1.0.2 RAC

I have a list range partitioned table. This table has few global partitioned indexes. There are no FK constrained defined on this table. I'm keeping the application online while range partition of this table is being dropped. I have set the ddl_lock_timeout of the system to 1 hour.

Question

1. Any down fall in increasing the ddl_lock_timeout system wide

2. Why does the drop operation take several hours. I was expecting it to run in few minutes. The table has about 150 million records in total and I'm purging 50% of it. I'm using update global indexes clause in drop clause. In AWR I see TM-contension for this table.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center