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

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Aashish.

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

Answered by: Chris Saxon - Last updated: September 09, 2020 - 3:14 pm UTC

Category: Database Administration - Version: 11.2.0.4

Viewed 100+ 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 we 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  

More to Explore

Administration

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