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

Answered by: Chris Saxon - Last updated: February 06, 2020 - 1:02 pm UTC

Category: Database Development - Version: Oracle 11g

Viewed 100+ times

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 we 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.

and you rated our response

  (1 rating)

Reviews

Truncate multiple tables in a single line statement.

February 05, 2020 - 8:27 pm UTC

Reviewer: kasimvali from India

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

Followup  

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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.