Writing verification steps for PL/SQL Sequence

I am writing the launch plan for one of my code. In which, for each step i have to write a verification step which will tell if the deployment/change has been made properly. Like if there is an alter table command to add a new column then as a verification, I would be using select column_name from table as verification step.

I am looking for a verification step for my sequence scrip开发者_开发技巧t. My sequence scripts is dropping the old sequence and re-creating it with initial value changed. lets say if my last sequece was on 10071 - the new sequece would start from 100710.

I wrote the following query

SELECT LAST_NUMBER 
  FROM all_sequences 
 WHERE sequence_name = 'SEQNAME';

Now, my question is, would that give the last number of my new sequence or the old sequence?

P.S: I can't use sequence NextValue - it would cause the system to miss 1 number and will mess up the whole system. However, I am open for the options in which my *next_value* can be utlizied, automatically.

Now, my question is, would that give the last number of my new sequence or the old sequence?

Err ... Why not just try it as suggested by Randy in the comments ?

SQL> !cat /tmp/sql.sql

create sequence foo start with 10;

select min_value, max_value, increment_by, last_number
from user_sequences
where sequence_name = 'FOO';

drop sequence foo;

create sequence foo start with 20;

select min_value, max_value, increment_by, last_number 
from user_sequences 
where sequence_name = 'FOO';

drop sequence foo;

SQL> @/tmp/sql

Sequence created.


 MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
---------- ---------- ------------ -----------
         1 1.0000E+28            1          10


Sequence dropped.


Sequence created.


 MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
---------- ---------- ------------ -----------
         1 1.0000E+28            1          20


Sequence dropped.

SQL>

LAST_NUMBER will not be accurate if your sequence uses a cache (which is the default)

SQL> CREATE SEQUENCE seq_1;

Sequence created.

SQL>
SQL> SELECT seq_1.nextval FROM dual;

   NEXTVAL
----------
         1

SQL>
SQL> SELECT sequence_name,
  2         min_value,
  3         last_number
  4  FROM user_sequences
  5  WHERE sequence_name = 'SEQ_1';

SEQUENCE_NAME                   MIN_VALUE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_1                                   1          21

SQL>
SQL> SELECT seq_1.nextval FROM dual;

   NEXTVAL
----------
         2

SQL>
SQL> SELECT sequence_name,
  2         min_value,
  3         last_number
  4  FROM user_sequences
  5  WHERE sequence_name = 'SEQ_1';

SEQUENCE_NAME                   MIN_VALUE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_1                                   1          21

SQL>