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?

5 Responses to Sequences – where did the view go?

  1. Neil,

    This sounds very similar to an issue I had with datadictionary views in 12.1.0.1.
    See here:
    http://stackoverflow.com/questions/27880179/why-is-my-plsql-code-missing-in-the-datadictionary-in-oracle-12c

    Basically: My plsql source (packages/procedures etc) did not show up in the *_source views, but when I execute the query that made up the view the source was there.

    According to Tom Kyte I had probably run into one of the bugs in the datadictionary views that where introduced whith all the changes needed to support container/pluggable database.

    Sounds to me like you found a bug that hasn’t been addressed in 12.1.0.2.

  2. jgarry says:

    But does dba_sequences work? And what does it look at?

    • Sorry for the delay – I’ve been involved in a major migration for a client for the last week.

      I’ve not seen any issues with DBA_SEQUENCES. That’s a dictionary view, reading “real” tables sys.seq$, sys,obj$ and sys.user$, rather than a window on an SGA memory structure. However, DBA_SEQUENCES does not display the current value in each DB instance for each sequence, or even show if the sequence is in the SGA at all (which is a useful aspect of gv$_sequences). The “last number” in DBA_SEQUENCES is only updated when a cache is refreshed, not when individual sequence numbers are used, and shows the next start point value to be pulled out of the cache when a sequence requests it. The .NEXTVAL value will always be less than or equal to this number.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: