Re: bypassing table constraints when using the 'imp' command

From: Keith Boulton <boulke_at_globalnetnospam.co.uk>
Date: Thu, 01 Oct 1998 18:51:07 GMT
Message-ID: <3613cb05.33863793@news.globalnet.co.uk>

On Tue, 29 Sep 1998 15:17:03 -0500, Michael Brennan <mbrennan_at_email.vill.edu> wrote:

>I need to compress/defragment the data on several tables by exp/imp but
>I wanted to know if there was a way to ingore the constraints instead of
>taking them off manually.
>
>Michael Brennan
>mbrennan_at_email.vill.edu

If running oracle 8, you can disable the constraints before export (normally check and RI constraints). You can generate a script to do this from the dba_constraints view e.g. in a sqlplus command file: set feedback off

set pagesize 0

spool discon.sql

select 'alter table '|| owner ||'.'|| table_name || ' disable constraint '|| constraint_name || ';'
from dba_constraints
where constraint_type in ('C', 'R')
and owner not in ('SYS', 'SYSTEM')
/

spool off
@discon.sql

After export and import, you can reenable them using the nocheck clause e.g. by modifying the above file to include alter table <xxx.yyy> enable constraint <constraint name> novalidate; Received on Thu Oct 01 1998 - 13:51:07 CDT