RE: sequences

From: TJ Kiernan <tkiernan_at_pti-nps.com>
Date: Tue, 11 Mar 2014 14:32:44 +0000
Message-ID: <196DB2D4BDE5804EAF3158CCC1C698BC09B5F6F6_at_lopez.pti-nps.com>

It's been nearly 2 years since I've upgraded, so please forgive the clouded memory. I don't specifically recall any problems with sequences when I upgraded (10.2.0.3 - 11.2.0.3). My guess is that you were testing with a transaction-inconsistent dataset (if you datapump over a network, it's not consistent by default). Your options would be to either export a transaction consistent set of data (I believe you can specify an "as-of" SCN), or you fix it after the import completes by setting up a PL/SQL loop to advance the sequences to the table values. The trouble with the 2nd approach is that I don't know of a good way to tie a table and sequence together (maybe dba_dependencies?), so you're stuck manually filling this in:

DECLARE
  startvalue number;
  stopvalue number;
begin
  Select <sequence>.nextval into startvalue from dual;   Select max(<table_key>)
    into stopvalue
    from <table>;
  WHILE startvalue <= stopvalue
  LOOP

     SELECT <sequence>.NEXTVAL
       into startvalue
       FROM DUAL;

  END LOOP;
  Select <next_sequence>.nextval into startvalue from dual;   Select max(<next_table_key>)
    into stopvalue
    from <next_table>;
  WHILE startvalue <= stopvalue
  LOOP

     SELECT <next_sequence>.NEXTVAL
       into startvalue
       FROM DUAL;

  END LOOP;
  --***and so on and so forth***--
END; Thanks,
T. J.

The information contained in this message is privileged and confidential information intended only for the use of the individual or entity identified above. If the receiver of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, use or copying of this message is strictly prohibited. If you have received this message in error, please immediately notify the sender by replying to his/her e-mail address noted above and delete the original message, including any attachments. Thank you.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zelli, Brian Sent: Tuesday, March 11, 2014 9:05 AM
To: oracle-l (oracle-l_at_freelists.org) Subject: sequences

I am exporting data from 10g to 11g and when we did it in test, the analyst said the sequences were out of whack. I would then play around until I could get it close. How do I deal with a sequence after it has been imported so it starts with the next value?

Brian

This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.

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

Received on Tue Mar 11 2014 - 15:32:44 CET