How to check Oracle sequences

Question

We have a client that does {something}, and after that some of our sequences return numbers that have already been used. Although the long-term answer will be that they stop doing something, I need an easy way to check the sequences on the tables in which they are used.

I can query user_sequencesto get last_numberfor each sequence, and I can get max(id_number)for every table. But when I try to do both in the same request, I return null.

My broken SQL:

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

"last_number" user_sequences, Cache , last_number, , .

,

1) select <seq_name>.nextval from dual;

2) select <seq_name>.currval from dual;

u currval , nextval.

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

Answer 3

seq.nextval will work, but will also increase consistency. If you have already called nextval in the current session, you can call seq.currval.

If you call currval before calling nextval, it throws an exception.

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

Answer 4

try using seq.nextval :)

Take a look here: http://www.techonthenet.com/oracle/sequences.php

This is not a good estimate of the use of Max (in the case of the last line removed!).