Re: locked_mode=3, no open transactions on the table?

NetComrade wrote:
> We had a weird case where a table was locked in locked_mode=3,
> however, when I looked at transactions, there were no transactions on
> the table.
> The lock was causing a truncate to fail on this table.
> The session was a TOAD session from a computer that died.
> As soon as session was killed, truncate could go ahead.
> Am I missing something?
> .......
> We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
> remove NSPAM to email

Hi, NetComrade,

In addition to an explicit LOCK SQL command run on the table (as Jonathan pointed out), another possibility of having a lock without a transaction is a DML with a long-running query as its prerequisite, such as

INSERT INTO YOURTABLE SELECT COLUMNS FROM SOMEBIGTABLE This immediately creates a TM lock in mode 3 on YOURTABLE, but won't create a transaction until the query phase is finished.

Next time when you have this problem, check v$sql.sql_text for the locking session and also its entry in v$session_wait.

Yong Huang
yong321ATyahoo.com Received on Mon Apr 25 2005 - 14:48:20 CDT