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>