Oracle sequence last number check and creation clauses.

Sometimes for example when you are updating database you might need to check that sequences last used value does not change. This can be done running following SQL before and after update and then comparing the result sets (this show only given schema sequences):

SQL> spool test_schema_name_sequences_last_num_before.txt

SQL> set pages 120
SQL> set lines 120

SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences where SEQUENCE_OWNER = '<TEST_SCHEMA_NAME'>) order by 2;

SQL> spool off;


# Again after changes:

SQL> spool test_schema_name_sequences_last_num_after.txt

SQL> set pages 120
SQL> set lines 120
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences where SEQUENCE_OWNER = '<TEST_SCHEMA_NAME'>) order by 2;
SQL> spool off;


NOTE! Remember that last_number column value is aware of cache. If you have cache 20 last_number is increased 20 every time next cache set is taken.  And because of this you can't depend only this column when cache is used you might need also check current_value from sequence. If cache is not used at all then last_number value is increased only when next value is taken from sequence and last_number is same as current_value.


If you want to get schema sequences creation clauses out of the database you can do it  with following SQL:
SQL> spool test_schema_name_sequences_ddl.txt

SQL> set pages 120
SQL> set lines 120 
SQL> select dbms_metadata.get_ddl('SEQUENCE',ds.sequence_name,ds.sequence_owner) from dba_sequences ds where ds.sequence_owner='<TEST_SCHEMA_NAME>';

SQL> spool off;