SQL Script Disable Foreign Keys

Updated: 04-Mar-2002
Version: 2.0

Description

Disables all enabled.foreign keys for a given owner.

Parameters

own - The foreign key owner.

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

rem whenever oserror exit 1
set serveroutput on size 100000
rem whenever sqlerror exit 2

set verify off
set serveroutput on

ACCEPT own PROMPT 'Enter Value for Constraint Owner : '
PROMPT
declare
   sql_stmt varchar2(1024);

cursor get_cons is
   select owner, table_name,constraint_name
   from all_constraints
   where constraint_type='R' 
   and status = 'ENABLED' and owner = UPPER('&&own');
    
con_rec get_cons%ROWTYPE;

begin

   FOR con_rec IN get_cons LOOP
     BEGIN
       sql_stmt := 'alter table '||con_rec.owner||'.'||con_rec.table_name||
                    ' disable constraint '||con_rec.constraint_name;

      dbms_output.put_line(sql_stmt);

     EXECUTE IMMEDIATE sql_stmt;

     EXCEPTION WHEN others THEN
        dbms_output.put_line('Error Processing '
                           ||con_rec.owner||'.'||con_rec.table_name||' '
                           ||con_rec.constraint_name);
        dbms_output.put_line(SQLERRM);
     END;
   END LOOP;

end;
/
select status,count(*) from all_constraints 
where constraint_type='R' and owner = UPPER('&&own')
group by status;

Related Links

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links