Sequences – where did the view go?

I was pottering around with the sequence cache recently, investigating a few things like exactly when it refreshes (such as if it is flushed or aged from the shared pool – pin it!) and I was monitoring the next value using V$_SEQUENCE, like I have been since, erm, Oracle 8i in 1999 or summit.

I happened to log into an Oracle 12.1.0.2 DB for a check-test and it was empty! My first reaction was, how come there are no sequences cached in the shared pool, shortly followed by No! The view doesn’t work any more!

Right, it’s a v$ view. (Actually, it’s a V$_ view, of which the only other one I know is V_$_lock). It is based upon c-memory-structure-plus-helper x$ “tables” (warning, these tables required tuning/diagnostic licensing packs to query!), so let’s query the underlying object view: x$kglob.

What used to happen pre-Oracle 12c:

SYS @ orcl > select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

select test_seq.nextval from dual;
NEXTVAL
----------
40067

select sequence_owner,sequence_name,nextvalue,cache_size from v$_sequences where sequence_name = 'TEST_SEQ'
/
SEQUENCE_OWNER                 SEQUENCE_NAME                  NEXTVALUE  CACHE_SIZE
------------------------------ ------------------------------ ---------- ----------
NEIL                           TEST_SEQ                            40068         20

select KGLNAOBJ,
KGLOBTN0 next_val,
KGLOBTN4 cache_size,
kglhdkmk kept
from x$kglob
where KGLOBTYP=6
and KGLNAOBJ = 'TEST_SEQ'
/
KGLNAOBJ             NEXT_VAL   CACHE_SIZE KEPT
-------------------- ---------- ---------- ----------
TEST_SEQ                  40068         20          0

And now what happened in Oracle 12:

SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0

select test_seq.nextval from dual;
NEXTVAL
----------
3

select sequence_owner,sequence_name,nextvalue,cache_size from v$_sequences where sequence_name = 'TEST_SEQ';

no rows selected

select KGLNAOBJ,
KGLOBTN0 next_val,
KGLOBTN4 cache_size,
kglhdkmk kept
from x$kglob
where KGLOBTYP=6
and KGLNAOBJ = 'TEST_SEQ';

KGLNAOBJ NEXT_VAL CACHE_SIZE KEPT
-------------------- ---------- ---------- ----------
TEST_SEQ 0

So the values are no longer exposed in Oracle 12c. NEXT_VAL and CACHE_SIZE are empty.

Does anybody know where to get this value? It's no longer in any column in X$KGLOB. Have Oracle hidden it in some weird container mode I don't understand yet? Can anybody help, or am I going to have to attach the SGA with a C program and step through a bunch of linked lists?

%d bloggers like this: