Sequence Info

 



define sequence_name='SEQ'

set feed off
set pagesize 10000
set wrap off
set linesize 200
set heading on
set tab on
set scan on
set verify off

set numf 999999999999999999999999
column 		datum new_value datum noprint
column 		sequence_owner 	format A20 					heading 'Sequence|Owner'
column 		sequence_name 	noprint new_value sequence
column 		min_value 	format 99						heading 'Minimal|Value  '
column 		max_value 	format 99999999999999999999999999999999 heading 'Maximal    |Value      '
column 		increment_by 	format 99 					heading 'Incr|By  '
column 		last_number 	format 999999999999 		heading 'Last    |Number  '
column 		cache_size 		format 9999999999999 		heading 'Cache|Size '
column 		order_flag 		format a7 					heading 'Order ?'
column 		cycle_flag 		format a7 					heading 'Cycle ?'

ttitle left 'Sequence Info 'sequence skip 2

break on sequence_name skip page -
on sequence_owner skip 2 -

select  to_char(sysdate, 'DD-MM-YY') datum,
    substr(sequence_owner,1,12) sequence_owner,
    substr(sequence_name,1,27) sequence_name,
    min_value,
    max_value,
    increment_by,
    last_number,
    cache_size,
                decode(order_flag,
                  'Y','Yes',
                  'N','No') order_flag,
                decode(cycle_flag,
                  'Y','Yes',
                  'N','No') cycle_flag
from dba_sequences
where 1=1
--and sequence_owner LIKE UPPER('user')
and     sequence_name like upper('&sequence_name')
order by 1,2,3,4;


define sequence_name='SEQ'
set feed off
set pagesize 10000
set wrap off
set linesize 200
set heading on
set tab on
set scan on
set verify off
--


ttitle left 'Shows next sequence number without incrementing it' skip 2


col sequence_owner format a20 					heading 'Sequence|Owner'
col sequence_name  format a25 					heading 'Sequence|Name'
col next_seq       format 9999999999999999999 	heading 'Next|Value'
col cache          format a25 					heading 'Cache'

select sequence_owner, sequence_name, next_seq, cache
from (select sequence_owner, sequence_name,
       nextvalue next_seq, 'in cache' cache
      from v$_sequences
      where nextvalue is not null
       union
      select sequence_owner, sequence_name,
       highwater next_seq, 'created nocache' cache
      from v$_sequences
      where nextvalue is null
       union
      select sequence_owner, sequence_name,
       last_number next_seq, 'not in cache' cache
      from dba_sequences s
      where not exists (select  sequence_owner, sequence_name
                        from v$_sequences v
                        where v.sequence_name = s.sequence_name
                        and   v.sequence_owner = s.sequence_owner))
where 1=1
--and  sequence_owner like upper('sequence_owner')
and     sequence_name like upper('&sequence_name')
order by sequence_owner, sequence_name
/