Skip to Main Content
  • Questions
  • Is truncate table operation creates lock on DML operations

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Aashish.

Asked: September 08, 2020 - 4:07 pm UTC

Last updated: September 09, 2020 - 3:14 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Team,

Can you please confirm me whether I can perform truncate partitions operation on critical transaction table of application. Table is heavily used by application and performing DML operations.

We want to truncate old partitions, after take export backup, Can we perform this operation while Application is UP and live... or we need to downtime application.

Oracle database version 11.2.0.4.

regards,
Aashish

and Chris said...

Yes, with a but.

Truncate partition is a blocking DDL operation. This means that there can be no uncommitted transactions against the partition you're truncating.

I'm guessing you're only truncating old partitions subject to no data changes. I which case you can go ahead - you can continue to write changes to all the other partitions.

Just be aware that by default truncate partition leaves any global indexes in an unusable state:

create table t (
  c1 int, c2 int
) partition by range ( c1 ) 
  interval ( 10 ) (
    partition p0 values less than ( 11 ) 
  );
create index i on t ( c2 );
  
insert into t 
with rws as (
  select level x from dual
  connect by level <= 100
)
  select x, x from rws;
commit;

alter table t
  truncate partition p0;
  
select index_name, status 
from   user_indexes;

INDEX_NAME   STATUS     
I            UNUSABLE  


Which will probably also leave your application in an unusable state!

To avoid this, add the update global indexes clause:

alter index i rebuild;

alter table t
  truncate partition p0
  update global indexes;
  
select index_name, status 
from   user_indexes;

INDEX_NAME   STATUS   
I            VALID  


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