How to Delete / Drop All the Tables in One / Multiple Oracle 10g 11g Schema


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.
It avoids the recycle bin objects creation.
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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google