Re: Reorg of 7.3.3.5.1 tables with lots ot Referential Integrity

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.
>
> The database is too big to run a complete export/import of the schema
> involved.
>
> I have learned how to determine the contraints on an individual table
> and how to see it parent table via foreign keys. I assume one can see
> its child tables via the Referenced table column as well.
>
> I am with the belief now that disabling the RI contraints of a table
> (ie: leaving the Check and Primary key constraints alone) and then
> exporting and importing the table, enabling constraints is enough to
> reorg the table. Of course this is done in standalone mode.
>
> Does this work? Is there more I should do. I assume I would run all
> the reports I can to pre and post to check that constraints came back
> online okay, etc etc.
>
> Should the constraints of the parent table and child table also be
> disabled at this time?
>
> Many thanks in advance.....Ian Evans.
>
>

Ian,

try this (no liability accepted - make sure you have a backup!) ...

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.

There should be no need to do anything to the primary key or any other constraints on the actual table that you are trying to re-org. However, for speed purposes, you may wish to disable/drop the primary key and unique constraints & indexes on the table & then re-enable (or recreate) these after the import. For a large table this could be a lot faster.

I hope this helps.

--
Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Oct 15 1998 - 10:37:30 CDT