Database Tuning (MOSC)

MOSC Banner

Locking behavior of TRUNCATE PARTITION yyy UPDATE GLOBAL INDEXES clause

edited Nov 11, 2011 2:20AM in Database Tuning (MOSC) 1 commentAnswered
 Recently we had issues with statements such as:

ALTER TABLE xxx TRUNCATE PARTITION yyy UPDATE GLOBAL INDEXES

My understanding of the documentation is it is supposed to keep a global index usable
despite the partition truncate clause, at the cost of a DDL statement no longer being a quick
data dictionary operation.

Not much is said regarding locking during the operation. I was expecting a TM lock only
on the partition being truncated, and not held during the index maintenance operation,
but the application seems to be blocked on a 'select for update' query on the same
table during the whole operation.

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