Hello Tom,
Could you please help me in below case.
There is a script that has Gather stats and truncate on PROCESSRES.
BEGIN
DBMS_STATS.gather_table_stats
(ownname => 'STSC',
tabname => 'PROCESSRES',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all indexed columns size skewonly',
CASCADE => TRUE,
FORCE => TRUE,
DEGREE => 4
);
DBMS_STATS.delete_column_stats (ownname => 'STSC',
tabname => 'PROCESSRES',
colname => 'PROCESSID',
FORCE => TRUE
);
DBMS_STATS.lock_table_stats (ownname => 'STSC',
tabname => 'PROCESSRES');
END;
END;
/
truncate table STSC.PROCESSRES DROP STORAGE;
exit;
I am getting error as
truncate table STSC.PROCESSRES DROP STORAGE
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
Thanks in advance!
Regards,
Sana
Another session is holding a lock on your table, which is stopping the truncate from getting the exclusive lock it needs.
There are many things that could be causing this, but a simple one is uncommitted DML.
For example, if I run this in session one:
create table t (
c1 int
);
insert into t values ( 1 );
Note the insert is uncommitted. In session two I run:
truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
This has to wait for the insert to commit/rollback. To give the other transactions some time to complete, you can increase the DDL timeout with:
alter session set ddl_lock_timeout = 300;
The truncate will now wait 300 seconds (5 minutes) for other transactions to complete. This is likely more than enough time to enable the truncate to execute or give you time to diagnose what's blocking this.
For example, after increasing the timeout and re-running the truncate with the first insert still uncommitted, I can see this in dba_dml_locks:
select * from dba_dml_locks;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTED LAST_CONVERT BLOCKING_OTHERS
223 CHRIS T None Exclusive 2 Not Blocking
230 CHRIS T Row-X (SX) None 479 Blocking
The first session (230) is blocking the truncate (223) from completing.