Re: dba_waiters.lock_type = "Disk Space Transaction"

From: Steve Smith <ssmith_at_ai.com>
Date: Fri, 14 Sep 2001 23:11:42 -0700
Message-ID: <F001.0038F971.20010914230016@fatcity.com>

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 ;^)

<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

Original Message ---- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: <A title=Jacques.Kilchoer_at_quest.com href="mailto:Jacques.Kilchoer_at_quest.com">Jacques Kilchoer To: <A title=ORACLE-L_at_fatcity.com href="mailto:ORACLE-L_at_fatcity.com">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 <FONT

  size=2>--------------- --------------- --------------------------

MODE_HELD <FONT

  size=2>---------------------------------------- <FONT 
  size=2>MODE_REQUESTED <FONT 
  size=2>---------------------------------------- <FONT 
  size=2>LOCK_ID1 <FONT 
  size=2>---------------------------------------- <FONT 
  size=2>LOCK_ID2 <FONT 
  size=2>---------------------------------------- <FONT

size=2>
 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 Received on Sat Sep 15 2001 - 01:11:42 CDT

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