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:

Shell Script to Automate Oracle 19c TDE Wallet & sqlnet.ora Backups

  Recently, one of my junior colleague had a requirement to clone a production database. While doing so he faced the following error while o...