disabling all the constraints in the schema

[ronnie]

Hi,
I want to write a script which disables all the integrity constraints in a given schema.

Can somebody help me with this

Thanks
Ronnie

[marist89]

The table you are looking for is user_constraints...

[jrpm]

spool dissem
select 'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
from dba_constraints
order by ower,table_name;
spool off
spool enabem
'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
from dba_constraints order by ower,table_name;
spool off



Then edit the spool files to trim out some feedback from the queries.

[ronnie]

Originally posted by jrpm
spool dissem
select 'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
from dba_constraints
order by ower,table_name;
spool off
spool enabem
'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
from dba_constraints order by ower,table_name;
spool off



Then edit the spool files to trim out some feedback from the queries.

Thanks a lot guys.

jrpm, I think u mean to say enable constraint.

ALso what does ur signature the answer is 42 mean. just curios :-)

Ronnie

[gandolf989]

If you read the 5 part trilogy the hitchhikers guide to the galaxy you would know that the answer to the ultimate question about life the universe and everything is 42, but they don't know the question. the books are by Douglas Adams annd they are a good read.

[SANJAY_G]

Be aware that when you disable a constraint the associate index is dropped. When you enable it again the index is created in the users default tablespace and not in the tablespace where it has been put originally.

Sanjay