Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Rolling back sequences?
dean wrote:
> Is it possible to roll back a sequence to its original point before a
> transaction started, if the transaction fails? I need to be able to log
> out reproducible SQL from a complex precedure. Oracle 9.2, windows
> platform.
Can you explain your goal a little better?
If by "I need to be able to log
out reproducible SQL from a complex precedure."
you mean you need to get to get EXACTLY the same results each run,
then you must essentially rollback the entire database. A restore from backup before each test run will do the trick. Of course, if there are any date or time values involves, you might also need to turn back the CPU clock. Maybe a few other things too.
OTOH if you mean you need to logically reproduce the same results each run,
then logically sequence number 5 is no different from sequence number 6, so why reset the sequence number back?
>
> Here's a typical example:
>
> select train_route_seq.nextval from dual;
>
> NEXTVAL
> ----------
> 311845
>
> 1 row selected.
>
> rollback
>
> Rollback complete.
>
> select train_route_seq.nextval from dual;
>
> NEXTVAL
> ----------
> 311846
>
> 1 row selected.
>
> Thanks,
>
> Dean
The DB works as expected. Why do you want to break it?
Ed Received on Tue Apr 04 2006 - 13:22:22 CDT