Home » RDBMS Server » Server Administration » disabling constraints without a script
disabling constraints without a script [message #51749] Tue, 11 June 2002 16:13 Go to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
I want to disable all user constraints without having to write a script for every alter table disable constraint clause.. Is there a way to do it with an Oracle supplied package or something? Thanks a lot..
Re: disabling constraints without a script [message #51751 is a reply to message #51749] Tue, 11 June 2002 20:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I beleive there is no such package available, that is inbuilt with oracle. but you can create one!!
SQL> get cons
  1  create or replace procedure disable_constraints
  2  as
  3  cursor c1 is select constraint_name,table_name from user_constraints;
  4  str varchar2(300);
  5  begin
  6  for mag in c1 loop
  7  exit when c1%notfound;
  8  str:='ALTER TABLE '||mag.table_name||' DISABLE CONSTRAINT '||mag.constraint_name;
  9  execute immediate str;
 10  end loop;
 11* end;
SQL> /

Procedure created.

SQL> get cons2
  1  create or replace procedure enable_constraints
  2  as
  3  cursor c1 is select constraint_name,table_name from user_constraints;
  4  str varchar2(300);
  5  begin
  6  for mag in c1 loop
  7  exit when c1%notfound;
  8  str:='ALTER TABLE '||mag.table_name||' enable CONSTRAINT '||mag.constraint_name;
  9  execute immediate str;
 10  end loop;
 11* end;
SQL> /

Procedure created.


note You may need to create an exceptions table in order to move the VIOLATED ROWS (if any) when you enable them back.
create table exceptions(row_id rowid,
	                owner varchar2(30),
	                table_name varchar2(30),
		        constraint varchar2(30));
Re: disabling constraints without a script [message #51764 is a reply to message #51749] Wed, 12 June 2002 14:22 Go to previous message
Tony
Messages: 190
Registered: June 2001
Senior Member
thanks a lot for your time...
Previous Topic: Oracle DB Migration between architectures HP-IBM
Next Topic: Re: CAN U PLZ TELL ME FULL FORM OF ORACLE
Goto Forum:
  


Current Time: Tue Sep 17 20:26:35 CDT 2024