How to empty an Oracle database schema

Seems to be easy, but there are some itchy problems.
First, disable all the constraints, but mind the disabled one, don't enable them aftermath. Same for the triggers.
In order to do this, you have to keep them recorded somehow.

drop table my_constraints; --if exists
create table my_constraints as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED,    GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints;

--don't use the "select * from" syntax, and you will avoid a "long 
--datatype" error
--maybe you don't need all  those fields, but it doesn't cost so 
--much to store them

--if you want to empty the recycle bin (three rows bellow you can see why): 

purge recyclebin;


DECLARE
cursor r1 is select * from my_constraints where status='ENABLED' and upper(substr(constraint_name,1,3))<>'BIN'; --recycle bin objects
cursor r2 is select * from user_tables;
BEGIN
  FOR c1 IN r1
  loop
    for c2 in r2
    loop
       if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
       dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable novalidate constraint ' || c1.constraint_name);
         end if;
    end loop;
  END LOOP;
end;
/

--now, the triggers

drop table my_triggers;

create table my_triggers as select TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, COLUMN_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, ACTION_TYPE,
CROSSEDITION, BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW, FIRE_ONCE, APPLY_SERVER_ONLY from user_triggers;

DECLARE
cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN'; 
--remember? recycle bin objects
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('alter trigger '  || c1.trigger_name || ' disable ' );
  END LOOP;
end;
/

--create a table to hold the name of the tables you want to empty them.

drop table empty_tables; --if exists

-- be carefull, do not empty the my_constraints , my_triggers, empty_tables tables

create table empty_tables as select table_name from user_tables  where lower(table_name) not in (.. some list of names..., 'my_constraints' , 'my_triggers' , 'empty_tables');


-- if you want to use other way than "truncate", feel free to do it, but mind the size of the archivelogs and the load of the system resources

DECLARE
cursor r1 is select * from empty_tables;
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('truncate table '  || c1.table_name || ' drop storage ' );
  END LOOP;end;
/


-- now, reinforce the constraints and the triggers:
-- first, the primary constraints, because, otherwise, you will receive an error when you try to
-- enable a FK constraint, referring a non primary key column.

DECLARE
cursor r1 is select * from user_tables where table_name in (select table_name from my_constraints where constraint_type='P');
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('alter  table '  || c1.table_name || ' enable primary key ' );
  END LOOP;
end;
/

-- now, the unique constraints,  same reason from above
declare
cursor r1 is select * from my_constraints where constraint_type='U' and constraint_name not like 'SYS%' order by constraint_name ; 
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
  FOR c1 IN r1
    loop       dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
  END LOOP;
end;
/

-- now, the rest:

declare
cursor r1 is select * from my_constraints where constraint_name not like 'SYS%' order by constraint_name ; 
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
  FOR c1 IN r1
    loop       dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
  END LOOP;
end;
/


-- and, eventually, the triggers:

DECLARE

cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN';
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('alter trigger '  || c1.trigger_name || ' enable ' );
  END LOOP;
end;
/


And that's all! Your schema is empty, except the tables you mentioned when you populate the empty_tables table.

And now drop the unnecessary tables:

drop table my_constraints;
drop table my_triggers;
drop table empty_tables;