SQL Script Disable Foreign Keys

Updated: 03-Aug-1998
Version: 1.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
rem whenever sqlerror exit 2
set serveroutput on size 100000
set verify off

ACCEPT own PROMPT 'Enter Value for Constraint Owner : '

declare
	w_p_string varchar2(254);
	w_cursor_id integer;
	w_p_exec integer;
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
   w_cursor_id:=dbms_sql.open_cursor;
   FOR con_rec IN get_cons LOOP
      w_p_string := 'alter table '||con_rec.owner||'.'||con_rec.table_name||
                    ' disable constraint '||con_rec.constraint_name;
      dbms_output.put_line(w_p_string);
      dbms_sql.parse(w_cursor_id,w_p_string,1);
      w_p_exec := dbms_sql.execute(w_cursor_id);
   END LOOP;
   dbms_sql.close_cursor(w_cursor_id);
end;
/

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

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