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.<\/p>\n
\r\nSQL> declare\r\n 2 l_count number := -1;\r\n 3 begin\r\n 4 while true loop\r\n 5 select count(*) into l_count from dba_tables where owner = 'ATG_CORE_QA3';\r\n 6 if l_count != 0 then\r\n 7 for r in (select * from dba_tables where owner = 'ATG_CORE_QA3') loop\r\n 8 begin\r\n 9 execute immediate 'drop table ' || r.owner || '.' || r.table_name || ' purge';\r\n 10 exception\r\n 11 when others then\r\n 12 null;\r\n 13 end;\r\n 14 end loop;\r\n 15 else\r\n 16 exit;\r\n 17 end if;\r\n 18 end loop;\r\n 19* end;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n