advertisements
_____________________________________________________________________________________________________________________
In many cases we may come across the situation to drop all the tables from the one schema or from multiple schemas. You can see different methods to drop the tables from schemas. PURGE option mentioned in the scripts will not generate the recycle bin objects.
PLSQL Script with Dynamic Sql for Single Schema
BEGIN
FOR TNAME IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE ' || TNAME.table_name || ' CASCADE CONSTRAINTS PURGE');
END LOOP;
END;
PLSQL Script with Dynamic Sql for multiple Schemas
You have to run the below script as DBA to drop the table objects from different schemas. Also, you have to add the schema names marked in red color below.
BEGIN
FOR TNAME IN (SELECT owner, table_name FROM dba_tables where owner in ('TEST')) LOOP
EXECUTE IMMEDIATE ('DROP TABLE ' || TNAME.OWNER||'.'||TNAME.table_name || ' CASCADE CONSTRAINTS PURGE');
END LOOP;
END;
SQL script with Spooling Option
set heading off echo off
Spool drop_tables.sql
select 'Drop table ' || table_name || ' CASCADE CONSTRAINTS PURGE;' from user_tables;
spool off
@drop_tables.sql
Example on PLSQL script
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP VIEW
EMP_RELEASE TABLE
SALGRADE TABLE
7 rows selected.
SQL> BEGIN
FOR TNAME IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE ' || TNAME.table_name || ' CASCADE CONSTRAINTS PURGE');
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.86
SQL>
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP VIEW
Elapsed: 00:00:00.01
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment