Database Reference
In-Depth Information
Next, place into a variable named
TRACE
the location and name of the trace file containing the dump information
for the block:
EODA@ORA12CR1> column trace new_val TRACE
EODA@ORA12CR1> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
2 from v$process a, v$session b, v$parameter c, v$instance d
3 where a.addr = b.paddr
4 and b.audsid = userenv('sessionid')
5 and c.name = 'user_dump_dest';
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.