Truncate tables that have a foreign key

A script to truncate tables that have foreign keys referencing them. It also keeps one record in the table, the one with ID=0. In this case, each table has this field as a primary key.

Finally, some tables are not to be truncated. The readible names of these tables are in a metadata table called TDWM_TABLES. The readible name is in the field VIEWNAME.

The process is:

- disable constraints on the selected tables, i.e. with names like 'TDWH_%'
- create a new intermediate table via CTAS that contains the records to keep, i.e. with ID=0
- truncate the table
- copy records back from the intermediate table
- drop intermediate table
- enable constraints on the selected tables, i.e. with names like 'TDWH_%'. Note: enable PK constraints before enabling the FK constraints!

Here is the script:

/* SCRIPT */

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN

-- Disable constraints
DBMS_OUTPUT.PUT_LINE ('-> Disabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' CASCADE' as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%')
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;

-- Truncate tables
DBMS_OUTPUT.PUT_LINE ('-> Truncating tables');
FOR reg IN (
SELECT
'CREATE TABLE Z_' || TABLE_NAME || ' AS SELECT * FROM ' || TABLE_NAME || ' WHERE ID=0' AS ST1,
'TRUNCATE TABLE ' || TABLE_NAME AS ST2,
'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM Z_' || TABLE_NAME AS ST3,
'DROP TABLE Z_' || TABLE_NAME AS ST4
FROM USER_TABLES t
INNER JOIN TDWM_TABLES b ON UPPER(t.TABLE_NAME) = UPPER(b.TABLENAME)
WHERE t.TABLE_NAME LIKE 'TDWH_%' AND
UPPER(b.VIEWNAME) NOT IN ('TDAYOFWEEK','TMONTHOFYEAR','TQUARTEROFYEAR')
)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
DBMS_OUTPUT.PUT_LINE (reg.ST2); EXECUTE IMMEDIATE reg.ST2;
DBMS_OUTPUT.PUT_LINE (reg.ST3); EXECUTE IMMEDIATE reg.ST3;
DBMS_OUTPUT.PUT_LINE (reg.ST4); EXECUTE IMMEDIATE reg.ST4;
END LOOP;

-- Enable constraints
DBMS_OUTPUT.PUT_LINE ('-> Enabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%' ORDER BY CONSTRAINT_NAME DESC)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;
END;