Home » RDBMS Server » Server Utilities » Sequence Issues After Export / Import (9i Rel 2)
Sequence Issues After Export / Import [message #276337] Wed, 24 October 2007 22:23 Go to next message
Messages: 294
Registered: February 2006
Senior Member

We took a Full Schema Level Export from a DR Database which was brought on READ Only and all the Archive Log Shipping coming from Priamry Database were stopped till Export were done,

Once import is done on another Database, we have seen issues with Sequences and 2 constraints,
Sequence nextval was less than maximum values of a column in alsmost 30 talbes out of 200
and 2 constraints were in NOT Validated State,

What i assume either the Sequences Archive log were not exported when we started exported on DR or is there any other reason , and how can i avoid this, and I have faced this earlier also, and what i do i go to imported DB reset the sequences and validate the constraints by removing Child Records without Parent.

Second Question is , When we take a full schema Export with all grants/indexes/privliges/data/constraints /
and import in a new regions just to step up in 3 different ways
1 only Data
2 only indexes
3 only constraints -- while importing constraints is there a option where we are sure that there is no Problem in data and there will not be any Data Voilations, to Speed up can i specify just put / Create the constraints and don't validate since Export was taking from a offline Database when no activity was there,

Oracle version is both 9i Rel 2 and also on 10g rel 2.


[Updated on: Wed, 24 October 2007 22:24]

Report message to a moderator

Re: Sequence Issues After Export / Import [message #276643 is a reply to message #276337] Thu, 25 October 2007 14:53 Go to previous message
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Sequences can often cause havoc when importing from a different environment.

We have started using SYS_GUID instead, it is much uglier, but you are guarunteed uniqueness, and for the most part the user never sees this column anyhow.

One issue is PK vioations if you are adding to existing data, in my situation we are usually importing into clean tables, so that
is not an issue.

To work around the error you mentioned, you can run a post-import script that resets the sequences to be greater than the max value just imported.

Hope this helps a little.
Previous Topic: Oracle errors on import
Next Topic: sql loader error
Goto Forum:

Current Time: Sun Oct 23 05:31:25 CDT 2016

Total time taken to generate the page: 0.11125 seconds