Droping a table in oracle is very simple task. With just one row of sql statement you can drop a table. For example you want to drop [cci]school[/cci] table in database. Use this simple sql statement, the table will be dropped.
DROP TABLE school;
Dropping multiple tables
If you have multiple tables to drop, trivial way is adding the above row as much as total of tables. How if you have hunderds table to dropped? It’s no problem if you want to copy and paste the above statement and change the table name line by line. If you are lazy to do that, I will give some tricky way to generate the sql statement without copy paste then change the table name.
By using String Concatenation we can generate sql statement to drop multiple tables. If you are not familiar with string contatenation, I recommend you to read my previous tutorial string concatenation.
SELECT 'DROP TABLE ' || table_name || ';' from user_tables;
If you run the above statement, you will give result like this. The table name is depend on user currently logged in.
DROP TABLE P_SCHOOL; DROP TABLE P_CLASS; DROP TABLE P_DISK; DROP TABLE P_CAT; DROP TABLE P_STUDENT; DROP TABLE P_RES; DROP TABLE P_KIT; DROP TABLE R_SCHOOL; DROP TABLE R_MAS; DROP TABLE R_KIT; DROP TABLE R_ANG; DROP TABLE R_GUS; DROP TABLE R_AFTER; DROP TABLE R_BEFORE; DROP TABLE R_NIGHT; DROP TABLE R_TODAY; DROP TABLE R_COLUMN; DROP TABLE P_LANG; DROP TABLE P_CUBE; DROP TABLE P_GUN; DROP TABLE P_OPEN; DROP TABLE P_CLOSE; DROP TABLE P_MISS; DROP TABLE P_CON; DROP TABLE P_RIP; DROP TABLE P_JEWEL;
Now you can copy paste the result statement and then execute it.
Generating statement conditionally
When generating sql statement, you can filter the generated statement to meet your requirement. For example, if you want to drop only table with suffix “P”. You can put where clause.
SELECT 'DROP TABLE ' || table_name || ';' FROM user_tables WHERE table_name LIKE 'P%';
The above statement will give result:
DROP TABLE P_SCHOOL; DROP TABLE P_CLASS; DROP TABLE P_DISK; DROP TABLE P_CAT; DROP TABLE P_STUDENT; DROP TABLE P_RES; DROP TABLE P_KIT; DROP TABLE P_LANG; DROP TABLE P_CUBE; DROP TABLE P_GUN; DROP TABLE P_OPEN; DROP TABLE P_CLOSE; DROP TABLE P_MISS; DROP TABLE P_CON; DROP TABLE P_RIP; DROP TABLE P_JEWEL;
