How to verify Oracle sequences

Question

We have a customer who does {something}, and after that some of our sequences are returning numbers that have already been used. While the long term answer would be for them to stop doing {something}, I need a simple way to check the sequences against the tables they are used in.

I can query user_sequences to get the last_number for each sequence and I can get the max(id_number) for each table. But when I try to do both in the same query I get nulls back.

My broken SQL is:

select max(last_number) , max(id_number) from user_sequences,
squiggly.ACCOUNT_CODE_DEFINITION where sequence_name = 'ACCOUNT_CODE_DEFINITION_SEQ' 
and sequence_owner = 'SQUIGGLY' ;

Answer 1

you can get the MAX from both tables with this query:

SELECT (SELECT last_number
          FROM all_sequences
         WHERE sequence_name = 'ACCOUNT_CODE_DEFINITION_SEQ'
           AND sequence_owner = 'SQUIGGLY') max_sequence,
       (SELECT MAX(id_number) 
          FROM squiggly.ACCOUNT_CODE_DEFINITION) max_id_number
  FROM dual

Answer 2

I would suggest never trust "last_number" of user_sequences because if Cache is enabled while creating the sequence , then the last_number is likely to contain a value greater than current value of the sequence.

Well ,i used below steps

1) select <seq_name>.nextval from dual;

2) select <seq_name>.currval from dual;

Since u can't execute currval alone for the first time , so i executed nextval first.

SQL> create sequence seq;

Sequence created.

SQL> select last_number from user_sequences;

LAST_NUMBER
-----------
          1

SQL> select seq.nextval from dual;

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

SQL> select seq.currval from dual;

   CURRVAL
----------
         1

SQL> select last_number from user_sequences;

LAST_NUMBER
-----------
         21

SQL> select seq.currval from dual;

   CURRVAL
----------
         1