How to reset an Oracle sequence at midnight everyday

Oracle, unlike MySQL, does not auto-generate values for the primary key column for you. That means, you can't "auto_increment" a column in Oracle. With each INSERT statement you have to provide the value for the primary key column yourself. Oracle has database sequences to help you generate series of numbers for this purpose.

Sometimes you end up using Oracle sequences for stuff other than as a source of values for your primary key. And sometimes you need to reset the value of the sequence back to 1 at midnight everyday. I ended up with the same problem and I couldn't find a canned solution off the Net for this. I spent a day trying to solve this and ended up learning a lot about Oracle sequences, Oracle packages and PL/SQL, the dbms_lock package, and the dbms_scheduler package.

The problem would be fairly easy to solve if resetting an Oracle sequence were a single atomic SQL statement. No, it turns out that you have to do a bit of circus involving multiple statements to reset the sequence back to 1. I figured that you basically have two options:

1. Drop the sequence (statement #1) and recreate the sequence (statement #2)


2. Change the INCREMENT BY property of the sequence to be negative of the CURRVAL (statement #1). Increment the seqeunce (statment #2). Reset the INCREMENT BY property back to 1 (statement #3).

In both the solutions, you run the risk of some other process coming along and doing a seq.nextval on the sequence while the reset is underway. Boom! A bit like getting caught with your pants down.

So, here's what I did:

1. Create a PL/SQL package in Oracle which acts as a proxy for the sequence. Make sure that no application module does a seq.nextval directly. All requests to get the nextval have to go through the getNextSeq() function/procedure provided by the PL/SQL pacakge.


2. The getNextSeq() function first acquires a lock in shared mode provided by the dbms_lock package in Oracle. Increments the sequence. Releases the lock, and returns the nextval. (Note: The dbms_lock package is a general purpose lock library. It does not mean that you acquired a lock on the sequence - that's not possible in Oracle, as some gurus told me. You just acquire a lock. Very much like a mutex lock you acquire in programming languages.)


3. In the same PL/SQL package, you create a function resetSeq() which acquires the same lock in exclusive mode, resets the sequence using either of the solutions given above, and releases the lock.


4. Now, you can create a scheduled job in the DB using the dbms_scheduler package to run the resetSeq() function at midnight everyday. In fact, you can execute that at whatever frequency/interval you want.

In the process I also realised that Oracle has the capability of doing a fair amount of metaprogramming through the EXECUTE IMMEDIATE statement. You can pass it any string and Oracle tries to parse + execute that as an Oracle statement, much like eval in Ruby or Lisp.

I spent most of my time breaking my head over why dbms_lock.request didn't seem to be working. Then I realized that dmbs_lock.allocate_unique worked on a per session basis. What that means is, if two separate connections called dmbs_lock.allocate_unique to create a lock with the same lock handle, both of them would end up getting lock objects that would behave independently of each other. In which case, effectively, both the processes were working on different locks and the whole purpose of using locks would be defeated. This is why, I'm calling allocate_unique just once, and "hard coding" the lock id in the package definition using Oracle "metaprogramming".

PS: I'm still not sure whether this is the best solution to this problem, but it's a solution that works. Please let me know if you have a better way of solving this.

Here's the script:

set serveroutput on;
declare
    lock_handle number;
begin
    dbms_lock.allocate_unique('my_lock', lock_handle, 31536000);
    dbms_output.put_line('Dropping my_seq');
    execute immediate 'drop sequence my_seq';

    dbms_output.put_line('Creating my_seq');
    execute immediate 'create sequence my_seq minvalue 1 nocache increment by 1';

    dbms_output.put_line('Creating my_pkg declaration');
    execute immediate 'CREATE OR REPLACE PACKAGE "my_PKG" AS
  procedure reset_my_seq;
  function get_my_next_seq return number;
END;';

    dbms_output.put_line('Creating my_pkg definition');
    execute immediate 'CREATE OR REPLACE PACKAGE BODY "my_PKG" AS
  my_lock_handle number := ' || lock_handle || ';
  
  procedure reset_my_seq is
    lock_status integer;
    curr_val integer;
  begin
    lock_status := dbms_lock.REQUEST(lockhandle => my_lock_handle, lockmode => dbms_lock.x_mode);

    execute immediate ''alter sequence  my_seq minvalue 0'';
    execute immediate ''select my_seq.nextval from dual'' into curr_val ;
    execute immediate ''alter sequence my_seq increment by -'' || curr_val ;
    execute immediate ''select my_seq.nextval from dual'' into curr_val ;
    execute immediate ''alter sequence my_seq increment by 1'';

    lock_status := dbms_lock.release(lockhandle => my_lock_handle);
  end; 
  
  
  function get_my_next_seq return number is
    lock_status integer;
    next_seq number;
  begin
    lock_status := dbms_lock.REQUEST(lockhandle => my_lock_handle, lockmode => dbms_lock.s_mode);
    select my_seq.nextval into next_seq from dual;
    lock_status := dbms_lock.release(lockhandle => my_lock_handle);
    return next_seq;
  end;
END;';


    -- dbms_output.put_line('Dropping scheduler job');
    --  dbms_scheduler.drop_job('reset_my_seq');

    dbms_output.put_line('Creating scheduler job');
    dbms_scheduler.create_job( job_name=> 'reset_my_seq',
    job_type=>'PLSQL_BLOCK',
    job_action=> 'begin my_pkg.reset_my_seq; end;',
    start_date=> systimestamp,
    repeat_interval=>'frequency=daily;interval=1;byhour=0; byminute=0; bysecond=0;',
    enabled=>true);
    commit;
end;
/