This content has been marked as final. Show 7 replies
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.
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.
944768 wrote: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.
can i have any ex : of sequence generating gaps?
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.