OraFAQ Forum: SQL & PL/SQL » Is there any single line sql syntax to disable all constraint in a table

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there any single line sql syntax to disable all constraint in a table [message #434629]

Thu, 10 December 2009 00:37

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

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

Report message to a moderator

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

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;/

Report message to a moderator

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

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Except that the ending / should be in another line.

Regards
Michel

Report message to a moderator

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

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

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?

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	Avoiding huge rollback
Next Topic:	searching through at table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 13 12:39:33 CDT 2025