Need to delete all tables based on their references i.e FK and PK

Question

I need to create a script to clean up all the objects in the schema 'myschema'. Order will be like

1. All tables(FK PK order)

How do I achieve this in one PLSQL block.

 set serveroutput on;
declare
lv_str varchar2(1000);
begin
for c in(select distinct a.table_name as table_name, b.table_name as parent_table_name
from
all_constraints a
left outer join all_constraints b on a.r_constraint_name = b.constraint_name and a.owner = b.owner) loop
lv_str :='DROP TABLE '||c.table_name;
--lv_str :='DROP TABLE '||c.parent_table_name;

dbms_output.put_line(lv_str);
end loop;
end;

Answer 1

This is a script I use to clear Scott's schema. Why? I use it for testing purposes and, as time goes by, it turns into a mess. I don't pay much attention about what I remove and in which order; I simply run it 2-3 times and everything's gone. See if you can use it, improve it if you want.

SET SERVEROUTPUT ON;

DECLARE
   l_user  VARCHAR2 (30) := 'SCOTT';
   l_str   VARCHAR2 (200);
BEGIN
   IF USER = l_user
   THEN
      FOR cur_r IN (SELECT object_name, object_type
                      FROM user_objects
                     WHERE object_name NOT IN ('EMP',
                                               'DEPT',
                                               'BONUS',
                                               'SALGRADE'))
      LOOP
         BEGIN
            l_str :=
                  'drop '
               || cur_r.object_type
               || ' "'
               || cur_r.object_name
               || '"';
            DBMS_OUTPUT.put_line (l_str);

            EXECUTE IMMEDIATE l_str;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;
   END IF;
END;
/

PURGE RECYCLEBIN;

SELECT * FROM user_objects;

---

How to drop foreign key constraints first, and then drop tables:

SQL> set serveroutput on
SQL> declare
  2    l_str varchar2(200);
  3  begin
  4    for cur_r in (select table_name, constraint_name
  5                  from user_constraints
  6                  where constraint_type = 'R')
  7    loop
  8      l_str :=  'alter table ' || cur_r.table_name ||
  9                        ' drop constraint '  || cur_r.constraint_name;
 10      dbms_output.put_line(l_str);
 11
 12      execute immediate l_str;
 13    end loop;
 14
 15    --
 16
 17    for cur_r in (select table_name from user_tables where table_name not in ('EMP', 'DEPT'))
 18    loop
 19      l_str := 'drop table ' || cur_r.table_name;
 20
 21      dbms_output.put_line(l_str);
 22
 23      execute immediate l_str;
 24    end loop;
 25  end;
 26  /
alter table TDET drop constraint SYS_C00105662
drop table BONUS
drop table SALGRADE
drop table TEST
drop table MYTABLE
drop table TABLEB
drop table TABLEA
drop table EMPLOYEES
drop table T_PRINT
drop table TMAS
drop table TDET

PL/SQL procedure successfully completed.

SQL>