-
Hi,
I am going to import data from database A into database B.Both dbs have the same table definitions. I just need the data from db A to db B. I know that I have to disable the constraints on the tables before I import data into the tables. But I have about 180 tables. Is there a script which can automatically generate another script which I can use to disable the constraints and enable them when I finish importing. It would take forever to disable them manually. I would appreciate if somebody can help.
Thanks in advance.
-
use this
set feed off pages 0 head off echo off line 100 ver off
col col1 newline
accept username prompt 'Please enter owner of Tables: '
REM #
REM # create script to disable all referencial constraints
REM #
spool d:\disable.sql
select 'alter table '|| r_owner ||'.'||
table_name ||' disable constraint '||
constraint_name ||';'
from user_constraints
where r_owner = upper('&username')
and constraint_type = 'R';
spool off
REM #
REM # create script to enable constraints after import
REM #
spool d:\enable.sql
select 'alter table '|| r_owner ||'.'||
table_name ||' enable constraint '||
constraint_name ||';'
from dba_constraints
where r_owner = upper('&username')
and constraint_type = 'R';
spool off
REM #
REM #
REM # Now, just run "@d:\disable.sql" to disable all constraints for the owner entered
REM #
REM # to re-enable the constraints just run "@d:\enable.sql"
REM #
-
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
|