How to: I need an empty Database – Part 2

In the first part of his post I showed the way to use IMP/EXP utilities from Oracle to get the DDL of a single schema or an entire database.  Some people don’t fully trust these utilities or just dislike the command line and prefer to work over procedures.  This post will give you another option to get a clean database using the information of the data dictionary on any DBMS or the user_<whatever> tables or the all_<whatever> tables from Oracle.
So, here we go.

Oracle

In particular, for the Oracle DBMS, I’ll use a package.  Let’s call it SCHEMA_CLEANER.  Now, we have to state what do we need?  For any database (that respects the relations between tables and data within those tables), the problems when you have to delete some data begin with the foreign keys being violated.
This indicates that the first thing to do is to DISABLE all those constraints so that we can delete all data in the schema without having to take core of the order we’re deleting that data.  But of course, we have later to take the time to enable all those constraints again, because we want to work with a consistent schema, and leaving the constraints disabled will lead almost for sure to inconsistent data.
Following, this we got 2 specifications for our package: DISABLE_FKS and ENABLE_FKS.
Finally, we need the procedure that will do the bulk delete.  So that’s one more specification for the package, let’s say: DELETE_DATA.
For his example I’ll be running the package procedures from the schema we want to clean.  Nevertheless, this code can be easily modified to be run as a DBA from the SYSTEM schema passing the schema to clean up as a parameter.
In this code, then I’ll use the user_<whatever> tables to get the info I need.  The mod would be to use the all_<whatever> tables and filter through the OWNER field in each table.
So, here is the code:

CREATE OR REPLACE PACKAGE SCHEMA_CLEANER AS

PROCEDURE DISABLE_FKS;
PROCEDURE ENABLE_FKS;
PROCEDURE DELETE_DATA;
PROCEDURE CLEAN_SCHEMA;

END SCHEMA_CLEANER;
/


CREATE OR REPLACE PACKAGE BODY SCHEMA_CLEANER AS

PROCEDURE DISABLE_FKS AS
ctable user_tables.table_name%TYPE;
fkey user_constraints.constraint_name%TYPE;

CURSOR tableList IS
SELECT table_name
FROM user_tables;

CURSOR FKList IS
SELECT constraint_name
FROM user_constraints
WHERE table_name = ctable
AND constraint_name LIKE '%FK%';

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('About to disable foreign keys...');
OPEN tableList;

FETCH tableList INTO ctable;

WHILE tableList%FOUND
LOOP
dbms_output.put_line('Table found: ' || ctable);
OPEN FKList;
FETCH FKList INTO fkey;
WHILE FKList%FOUND
LOOP
EXECUTE IMMEDIATE ('ALTER TABLE ' || ctable || ' DISABLE CONSTRAINT ' || fkey);
dbms_output.put_line('Foreign Key ' || fkey || ' disabled.');
FETCH FKList INTO fkey;
END LOOP;
CLOSE FKList;
FETCH tableList INTO ctable;
END LOOP;    
CLOSE tableList;
dbms_output.put_line('Foreign keys disabled.');
END DISABLE_FKS;

PROCEDURE ENABLE_FKS AS
ctable user_tables.table_name%TYPE;
fkey user_constraints.constraint_name%TYPE;

CURSOR tableList IS
SELECT table_name
FROM user_tables;

CURSOR FKList IS
SELECT constraint_name
FROM user_constraints
WHERE table_name = ctable
AND constraint_name LIKE '%FK%';

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('About to enable foreign keys...');
OPEN tableList;

FETCH tableList INTO ctable;

WHILE tableList%FOUND
LOOP
dbms_output.put_line('Table found: ' || ctable);
OPEN FKList;
FETCH FKList INTO fkey;
WHILE FKList%FOUND
LOOP
dbms_output.put_line('Foreign Key found: ' || fkey);
EXECUTE IMMEDIATE ('ALTER TABLE ' || ctable || ' ENABLE CONSTRAINT ' || fkey);
dbms_output.put_line('Foreign Key ' || fkey || ' enabled.');
FETCH FKList INTO fkey;
END LOOP;
CLOSE FKList;
FETCH tableList INTO ctable;
END LOOP;    
CLOSE tableList;
dbms_output.put_line('Foreign keys enabled.');
END ENABLE_FKS;

PROCEDURE DELETE_DATA AS
thetable user_tables.table_name%TYPE;

CURSOR ctable IS
SELECT table_name
FROM user_tables;

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('Starting data deletion process...');
OPEN ctable;
FETCH ctable INTO thetable;
dbms_output.put_line('Deleting, please wait...');
WHILE ctable%FOUND LOOP
dbms_output.put_line('Table found: '|| thetable);
dbms_output.put_line('Deleting data in '|| thetable);
EXECUTE IMMEDIATE ('DELETE FROM ' || thetable);
dbms_output.put_line(thetable || ' is empty.');
FETCH ctable INTO thetable;
END LOOP;
CLOSE ctable;
dbms_output.put_line('Data deletion process finished.');
END DELETE_DATA;

PROCEDURE CLEAN_SCHEMA AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SCHEMA_CLEANER.DISABLE_FKS;
SCHEMA_CLEANER.DROP_DYNAMIC_TABLES;
SCHEMA_CLEANER.DELETE_DATA;
SCHEMA_CLEANER.ENABLE_FKS;
END CLEAN_DB;

END SCHEMA_CLEANER;

/