Skip to Main Content
  • Questions
  • Truncate Multiple tables in a single line statement.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, kasimvali.

Asked: February 03, 2020 - 7:00 pm UTC

Last updated: November 29, 2023 - 2:02 pm UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

My question: Is it possible to truncate multiple tables in a single line statement?

Firstly, thanks for giving this opportunity to ask a question.

I have developed a code to truncate multiple tables in a single line statement. Please refer the below code.

----create a type and then use it as a input parameter of a procedure.------
create or replace type table_ty is table of varchar2(30);

create or replace PROCEDURE Trunc_Mul_Tab(
    table_names IN table_ty
)
    IS
BEGIN
    FOR i IN table_names.first..table_names.last LOOP
        dbms_output.put_line('Table_name:'||table_names(i) );
        execute immediate 'truncate table '||table_names(i);
        dbms_output.put_line('Table'||table_names(i)|| 'is truncated successfully');
    END LOOP;
END;
/

--By using type to pass multiple input values.
EXEC Trunc_Mul_Tab(table_ty('EMP','DEP','STUDENTS','MARKS')); 


As per above code i have tested in lower environment. Executed Successfully.

I hope this might be very useful to next versions of oracle to set as a pre-defined package.
For example we need to truncate 10 tables for that we have to write 10 truncate statements instead of that we using above code to truncate 10 tables in a single statement.

Please kindly take a look at it. If you have any better development please implement on that.

and Chris said...

It is possible to truncate many tables in one go. Just add the cascade option added in 12c.

This will also remove all rows from any child tables. Provided you declare the foreign keys as ON DELETE CASCADE:

create table t1 ( c1 primary key ) as 
  select level from dual
  connect by level <= 100;
  
create table t2 ( 
  c1 references t1 ( c1 ) 
    on delete cascade 
);

insert into t2 values ( 1 );

truncate table t1
  cascade;
  
select count (*) from t1;

COUNT(*)   
          0 
          
select count (*) from t2;

COUNT(*)   
          0 


Of course, this a dangerous operation! You can't roll truncate back, so you need to take care doing this.

Rating

  (2 ratings)

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

Comments

Truncate multiple tables in a single line statement.

kasimvali, February 05, 2020 - 8:27 pm UTC

As per your scenario we can truncate multiple tables which have a relationship(foreign key with on delete cascade) with other tables only. We cannot truncate multiple tables by using cascade option.

But in my scenario we can truncate any number of tables in a single line statement without relationship(foreign key) with other tables.

For example: If we want to truncate 'N' number of tables without any relationship with other tables cascade option doesn't work.

Please provide me if you have any alternative code to truncate multiples tables in a single line statement.
Chris Saxon
February 06, 2020 - 1:02 pm UTC

There is no way to truncate many, unrelated tables in one statement.

If this is a feature you want added to Oracle Database, submit an enhancement request with a (business) justification for doing it.

Or post it to the Database Ideas forum to gather community support:

https://community.oracle.com/community/groundbreakers/database/database-ideas

A reader, November 28, 2023 - 6:53 am UTC

thank you for your code
Chris Saxon
November 29, 2023 - 2:02 pm UTC

You're welcome

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.