Procedure "CLEANOUT"

Procedure-CLEANOUT.sql

CREATE OR REPLACE PROCEDURE "CLEANOUT" AS

  -- Drop FK restraints (type = ''R') so tables can be dropped in any order
  cursor con_cursor IS
  SELECT rtrim(constraint_name) as conname, rtrim(table_name) as tabname
  FROM user_constraints
  WHERE rtrim(constraint_type) = 'R';

  -- Leave some objects to prevent problems when objects are dropped out of order
  -- Do not drop indexes, they will go with tables
  -- Do not drop package bodies, they will go with packages
  -- Do not drop triggers, they will go with tables
  -- Do not drop this procedure (CLEANOUT)
  cursor obj_cursor IS
     SELECT rtrim(object_name) as objname,
             rtrim(object_type) as objtype
  	 FROM 	user_objects
     WHERE 	object_type <> 'INDEX'
	 AND   	object_type <> 'PACKAGE BODY'
     AND	object_type <> 'TRIGGER'
     AND	object_name <> 'CLEANOUT'
     AND    object_name NOT LIKE ('BIN%');

  con_value con_cursor%ROWTYPE;
  obj_value obj_cursor%ROWTYPE;

BEGIN
  dbms_output.put_line ('CLEANOUT Started ... ');
  OPEN con_cursor;
  dbms_output.put_line ('Dropping FK CONSTRAINTS');
  
  LOOP
    FETCH con_cursor into con_value;
    exit WHEN con_cursor%NOTFOUND;
    dbms_output.put_line ('Dropping FK constraint: ' || con_value.conname);
    EXECUTE IMMEDIATE 'ALTER TABLE ' || con_value.tabname || ' DROP CONSTRAINT ' || con_value.conname || ' CASCADE'; 
  END LOOP;

  CLOSE con_cursor;

  dbms_output.put_line ('Dropping OBJECTS');
  OPEN obj_cursor;
  LOOP
    FETCH obj_cursor into obj_value;
    exit when obj_cursor%NOTFOUND;
    dbms_output.put_line ('Dropping object : ' || obj_value.objtype || ' ' || obj_value.objname);
    EXECUTE IMMEDIATE 'DROP ' || obj_value.objtype || ' ' || obj_value.objname;
  END LOOP;
  CLOSE obj_cursor;
  
  dbms_output.put_line ('Purging recycle bin ...');
  EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
  COMMIT;
  dbms_output.put_line ('... CLEANOUT Completed');
END;
/