Drop all tables in a schema

This assumes the tables have no integrity constraints related to a table in another schema. This just loops until all tables are dropped. If you are the nervous type, add an exception handler for the integrity constraint exception, and exit on all others. The only one you see should be related to integrity constraints on tables that reference the one being dropped.

SQL> declare
  2    l_count number := -1;
  3  begin
  4    while true loop
  5      select count(*) into l_count from dba_tables where owner = 'ATG_CORE_QA3';
  6      if l_count != 0 then
  7        for r in (select * from dba_tables where owner = 'ATG_CORE_QA3') loop
  8          begin
  9            execute immediate 'drop table ' || r.owner || '.' || r.table_name || ' purge';
 10          exception
 11            when others then
 12              null;
 13          end;
 14        end loop;
 15      else
 16        exit;
 17      end if;
 18    end loop;
 19* end;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.