January 18, 2016

Script to truncate multiple tables using a single query

SQL> create table xyz (id number);
Table created.

SQL> create table t1 as select * from xyz;
Table created.

SQL>BEGIN

 FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

 LOOP

      EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name;

  END LOOP;

END;
/

SQL> select * from xyz;

no rows selected

SQL> select * from t1;

no rows selected

1 comment:

How to Patch Oracle 19c to Release 19.30 on Windows – A Complete Step-by-Step Blog

  As soon as   Oracle released quarterly patch for Jan 2026 , I had to patch one of my Oracle 19c databases from 19.25 to 19.30. Since this ...