Drop multiple tables from users schemas

Scenario: To drop, truncate multiple or all tables from user’s schema.

Let’s suppose we’ve got the following tables in user’s schema and we want to drop them all:

SELECT table_name FROM user_tables;

TABLE_NAME                     
------------------------------ 
CUSTOMER                       
PRODUCT                        
TIME                           
GEOGRAPHY                      
TODAYS_SPECIAL_OFFERS          
PURCHASES

6 rows selected

Execute statement which will create set of statements ready to be copied and paste:

SELECT 'drop table '||table_name||' cascade constraints;' FROM user_tables;

'DROPTABLE'||TABLE_NAME||'CASCADECONSTRAINTS;'                 
-------------------------------------------------------------- 
DROP TABLE CUSTOMER cascade constraints;                       
DROP TABLE PRODUCT cascade constraints;                        
DROP TABLE TIME cascade constraints;                           
DROP TABLE GEOGRAPHY cascade constraints;                      
DROP TABLE TODAYS_SPECIAL_OFFERS cascade constraints;          
DROP TABLE PURCHASES cascade constraints;                      

6 rows selected

 

Now, analyse results and copy appropriate statements, paste and execute them against the database:

-- Execute multiple statements
DROP TABLE CUSTOMER cascade constraints;                       
DROP TABLE PRODUCT cascade constraints;                        
DROP TABLE TIME cascade constraints;                           
DROP TABLE GEOGRAPHY cascade constraints;                      
DROP TABLE TODAYS_SPECIAL_OFFERS cascade constraints;          
DROP TABLE PURCHASES cascade constraints;  

-- Results
DROP TABLE CUSTOMER succeeded.
DROP TABLE PRODUCT succeeded.
DROP TABLE TIME succeeded.
DROP TABLE GEOGRAPHY succeeded.
DROP TABLE TODAYS_SPECIAL_OFFERS succeeded.
DROP TABLE PURCHASES succeeded.

 

That’s the way to delete either multiple or all tables from user’s schema.

This approach can also be used in order to truncate multiple tables from user’s schema:

SELECT 'truncate table '||table_name||' drop storage;' FROM user_tables;
 
'TRUNCATETABLE'||TABLE_NAME||'DROPSTORAGE;'                 
----------------------------------------------------------- 
TRUNCATE TABLE CUSTOMER DROP storage;                       
TRUNCATE TABLE PRODUCT DROP storage;                        
TRUNCATE TABLE TIME DROP storage;                           
TRUNCATE TABLE GEOGRAPHY DROP storage;                      
TRUNCATE TABLE TODAYS_SPECIAL_OFFERS DROP storage;          
TRUNCATE TABLE PURCHASES DROP storage;                      
 
6 rows selected

That’s it.