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 Delete Old (Inactive) Patches in Oracle 19c Using OPatch

How I Clean Up Old (Inactive) Patches in Oracle Using OPatch Problem: After multiple Oracle RU patch cycles, old inactive patches acc...