/* Formatted on 5/28/2018 2:54:17 PM (QP5 v5.149.1003.31008) */
DECLARE
TYPE T_V_SCHEMA IS TABLE OF VARCHAR2 (20);
V_SCHEMA T_V_SCHEMA;
BEGIN
V_SCHEMA := T_V_SCHEMA ('TEST1','TEST2','TEST3'); --Input Schema Name
FOR L_V_SCHEMA IN 1 .. V_SCHEMA.COUNT --Loop schema in the list
LOOP
FOR L_V_TABLE
IN (SELECT TABLE_NAME, CONSTRAINT_NAME --Disable first the foreign key
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
AND STATUS = 'ENABLED'
AND OWNER = V_SCHEMA (L_V_SCHEMA))
LOOP
-- EXECUTE IMMEDIATE ' ALTER TABLE '
-- || V_SCHEMA (L_V_SCHEMA)
-- || '.'
-- || L_V_TABLE.TABLE_NAME
-- || ' DISABLE CONSTRAINT '
-- || V_SCHEMA (L_V_SCHEMA)
-- || '.'
-- || L_V_TABLE.CONSTRAINT_NAME;
DBMS_OUTPUT.
PUT_LINE (
'ALTER TABLE '
|| V_SCHEMA (L_V_SCHEMA)
|| '.'
|| L_V_TABLE.TABLE_NAME
|| ' DISABLE CONSTRAINT '
|| L_V_TABLE.CONSTRAINT_NAME || ';');
END LOOP L_V_TABLE;
FOR L_V_TABLE
IN (SELECT TABLE_NAME, CONSTRAINT_NAME -- then disable all constraints
FROM DBA_CONSTRAINTS
WHERE STATUS = 'ENABLED' AND OWNER = V_SCHEMA (L_V_SCHEMA))
LOOP
-- EXECUTE IMMEDIATE 'ALTER TABLE '
-- || V_SCHEMA (L_V_SCHEMA)
-- || '.'
-- || L_V_TABLE.TABLE_NAME
-- || ' DISABLE CONSTRAINT '
-- || V_SCHEMA (L_V_SCHEMA)
-- || '.'
-- || L_V_TABLE.CONSTRAINT_NAME;
DBMS_OUTPUT.
PUT_LINE (
'ALTER TABLE '
|| V_SCHEMA (L_V_SCHEMA)
|| '.'
|| L_V_TABLE.TABLE_NAME
|| ' DISABLE CONSTRAINT '
|| L_V_TABLE.CONSTRAINT_NAME || ';');
END LOOP L_V_TABLE;
FOR L_V_TABLE IN (SELECT TABLE_NAME --Truncate Table
FROM DBA_TABLES
WHERE OWNER = V_SCHEMA (L_V_SCHEMA))
LOOP
-- EXECUTE IMMEDIATE 'TRUNCATE table '
-- || V_SCHEMA (L_V_SCHEMA)
-- || '.'
-- || L_V_TABLE.TABLE_NAME;
DBMS_OUTPUT.
PUT_LINE (
'TRUNCATE table '
|| V_SCHEMA (L_V_SCHEMA)
|| '.'
|| L_V_TABLE.TABLE_NAME || ';');
END LOOP L_V_TABLE;
END LOOP;
END;
/
