Sometimes, we want to drop all tables in our schema. To do this, I create the following PL/SQL script file.
Warning!!!!! The script below will remove all tables
under the current schema!!!
begin
for r in (select table_name from user_tables order by table_name)
loop
begin
execute immediate 'drop table '||r.table_name
||' cascade constraints purge';
exception when others then null;
end;
end loop;
end;
/
Warning!!!!! The script above will remove all tables
under the current schema!!!
I saved it as file this_will_drop_all_user_tables.sql. I logged in my schema using sqlplus and run the scripts.
$sqlplus myschema/mypassword SQL> @this_will_drop_all_user_tables PL/SQL procedure successfully completed.All the tables are removed under the current schema. Again, please be careful before run the script! It will remove all tables under the current schema.