Skip to Main Content
  • Questions
  • Lock on table while truncating after gather stats

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: April 21, 2021 - 9:40 am UTC

Last updated: April 21, 2021 - 3:12 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

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

and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database