Re: Disabling constraints - in order

Chris, I think I have what you may be looking for. Complete an export of the database with no rows (rows=n). Then do an import of the database with the flag show=y. This will not touch the database but will give you an output file (don't forget to log !!!) that will have the exact order that tables need to be in to maintain RI integrity. The Oracle Metal Link has a script for converting the log file to useful ddl. Hint: I have just done my import show=y and piped standard out to a file (Seems to be pretty close.

Next I would parse out the CREATE TABLE COMMANDS and then create a script based on on the following query:

        select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;

For ease you could load the names of the tables into a table and use a cursor for looping and creating one consistent spool file for disabling your constraints.

This is how I would attack this. A more unconventional way would be to run the above script without table order multiple times. This would eventually disable all constraints. The same for the application of constraints. As dependant constraints are applied others will alter without error.

Rich.

gilb_at_xxx.senate.gov wrote:
>
> Chris,
>
> You may have tried this already, but there is an option of export
> which allows you to export without constraints. I don't know if this
> will help you or if you've tried it already - just thought I'd throw
> in my .02 worth.
>
> Gilbert
>
> On Mon, 1 Jun 1998 19:22:05 +0800, "Chris Kempster"
> <c.kempster_at_cowan.edu.au> wrote:
>
> >Dear all,
> >
> >I have a big problem, I need to disable the pk and fk constraints of over
> >300 tables of a very poorly documented system in the correct order as to not
> >get errors, and then have a reverse script to rebuild them again.
> >
> >The problem I have is that we were given a blank 'boot' DB, with all the
> >tables, triggers, constraints enables with minimal data. We need to dump
> >'our' data from the current version of this boot DB (may have changes) the
> >users have been on for some time now. They want to transfer select tables
> >(most of them) over to the new (v1.2.2) boot DB.
> >
> >Unfortunately for me, a straight import/export, even with various imp/exp
> >parameter configs is not working for me.
> >
> >Any ideas/example scripts I can use would be great.
> >
> >
> >Chris.
> >
> >
Received on Thu Jun 11 1998 - 06:33:01 CDT