Re: Disabling constraints - in order

In article <6ku358$1us$1_at_news.cowan.edu.au>,   "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.
>
>

At SQL prompt login as the user who owns the tables SQL> set head off SQL> set pages 0 SQL> spool disable.sql SQL> select 'alter table '||table_name||' disable constraint '||constraint_name||' ; ' 2> from user_constraints where constraint_type in ('P','R') ; SQL> spool off

You can edit this file and look at the DDL generated.

Remember you will have to run this script many times since it doesn't take into account referential hierarchy.

To find out if there are any primary keys or foreign keys not disabled SQL> select count(*) from user_constraints where status = 'ENABLED' ; if the count is not zero keep executing the above script (disable.sql)

You can also use the SQL used for creating the disable.sql to ENABLE the constraints by replacing the 'disable' word in the SQL to 'enable'

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Jun 01 1998 - 17:07:51 CDT