May 11 2011

Change sequence’s current value (CURRVAL) without dropping it

Category: Database,Sequenceittichai @ 7:51 pm

This is not new but I’ve learned about it last week :-) from one of developers when reviewing codes with them for the plan to change the sequence’s current value. Normally I would drop the sequence and create it with START WITH to a new desired number. However, my approach will obviously invalidate all dependencies which commonly refer to triggers.

The trick is to change the INCREMENT BY value to the difference between the current value and the needed value, then use it once, finally switch the INCREMENT BY back to 1.

Here is a quick demo:

This TR_TAB_A_BI trigger depends on the SQ_TAB_A sequence.

create or replace trigger TR_TAB_A_BI
before insert on TAB_A
for each row
when (new.id is null)
begin 
	select SQ_TAB_A.nextval
	into :new.id
	from dual;
end;
/

Start with trigger valid.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

Let’s drop trigger.

SQL> drop sequence SQ_TAB_A;

Sequence dropped.

As expected, the trigger became invalid.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          INVALID

After the sequence is recreated, you can compile this trigger before use, or you can just execute an insert into the table, Oracle will recompile this trigger automatically before actual use.

SQL> create sequence SQ_TAB_A start with 243 cache 20;

Sequence created.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          INVALID

SQL> insert into TAB_A (B) values ('AA');

1 row created.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

Neat!

The other way to avoid triggers being invalidated at all, as mentioned in the first paragraph, is to change the INCREMENT BY value.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

Assuming the current value is 300, we want a new current value to be 500. So the difference is 200.

SQL> alter sequence SQ_TAB_A increment by 200 nocache;

Sequence altered.

SQL> select SQ_TAB_A.nextval from dual;

   NEXTVAL
----------
       500

SQL> alter sequence SQ_TAB_A increment by 1 cache 20;

Sequence altered.

With all changes in sequence, the trigger will remain valid.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

Tags: , , , , , ,

3 Responses to “Change sequence’s current value (CURRVAL) without dropping it”

  1. Gino Law says:

    I gotta bookmark this site it seems handy handy.

  2. Monir says:

    Very helpfull. thanks

  3. tyler says:

    That’s a useful tip! I usually write a loop to increment the sequence. That wouldn’t work if the sequence is to be decremented, but then you don’t want that most of the time!

    SQL>
    SQL> select my_seq.currval from dual;

    CURRVAL
    ———-
    21

    1 row selected.

    SQL>
    SQL> — Let’s say I want to increment this sequence value till 30
    SQL>
    SQL> var n number;
    SQL>
    SQL> exec for i in 1..(30 – 21) loop :n := my_seq.nextval; end loop;

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select my_seq.currval from dual;

    CURRVAL
    ———-
    30

    1 row selected.

    SQL>
    SQL>

Leave a Reply