This discussion is archived
7 Replies Latest reply: Feb 11, 2013 11:12 AM by rp0428 RSS

how to reset sequence when table need to be droped.

947771 Newbie
Currently Being Moderated
Hi,

i want to use sequence in a trigger to insert primary key of a table.

porblem is i need to reset the sequnece , i want to drop the sequence then again recreate it.

becasue i also need to drop the table. i know GTT can be used but , situation is such.

is it ok to do that as the query is offline query and admin will run it .

or should is there any reset method which is better than dropping.

but please note i have to drop the tabel.


yours sincerly
  • 2. Re: how to reset sequence when table need to be droped.
    rp0428 Guru
    Currently Being Moderated
    >
    i want to use sequence in a trigger to insert primary key of a table.

    porblem is i need to reset the sequnece , i want to drop the sequence then again recreate it.
    >
    Why do you need to 'reset' the sequence? A sequence is generally used to generate surrogate keys and those values are guaranteed to have gaps.

    Just continue using the sequence. It doesn't matter what the values are that it generates. Just use them.
    >
    becasue i also need to drop the table. i know GTT can be used but , situation is such.

    is it ok to do that as the query is offline query and admin will run it .

    or should is there any reset method which is better than dropping.

    but please note i have to drop the tabel.
    >
    So drop the table. The table and the sequence have NO RELATION to each other. Only the table data might have a relation to the sequence. But if you drop the table then there is NO DATA.

    So there is nothing left that has any relation to the sequence or the numbers that it generates.

    Your solution is to drop the table and, if you wish, continue using the sequence and whatever numbers it produces.

    If you really think you need to 'reset' the sequence then explain why and we can offer some alternatives.
  • 3. Re: how to reset sequence when table need to be droped.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Support rp0428's response fully.

    The only requirement for a surrogate key is uniqueness. A sequence gives that. No need to reset it.

    In fact, resetting it means potential duplicates as the same numbers will be handed out by the sequence again. Why?

    And no, size of the number is not a factor, and nor is human readability of such a surrogate key number.
  • 4. Re: how to reset sequence when table need to be droped.
    947771 Newbie
    Currently Being Moderated
    can i have any ex : of sequence generating gaps?

    yours sincerely
  • 5. Re: how to reset sequence when table need to be droped.
    jeneesh Guru
    Currently Being Moderated
    944768 wrote:
    can i have any ex : of sequence generating gaps?
    You could try yourself..

    1.create a sequence
    2.Insert into a table using the sequence (INSERT INTO T(id) values(seq1.nextval);
    3.commit;
    4.Insert next value
    5.ROLLBACK;
    6.Insert next value
    7.Commit;

    Now check the data in the table
  • 6. Re: how to reset sequence when table need to be droped.
    BluShadow Guru Moderator
    Currently Being Moderated
    944768 wrote:
    can i have any ex : of sequence generating gaps?

    yours sincerely
    Sequences generally get cached in memory to allow for faster access. If the database is shut down and restarted, or crashes etc. then the cached numbers are lost and the next value in the sequence will be the one after the previously cached set of numbers.
    Sequences offer to guarantee a unique number, but they do not guarantee a gap free sequence, that's the way they work, and it would be difficult for Oracle to implement them any other way in an environment that is generally used by multiple users.
  • 7. Re: how to reset sequence when table need to be droped.
    rp0428 Guru
    Currently Being Moderated
    >
    can i have any ex : of sequence generating gaps?
    >
    Sure - but I'm really busy right now.

    Hey - Tom Kyte never has much to do. Ok if he answers your question for you?
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:530735152441

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points