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.
 
 
Search WWH ::




Custom Search