This content has been marked as final. Show 6 replies
Because some other session has a lock on that table, you can delete it because you are in fact deleting your read consistent view of the table. See this test case:
In session 1:
In session 2:
YAS@10G>create table t as select * from dual; Table created. YAS@10G>insert into t values(2); 1 row created.
I was not able to truncate it because session 1 inserted a row and it is holding a lock on the table. I could delete from the table because the rows I want to delete are not locked.
YAS@10G>truncate table t; truncate table t * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified YAS@10G>delete from t; 1 row deleted. YAS@10G>commit; Commit complete. YAS@10G>select * from t; no rows selected
TRUNCATE is DDL and it needs an exclusive lock on the table. Session 1 is holding a shared lock on the table which does not permit the other session to get an exclusive lock.
Great Explanation. Thank you very much Yas.
The main reason is because of TM lock. When an session has open transaction it's hold two locks, TM and TX,
Row Locks (TX)
Row-level locks are primarily used to prevent two transactions from modifying the same row.
Table Locks (TM)
Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation.
truncate is DDL not DML
more info here
How do i identify TM lock and avoid the waiting time?
select * from v$locked_object where object_id = xyz
(you get object_id from dba_objects)
You can explicitly lock a table with
lock table schema.table in exclusive mode nowait;
with the keyword "nowait" you force not to wait for (other) locks
select * from v$locked_object
and there is column locked_mode