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.
