Lock Types - Oracle Database Transactions and Locking Revealed - page 36

Now terminate the session and edit the trace file:

EODA@ORA12CR1> disconnect
EODA@ORA12CR1> edit &TRACE

Searching the trace file for the value of Itl , we see there are two transaction slots that have been initialized (even
though there has only been one transaction issued for this table):

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0013.00e.000024be 0x00c000bf.039e.2d --U- 1 fsc 0x0000.01cfa56a
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

The INITRANS value of 1 reported in the data dictionary is most likely a legacy value and it really should display a
value of 2 for more current versions of Oracle.

TM (DML Enqueue) Locks
TM locks are used to ensure that the structure of a table is not altered while you are modifying its contents. For example,
if you have updated a table, you will acquire a TM lock on that table. This will prevent another user from executing
DROP or ALTER commands on that table. If another user attempts to perform DDL on the table while you have a
TM lock on it, he'll receive the following error message:

drop table dept
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

■ In oracle 11 g release 2 and above, you may set DDL_LOCK_TIMEOUT in order to have DDL wait. this is achieved
typically via the ALTER SESSION command. For example, you could issue ALTER SESSION SET DDL_LOCK_TIMEOUT=60;
before issuing the DROP TABLE command. the DROP TABLE command issued would then wait 60 seconds before
returning an error (or it could succeed, of course, as well).

Note

The ORA-00054 message is a confusing message at first, since there is no direct method to specify NOWAIT or WAIT
on a DROP TABLE at all. It is just the generic message you get when you attempt to perform an operation that would be
blocked, but the operation does not permit blocking. As you've seen before, it's the same message you get if you issue
a SELECT FOR UPDATE NOWAIT against a locked row.
The following shows how these locks would appear in the V$LOCK table:

EODA@ORA12CR1> create table t1 ( x int );
Table created.

EODA@ORA12CR1> create table t2 ( x int );
Table created.

EODA@ORA12CR1> insert into t1 values ( 1 );
1 row created.