How To Truncate Tables In A Schema With Referential Integrity

Often we need to clean a schema, keeping the tables, indexes, etc. it is necessary to truncate the tables, the problem comes when exists referential integrity between tables in the scheme, in this case we will not see referential integrity between different schemes. Used by the truncate is faster than the delete, not fire the trigger, the redo and undo is minimal. Of course, there are other options like making a export and import without rows, this is just one more option, depending on the case which can be adjusted more or less to our needs.

First of all, let the script to generate the disable and enable sentences for the constraints, we will use the following script;

BEGIN
-- GENERATE SCRIPT TO DISABLE CONSTRAINTS ----
FOR c IN (SELECT table_name, constraint_name FROM dba_constraints WHERE owner ='INTRANET' and constraint_type = 'R')
LOOP
DBMS_OUTPUT.PUT_LINE ('alter table publicidad.' || c.table_name || ' disable constraint ' || c.constraint_name || ';');
END LOOP;

-- GENERATE SCRIPT TO ENABLE CONSTRAINTS ----
FOR c IN (SELECT table_name, constraint_name FROM dba_constraints WHERE owner ='INTRANET' and constraint_type = 'R')
LOOP
DBMS_OUTPUT.PUT_LINE ('alter table publicidad.' || c.table_name || ' enable constraint ' || c.constraint_name || ';');
END LOOP;
END;
/

Once we have it, we have to disable all constraints that we have obtained with the execution of the PL/SQL anonymous above. Note that we run the script to disable the constraints several times, this is because they may not all matching disable, for example by having a circular referential integrity.

At this point we can execute truncate all table or subset of tables that interests us. If the truncate is a subset that we must remember the model and you might have problems to raise some constraints by violating integrity. We can use the following script to generate the truncated;

BEGIN
-- GENERATE SCRIPT TO TRUNCATE ALL TABLES ----
FOR c IN (SELECT table_name FROM dba_tables WHERE owner ='INTRANET')
LOOP
DBMS_OUTPUT.PUT_LINE ('truncate table ' || c.table_name || ';' );
END LOOP;

END;
/

By putting enable the constraints you may also need to run the script several times. It would also be good idea to review all the constraints associated with the schema and type ‘R’ are enable.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s