How to verify Oracle sequences

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' ;

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

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

seq.nextval will work but also increment the sequence. If you already called nextval in the current session you can call seq.currval.

If you call currval before you call nextval it will throw an exception.

I would do:
select last_number from user_sequences where sequence_name = 'seq'

try to use seq.nextval :)

Look here : http://www.techonthenet.com/oracle/sequences.php

It's not a good pratice to use the Max (in case of the last row have beed deleted !).