RE: enabling/ disabling constraints/ triggers

From: Glenn Travis <Glenn.Travis_at_wcom.com>
Date: Tue, 23 Jan 2001 17:57:37 -0500
Message-Id: <10750.127331@fatcity.com>

Here's a great script to generate disable and enable scripts;

---

set linesize 132
set ver off
set feed off
set pagesize 0
col cmd for a80
spool disable.&&owner..cons.sql
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' cmd
from dba_constraints
where constraint_type in ('R','P','U')
and owner='&&owner'
order by cmd
/
prompt quit
spool off
spool enable.&&owner..cons.sql
select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';' cmd
from dba_constraints
where constraint_type in ('R','P','U')
and owner='&&owner'
order by cmd
/
prompt quit
spool off
quit

---

The same can be applied to triggers using the sql: select 'alter trigger '||owner||'.'||object_name||' disable ;' cmd from dba_objects
where object_type='TRIGGER'
and owner='&&owner'
order by cmd;

---

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> bruce.taneja_at_mcd.com
> Sent: Tuesday, January 23, 2001 11:36 AM
> To: Multiple recipients of list ORACLE-L
> Subject: enabling/ disabling constraints/ triggers
>
>
>
>
> I have never done this before .. learning DBA.. :)
>
> - what commands can I use to enable/ disable all trigggers?
> - what commands can I use to enable/ disable all constraints (if that is
> possible)..
> - in particular do you recommend disabling any constraints while
> doing full
> imports of a database e.g. while during a migration to a higher version
> database (export/ import upgrade i.e.)
>
> - any other recommendations for upgrade using export/ import related to
> indexes/ constraints disable/ enablement..
>
> thanks!
> Bruce
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: bruce.taneja_at_mcd.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Tue Jan 23 2001 - 16:57:37 CST