What is the best way of resetting a sequence in Oracle 10g?

Question

To reset an Oracle sequence, I follow the following temporary way. Let's say, I have a table named TEMP_TABLE with a sequence associated with it.

The following SQL produces 22 as the sequence next value (only in this case).

SELECT TEMP_TABLE_SEQ.NEXTVAL from DUAL;

The following statement decreases the cache size to SEQUENCE.MINVALUE.

ALTER SEQUENCE TEMP_TABLE_SEQ INCREMENT BY -21;

Next, I'm performing a dummy select that retrieves 1 as the sequence value.

SELECT TEMP_TABLE_SEQ.NEXTVAL from DUAL;

I'm now resetting the cache size to 1.

ALTER SEQUENCE TEMP_TABLE_SEQ INCREMENT BY 1;

So that the following statement,

SELECT TEMP_TABLE_SEQ.NEXTVAL from DUAL;

gives me 2 as the next sequence value and so on.

---

In PostgreSQL server, the following only one statement is sufficient to reset a sequence.

ALTER SEQUENCE TEMP_TABLE_SEQ RESTART WITH 0;

Is there such a fair way in Oracle (10g) to reset a sequence?

Answer 1

How about this :

1. Drop the sequence

    DROP TEMP_TABLE_SEQ;
   

2. Recreate the sequence

    CREATE SEQUENCE TEMP_TABLE_SEQ
    MINVALUE 1
    MAXVALUE  999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;