-
I am trying to TRUNCATE ALL tables in my schema
and I keep getting
"unique/primary keys in table referenced by enabled foreign keys".
should I disable all constraints ?? if so how can I do it
in one statment???
thanks,
-
create a dynamic script that will find all of the constraints and generate the sql to disable them, then you will have to do the same thing to enable them. Here is what I use:
set termout off
clear columns
column sqlline newline
set feed off
set pages 0
spool E:\DIR\disable_cons.sql
select 'spool E:\dir\disable_cons.log' from dual
/
select ' ' sqlline
,'prompt ' sqlline
,'prompt Disabling Constraint: '||constraint_name||' from Table '||table_name||'...' sqlline
,'prompt ' sqlline
,'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' sqlline
from sys.dba_constraints
where ( constraint_type = 'R' or
(constraint_type = 'C' and constraint_name not like 'SYS%'))
and owner NOT IN ('SYS','SYSTEM')
and status = 'ENABLED'
/
select 'spool off' from dual
/
spoo off
set feed on
set pages 60
set termout on
prompt Output is in... E:\dir\disable_cons.sql
You can just edit this to fit your db and also create a script to go and enable all of the constraints as well. I just write a script to run this file then run the file it creates, I suppose you could just use a "run disable_cons.sql" line at the end of this one to do the same thing.
You may want to do the same for the triggers.
HTH
-
set feed off pages 0 head off echo off line 100 ver off
col col1 newline
REM #
REM # create script to disable all referencial constraints
REM #
spool /tmp/disable.sql
select 'alter table '|| r_owner ||'.'||
table_name ||' disable constraint '||
constraint_name ||';'
from dba_constraints
where r_owner = upper('')
and constraint_type = 'R';
spool off
REM #
REM # create script to enable constraints
REM #
spool /tmp/enable.sql
select 'alter table '|| r_owner ||'.'||
table_name ||' enable constraint '||
constraint_name ||';'
from dba_constraints
where r_owner = upper('')
and constraint_type = 'R';
spool off
- magnus
change to your own schema.
to run just use: @/tmp/disable.sql
on WIN32 change directory to something like: d:\disable.sql
then enable them by running the other script "enable.sql"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|