Re: dba_waiters.lock_type = "Disk Space Transaction" - significance?

[Steve Smith]

Title: dba_waiters.lock_type = "Disk Space Transaction" - significance?

Analyzing a table (analyze...estimate/compute statistics) will put a lock on the library cache object preventing any ddl changes, but any dml should be okay.   Analyzing to validate the structure of a table(analyze ...validate structure) puts an exclusive lock on the table and prevents any other sessions from doing any dml.    Are you analyzing the indexes?  If so, that puts a share lock on the underlying table and that prevents any dml until the lock is released.  Any sessions wanting to update, insert, delete on that table will wait until the analyze is complete..   Steve Smith Unemployed DBA ;^)   ----- Original Message ---- From: Jacques Kilchoer To: Multiple recipients of list ORACLE-L Sent: Friday, September 14, 2001 9:30 PM Subject: dba_waiters.lock_type = "Disk Space Transaction" - significance? I have a background process running that's doing an analyze on tables, and storing the information in another table. The table in which it's trying to store information has currently reached the maximum number of extents (I'm doing a test to see how the background job handles ORA- errors, so far not very gracefully.) Now other sessions are "stuck" behind the one session doing the analyze. What could be the causes for this lock_type? ("disk space transaction") Oracle 8.1.7 - Windows 2000 SQL> select * from dba_waiters ; WAITING_SESSION HOLDING_SESSION LOCK_TYPE --------------- --------------- -------------------------- MODE_HELD ---------------------------------------- MODE_REQUESTED ---------------------------------------- LOCK_ID1 ---------------------------------------- LOCK_ID2 ----------------------------------------              12              21 Disk Space Transaction Exclusive Exclusive 0 0              17              21 Disk Space Transaction Exclusive Exclusive 0 0              20              21 Disk Space Transaction Exclusive Exclusive 0 0