Re: Reorg of 7.3.3.5.1 tables with lots ot Referential Integrity

In article <7054rq$27e$1_at_nnrp1.dejanews.com>,   esiyuri_at_my-dejanews.com wrote:
> In article <703oi9$r42$1_at_news.mel.aone.net.au>,
> ross.coventry_at_c031.aone.net.au (Ross Coventry) wrote:
> > Hi Folks,
> > I have a large database on HPUX 10.20, Oracle server 7.3.3.5.1 which
> > has need of some table reorganisation. Unfortunately this is our only
> > database with plenty of RI incorporated within it.
>
> 1/ Export the table that you wish to rebuild.
> 2/ Disable any foreign keys (ie on "child" tables) that reference the primary
> key of this table. (ALTER TABLE XXX DISABLE CONSTRAINT YYY;). There is no
> need to touch the constraints on any "parent" tables.
> 3/ Truncate (or drop then recreate as desired) the table.
> 3/ Import the data from the previous export.
> 4/ Re-enable the foreign key constraints. This may take some time.
>

I forgot to add that if you want to *drop* the table in step (3) then you will need to drop (ie NOT just disable) the constraints in step (2), otherwise you will get the misleading error message: "ORA-02266: unique/primary keys in table referenced by enabled foreign keys" (in v7.3.3.6). If you just want to truncate the table, it *is* sufficient just to disable the foreign keys.

When dropping a table it does not seem to make any difference if the foreign keys are disabled - I believe that this is a "bug" in the error message and/or documentation.

--
Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 16 1998 - 04:52:52 CDT