Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: Sequences out of sequence

    Bear with me if this has been discussed a 1,000 times, but I've googled and searched the archive and I must not be searching on the correct term. Most of what I find has to do with resequencing for performance... which is not really what I'm looking for (at least I don't think I do).

    I was given a backup copy of a database where the PK values for the tables are populated used sequences. However, the values in each of the tables are 5-20 increments ahead of the sequences that generate them. I.e., the max(PK) of a table's PK column is at 100 and the nextVal of the table's sequence is at 80. As a result, all of the INSERTs fail because we are reusing the sequences in a PK column.

    I have a couple of questions:

    1. What causes this? Is it part of the backup/restore process or something else?

    2. Is there a SP that will update all the sequences for me, or do I need to write my own SP to do this?

    Thank you.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I was given a backup copy of a database where the PK values for the tables are populated used sequences.

    provide copy of actual OS command line that produced "backup copy"

    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete results of the following:
    sqlplus <username>/<password>
    SELECT * from v$version;
    exit
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Posts
    184
    Anacedent,

    Sorry, I don't have the backup syntax that was used as it was performed by customer.

    Sorry about the version missing. I meant to include that:
    Oracle Database 10g Release 10.2.0.1.0 - Production

    Thank you .

    rt

  4. #4
    Join Date
    May 2004
    Posts
    184

    OS info

    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

  5. #5
    Join Date
    May 2004
    Posts
    184
    Also, the customer used the EXP command, (I'm using IMP) but I don't know what switches they may have added.

    rt

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    any solution is very much process & detail dependent.
    I expect that with a correct process, the import can occur without error.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess that could happen when doing "exp .... consistent=N" - tables (and sequences) could have changed in the course of the operation. As to how to fix it, I'm not aware of any built-in functionality, so most likely you'll have to write your own SP or script.

  8. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    We experienced this issue, but only once. We couldn't work out what had caused it, but our solution was to reset the sequence, starting again at a higher value. This worked for us because the sequence was used only to assign a unique number on the creation of a record - the actual number assigned was irrelevant, just as long as it was unique. If you have a different use for the sequence numbers then you'll looking at an SP or script.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  9. #9
    Join Date
    May 2004
    Posts
    184
    Thank you n_i and cis_groupie for your help. I think the INCONSISTENT defaulting to No is probably the source of the problem and I'm requesting a fresh backup. Unfortunately, I've seen this in the past occasionally too but we never found the cause. Given the high usage of this application, it's quite possible we are getting a backup that is spread over enough time that it is creating this problem. I'll post back my results with the second backup to let you know if that made a difference.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •