Database Tuning (MOSC)

MOSC Banner

why would "shrink space casdcade" on a table cause hours of TM enqueue locking on that table?

edited Sep 1, 2010 7:07AM in Database Tuning (MOSC) 6 commentsAnswered ✓
 Database Enterprise Edition 10.2.0.3 on x86_64 RHEL4.

------------------------------------------------

I thought locking during an online shrink was supposed to be limited to row level locks (that is except for a table lock at the last moment when the high water mark is moved).  But that's not what we're seeing.  We're seeing a table get locked up with TM enqueues for hours, which is resulting in ridiculous wait times for any sql sessions that attempt to update the table.  We sampled a 3 hour period while the shrink was running, and results show the shrink session was responsible for over 2 hours of wait out of the 3 hour sample

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