无中文版本
Many people believe that the only way to reset a sequence is to drop it and recreate it again. This causes a number of problems for any object - functions, procedures etc - which rely of the sequence as it renders them invalid as the following small example shows :
SQL> -- Create a test sequence
SQL> create sequence test_seq;
Sequence created.
SQL> -- Create a test table
SQL> create table test(a number);
Table created.
SQL> -- Create a trigger on the table to use the test sequence
SQL> CREATE OR REPLACE TRIGGER test_bi
2 BEFORE INSERT ON TEST
3 FOR EACH ROW
4 BEGIN
5 IF (:NEW.a IS NULL) THEN
6 SELECT test_seq.NEXTVAL INTO :NEW.a FROM dual;
7 END IF;
8 END;
9 /
Trigger created.
SQL> -- Insert a few rows - firing the trigger and using the sequence
SQL> insert into test (a) values (NULL);
1 row created.
SQL> r
1* insert into test (a) values (NULL)
1 row created.
SQL> r
1* insert into test (a) values (NULL)
1 row created.
SQL> r
1* insert into test (a) values (NULL)
1 row created.
SQL> -- Check results
SQL> select * from test;
A
----------
1
2
3
4
SQL> commit;
Commit complete.
SQL> -- Check status of trigger. Note that USER_TRIGGERS shows it as ENABLED but
SQL> -- USER_OBJECTS shows VALID.
SQL> select status from user_objects
2 where object_name = 'TEST_BI';
STATUS
-------
VALID
SQL> select status from user_triggers
2 where trigger_name = 'TEST_BI';
STATUS
--------
ENABLED
SQL> -- Drop the sequence which will invalidate the trigger
SQL> drop sequence test_seq;
Sequence dropped.
SQL> -- Note that USER_TRIGGERS still says ENABLED but USER_OBJECTS shows INVALID now !
SQL> select status from user_triggers
2 where trigger_name = 'TEST_BI';
STATUS
--------
ENABLED
SQL> select status from user_objects
2 where object_name = 'TEST_BI';
STATUS
-------
INVALID
So now the trigger is invalid and will remains so until such time as the sequence is recreated and either the trigger manually compiled or an insert into the table is carried out. On the insert, the trigger will be recompiled and will once more become valid. This is useful, but will tend to cause a slowdown in user responses if the trigger has to be compiled on first use.
SQL> create sequence test_seq; Sequence created. SQL> insert into test (a) values (NULL); 1 row created. SQL> commit; Commit complete. SQL> select status from user_objects where object_name = 'TEST_BI'; STATUS ------- VALID
As this recompiling could affect many other objects, it is advisable to avoid it. There is a way to reset a sequence back to its start value without having to drop it, and most importantly, without having to invalidate any objects.
We start by finding out some information about the sequence by querying USER_SEQUENCES as follows :
SQL> select sequence_name, min_value, max_value, increment_by, cache_size, last_number 2 from user_sequences 3* where sequence_name = 'TEST_SEQ' SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ ---------- ----------- TEST_SEQ 1 1.0000E+27 1 20 21
Make a note of the increment size and min_value for later. Note that this sequence is cached, so the value in LAST_NUMBER may not be the correct value as it is not the last value 'handed out' by the sequence. The only way to get at the correct value is to select the NEXTVAL from the sequence.
SQL> select test_seq.nextval from dual
NEXTVAL
----------
2
Now we have the last number, we can negate it to give a new increment for the sequence, however, because the MINVALUE is 1 we need to allow it to become zero - or the change to the sequence will fail. Simply alter the sequence and set a new INCREMENT_BY and MINVALUE as follows :
SQL> alter sequence test_seq 2 increment by -2 3 minvalue 0; Sequence altered.
The sequence is still not reset. In order to do this, we need to select its NEXTVAL again which will increment the current value (2) by -2 giving us zero.
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
0
Note that we had to allow the MINVALUE to be zero to allow the dummy select we did to reset the current value to zero. Why not simply set the increment to -1 instead ? Well, we could have, but then we have left ourselves with a problem because, we want to reset the sequence back to its original starting value - which is 1 - so that the first time it is used after our adjustments, the value returned is 1. If we simply set the increment to -1 we would indeed get 1 as the next value. Then we would have to monitor the sequence to find out when it was first used, and before it was used again, reset the increment to 1 again so that the sequence is once more counting upwards rather than down. In addition, if we missed the first usage, the user would get an error on subsequent usage because we are trying to reduce the value below that which we set as the minimum.
Now we have successfully reset the sequence back to its original state as the next selection from it will give the value 1 and that was our original intent.
NOTE we have actually changed the sequence as its original MINVALUE was 1 but now it is zero. This might cause a problem on some installations if zero is not allowed. If so, the MINVALUE will need to be adjusted after the first use of the sequence. The chance of a problem is quite minimal unless the sequence is allowed to CYCLE around when it reaches the MAXVALUE.
Once again, checking the status of the trigger shows that it is still valid - it has not been affected by the changes we made to the sequence.
SQL> select status from user_objects where object_name = 'TEST_BI'; STATUS ------- VALID
Footnote The original answer to this FAQ was the link below to a document written by Howard J Rogers. Unfortunately, Oracle Australia requested that Howard take down his web site and so the document is no longer available officially. This rewrite of the FAQ answer is not a blatent copy of Howard's document, but is my own work - which addmittedly is influenced by Howard. The link has been maintained in case Howards gets permission to re-open the site.
如果给你带来帮助，欢迎微信或支付宝扫一扫，赞一下。
