Wednesday, June 17, 2009

gen_enable_constraints.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF


PROMPT
PROMPT ***** Enable All Constraints *****
PROMPT

SPOOL enable_constraints.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL enable_constraints.log' FROM dual;
SELECT '' FROM dual;

SELECT '-- foreign keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' ENABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'R'
and status = 'DISABLED'
/


SELECT '-- primary keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' DISABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'P'
and status = 'DISABLED'
/

SELECT '-- unique keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' DISABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'U'
and status = 'DISABLED'
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ********************************
PROMPT
PROMPT Output saved at enable_constraints.sql
PROMPT