How do I automatically reset a sequence's value to 0 every year in Oracle 10g?

Sequences aren't really designed to be reset. But there are some cases where resetting a sequence is desirable, for example, when setting up test data, or merging production data back into a test environment. This type of activity is not normally done in production.

IF this type of operation is going to be put into production, it needs to thoroughly tested. (What causes the most concern is the potential for the reset procedure to be accidentally performed at the wrong time, like, in the middle of the year.

Dropping and recreating the sequence is one approach. As an operation, it's fairly straightforward as far as the SEQUENCE goes:

    DROP SEQUENCE MY_SEQ;
    CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 0;

[EDIT] As Matthew Watson correctly points out, every DDL statement (such as a DROP, CREATE, ALTER) will cause an implicit commit. [/EDIT]

But, any privileges granted on the SEQUENCE will be dropped, so those will need to be re-granted. Any objects that reference the sequence will be invalidated. To get this more generalized, you would need to save privileges (before dropping the sequence) and then re-grant them.

A second approach is to ALTER an existing SEQUENCE, without dropping and recreating it. Resetting the sequence can be accomplished by changing the INCREMENT value to a negative value (the difference between the current value and 0), and then do exactly one .NEXTVAL to set the current value to 0, and then change the INCREMENT back to 1. I've used a this same approach before (manually, in a test environment), to set a sequence to a larger value as well.

Of course, for this to work correctly, you need to insure no other sessions reference the sequence while this operation is being performed. An extra .NEXTVAL at the wrong instant will screw up the reset. (NOTE: achieving that on the database side is going to be difficult, if the application is connecting as the owner of the sequence, rather than as a separate user.)

To have it happen every year, you'd need to schedule a job. The sequence reset will have to be coordinated with the reset of the YYYY portion of your identifier.

Here's an example:

http://www.jaredstill.com/content/reset-sequence.html

[EDIT]

UNTESTED placeholder for one possible design of a PL/SQL block to reset sequence

    declare
      pragma autonomous_transaction;
      ln_increment       number;
      ln_curr_val        number;
      ln_reset_increment number;
      ln_reset_val       number;
    begin

      -- save the current INCREMENT value for the sequence
      select increment_by
        into ln_increment
        from user_sequences
       where sequence_name = 'MY_SEQ';

      -- determine the increment value required to reset the sequence
      -- from the next fetched value to 0
      select -1 - MY_SEQ.nextval into ln_reset_increment from dual;

      -- fetch the next value (to make it the current value)
      select MY_SEQ.nextval into ln_curr from dual;

      -- change the increment value of the sequence to 
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_reset_increment ||' minvalue 0';

      -- advance the sequence to set it to 0
      select MY_SEQ.nextval into ln_reset_val from dual;

      -- set increment back to the previous(ly saved) value
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_increment ;
    end;
    /

NOTES:

• how to best protect the sequence from access while it's being reset, RENAME it?
• Several test cases to work through here.
• First pass, check normative cases of positive, ascending, increment 1 sequence.
• would a better approach be to create new SEQUENCE, add permissions, rename existing and new sequences, and then re-compile dependencies?

Just throwing this out there as an idea:

If you want a solution that requires no ongoing DDL (i.e. no dropping and creating or resetting sequences), or even any jobs, you could consider something like this (this is in principle only, I haven't tested this approach but I'm sure it'll work):

1. Create a single sequence.

2. Create a reference table, with one row for each year, e.g.

   YEARS (year NUMBER(4,0) PRIMARY KEY, starting_value NUMBER)

3. When you get NEXTVAL from the sequence, you then have to subtract the starting_value when queried from the YEARS table for the current year. If the year is not found, a new row should be inserted (i.e. the first process run in any given year will insert the new value).

e.g. a function, e.g. get_year_starting_value (pn_year IN NUMBER) RETURN NUMBER could query this table and return the starting_value for the given year; if it gets NO_DATA_FOUND, it could call a procedure to insert it using the NEXTVAL from the sequence (committed in an autonomous transaction so that the new value is immediately available to other sessions, and so that the function doesn't fail due to the side effect)

Probably not a solution for all cases, but I think this approach may help in at least some scenarios.

Use a job to do the trick. First, create a stored procedure to reset your sequence (I usually go with the DROP/CREATE solution, but you could use spencer7593's trick) :

CREATE OR REPLACE PROCEDURE my_seq_reset AS
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE my_seq';
    EXECUTE IMMEDIATE
      'CREATE SEQUENCE my_seq' ||
      '  MINVALUE 1 ' ||
      '  MAXVALUE 999999 ' ||
      '  START WITH 1 ' ||
      '  INCREMENT BY 1 ' ||
      '  NOCACHE';
END;

Then create the job (see here for the reference) :

BEGIN
  dbms_scheduler.create_job(
    job_name        => 'job$my_seq_reset',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'my_seq_reset',
    start_date      => TO_DATE('01-01-09', 'DD-MM-RR'),
    repeat_interval => 'FREQ=YEARLY;BYDATE=0101',
    enabled         => TRUE,
    auto_drop       => FALSE,
    comments        => 'My sequence yearly reset job.'
  );
END;

You're done.