3

I am using oracle 11g . i came across creating sequences, it raised a question

Is it possible to rollback a sequence using java?

If we can , pls suggest me ?

Roman C
  • 49,761
  • 33
  • 66
  • 176
Santhosh
  • 8,181
  • 4
  • 29
  • 56
  • Why would you want to do that? – Frank Schmitt Feb 03 '14 at 12:27
  • please see to my comment for @Guntram answer – Santhosh Feb 03 '14 at 12:29
  • Unless you can identify an actual problem, don't try to find a solution. – David Aldridge Feb 03 '14 at 14:44
  • 1
    @sankrish `Sequence` is designed to _always_ generate the next possible value. It can only be restarted from the beginning. There is no way to `rollback` the generated sequence, as a parallel session in another process could have already incremented it. Sequences are stateless. They get generated and never know it was useful or not. So for your situation, you may have to write your own logic. But it is near impossible to make it _thread-safe_. – Maheswaran Ravisankar Feb 03 '14 at 16:34

1 Answers1

5

Yes, use alter sequence. See http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2011.htm.

But note there is no real use for this. Unless you delete all records from your tables that use your sequence, you'll get errors when you use the sequence for creating unique IDs.

And you will always have "holes" in your generated numbers, there is no point in trying to make IDs created by sequences contiguous. Imagine user A doing an insert, user B doing another insert, user B commits, user A rolls back. The sequence number that A used will be lost, as B used a higher one.

Guntram Blohm
  • 9,667
  • 2
  • 24
  • 31
  • In my case i have 5 insert statements using the same sequence . i wannted to rollback the sequence even if one of them ends in error – Santhosh Feb 03 '14 at 12:28
  • 5
    And what happens if another user executes the same code, creates another 5 sequence numbers, and *then* the first ends in error? Don't try to make contiguous IDs. It just won't work. You need unique IDs, which sequences guarantee, but there's no point in trying to make sure every single ID is used. – Guntram Blohm Feb 03 '14 at 12:30
  • 1
    @sankrish The question still stands - *why* do you need to rollback the sequence in this case? Sequences are great for generating unique identifiers, they are not so great for generating consecutive numbers without "holes" in them. – Frank Schmitt Feb 03 '14 at 12:31
  • i just wanted to reuse the sequences that are generated for partial execution .. (i.e) if 3 tables gets inserted with the value and 2 fails – Santhosh Feb 03 '14 at 12:33
  • @GuntramBlohm: I agree with the part of your answer that messing with the sequence is pointless at best and dangerous at worst. But I don't believe you are correct regarding alter sequence. Alter sequence does not affect starting number. Here's a sample Oracle link in that regard: http://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm – Olaf Feb 03 '14 at 20:51
  • @Olaf, Guntram is correct - you *can* use ALTER SEQUENCE to change the INCREMENT BY - with a combination of two ALTERs and a select you can reset the start of a sequence to any value you want. – Jeffrey Kemp Feb 04 '14 at 03:27
  • I admit i hadn't checked the syntax, but i remembered having seen it done somewhere. But you're right Jeffrey, it was done the way you explained. Thank you for clearing this up. – Guntram Blohm Feb 04 '14 at 09:26