Oracle Disable all constraints with a subscript

From WickyWiki
Revision as of 07:26, 5 July 2013 by Admin (talk | contribs) (3 revisions)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

This script should be run in SQLPLUS to make it work. The file 'tmp_subscript.sql' will be written in the current working directory.

set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_subscript.sql
select 'ALTER TABLE '||substr(c.table_name,1,35)||
-- for ENABLE use:
-- ' ENABLE CONSTRAINT '||constraint_name||' ;'
  ' DISABLE CONSTRAINT '||constraint_name||' ;'
  from user_constraints c, user_tables u
  where c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_subscript.sql;
!rm -i tmp_subscript.sql;
exit
/