Re: Enable novalidate contains after import?

From: Toon Koppelaars <toon_at_rulegen.com>
Date: Mon, 23 May 2016 11:25:55 +0200
Message-ID: <CAA9w=Esg=VtoNnPmCfgtYnuTyWjw=npTLOYEz+OUHrx+f7wyHw_at_mail.gmail.com>

The unique and primary key constraints are backed by (unqiue) indexes. These will be created anyways. I guess (but did not investigate) that's why Oracle enables these constraints in enable mode.

So you're waiting for the indexes to be created. Once they are there, adding/enabling the uk/pk constraints shouldn't take much time.

On Mon, May 23, 2016 at 11:17 AM, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:

> This is most likely not possible, at least according to the docs and a few
> experiments that I've carried out.
>
> I'm still migrating that Solaris 9i database to Azure on llg. I have huge
> tables with over half a billion rows, primary key, unique and referential
> integrity constraints.
>
> Data are from production where it has been validated by these constraints
> already, so I should be safe to enable the contains with novalidate in the
> receiving database.
>
> Prior to running 9i exp, I set the three types of constraints to
> novalidate and exported. On import I see that while the referential
> integrity constraints do get enabled novalidate, the unique and primary key
> one are always enabled in validate mode, so the whole table is scanned to
> ensure that it is OK. This wastes a good many hours doing checks that have,
> effectively, already been done on the source database.
>
> Does anyone know of a way to have the imp create these constraints but in
> novalidate mode as per the source database?
>
> I'm tempted to run a show=y constraints=y (indexes=n maybe) to generate a
> script but that would require editing, or me writing a parser to edit it,
> to get the constraints sql out and changed to novalidate. If nothing else
> exists.
>
> I need to shave as much time as possible off the imports. So far I'm down
> from over 89 hours to 34. I need more (less ) if possible.
>
>
> Cheers,
> Norm.
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

--
http://www.freelists.org/webpage/oracle-l

Received on Mon May 23 2016 - 11:25:55 CEST