"It would be very welcome if there was a simple built-in properly functioning reset function..."

I agree...but dont forget what the motivation for sequences is - a scalable solution "from which multiple users can generate unique integers" (quoted text from the Concepts guide).

So the idea of "resetting" them, ie, instilling some inherent business definition in the resulting integers, eg "This year we restart from 1" is a departure from their intended purpose, hence the problem you're seeing.

Option 1- using decrement

Even if you *know* exactly what number to decrement by, you will *always* run the risk of encountering 'ORA-08004: sequence SEQ.NEXTVAL' if the sequence is in use during this reset task. For example, lets say I'm doing:

insert into t values (seq.nextval);

then even doing "lock table t in exclusive mode" before your maintenance does not solve the issue, because the moment I do: 'alter sequence' I will release that lock.

In order to keep occurrences of that down to a minimum, you could look at doing:

alter sequence seq nocache

temporary during the reset, so your nextval check will not be as far "out of date" with respect to other instances.

Option 2- using cycle

You could approach this from a different tack. Find the current sequence value, and then issue:

alter sequence seq cycle minvalue 1 maxvalue [current max]

The next seq.nextval will cause the sequence to cycle, and then you issue:

alter sequence seq cycle minvalue 1 maxvalue [very high number]