Script to delete 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> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
XYZ                            TABLE

SQL>BEGIN

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

 LOOP

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

  END LOOP;

END;
/
SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$KPwTny0vY53gUKjACwIKTg==$0 TABLE
BIN$KPwTny0wY53gUKjACwIKTg==$0 TABLE

No comments:

Post a Comment