Home » SQL & PL/SQL » SQL & PL/SQL » Is there any single line sql syntax to disable all constraint in a table (Oracle-10.2 / Linux-AS)
Is there any single line sql syntax to disable all constraint in a table [message #434629] Thu, 10 December 2009 00:37 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
I imported table-1 then inserting this records into table-2.
Before inserting I wanted to disable all constraints in table-2.
Table-2 has many constraints.I am doing this by a Unix shell script.

Question
Is there any single line sql syntax to disable/enable all constraints in a table ?

Thanks in advance
sbmk

[Updated on: Thu, 10 December 2009 00:40]

Report message to a moderator

Re: Is there any single line sql syntax to disable all constraint in a table [message #434630 is a reply to message #434629] Thu, 10 December 2009 00:43 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You have to disable constraints of a table one by one, seperately.
No way to disable all constraints of a table at a time by single SQL statement.

regards,
Delna
Re: Is there any single line sql syntax to disable all constraint in a table [message #434665 is a reply to message #434630] Thu, 10 December 2009 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it in a single line, but the line is quite long

BEGIN FOR i in (select constraint_name c from user_constraints where table_name = '<>') loop execute immediate 'alter table <> disable constraint '||i.c; end loop; END;/
Re: Is there any single line sql syntax to disable all constraint in a table [message #434669 is a reply to message #434665] Thu, 10 December 2009 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Except that the ending / should be in another line.

Regards
Michel
Re: Is there any single line sql syntax to disable all constraint / Index in a table [message #434719 is a reply to message #434665] Thu, 10 December 2009 06:14 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Thanks JRowbottom.
It works. similler to that Is there any syntax to disable all indexes of a table ?. I tried with below syntax (alter index index_name unusable). but I am getting error
Can u please tell where I did mistake

BEGIN
FOR i in (select index_name c from user_indexes where table_name = 'X')
loop
execute immediate 'alter index i.c unusable ';
end loop;
END;
/

I am getting error ORA-00940: invalid ALTER command

Thanks in advance
sbmk

[Updated on: Thu, 10 December 2009 06:19]

Report message to a moderator

Re: Is there any single line sql syntax to disable all constraint / Index in a table [message #434720 is a reply to message #434719] Thu, 10 December 2009 06:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Code should be
execute immediate 'alter index '||i.c||' unusable '; 


Why are you rying to make indexes unusable? What do you think this will achieve?
Re: Is there any single line sql syntax to disable all constraint / Index in a table [message #434725 is a reply to message #434720] Thu, 10 December 2009 07:05 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
I m inserting Millions of rows from one table to another table.(Script run every week). Before insert I am disabling all Constraint / Indexes to speed up insertion process
Then Enables all Constraint / Indexes.

Thanks
sbmk
Re: Is there any single line sql syntax to disable all constraint / Index in a table [message #434812 is a reply to message #434725] Thu, 10 December 2009 22:37 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Is this a way to speed up the process?

And if both the table contains same structure with same constraints, then you can remove constraints from back-up table. By whatever you are doing, I can say, because they are available only for presence, no meaning of those constraints in your case.

regards,
Delna
Previous Topic: Avoiding huge rollback
Next Topic: searching through at table
Goto Forum:
  


Current Time: Sun Sep 25 02:41:37 CDT 2016

Total time taken to generate the page: 0.10219 seconds