On Oracle: How to determine session's CURRVAL of an oracle sequence without error and without modify ?

Any reference to CURRVAL always returns the last reference to NEXTVAL in the current session. Each session caches its own previous call of NEXTVAL. CURRVAL is session private, whereas NEXTVAL is DB wide global and unique.

If you use CURRVAL without previous call of NEXTVAL, you will get error:

  ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session

If you call NEXTVAL, it will modify the sequence.

So how can we determine session's CURRVAL of a sequence without error and without modify ?

The similar question is asked in:
How to retrieve the current value of an oracle sequence without increment it?

Note: Tested on Oracle 19c in Linux

First we create a sequence:


create sequence test_seq_1;

Then open a new Sqlplus session, make a session heapdump at level 29 (PGA, UGA, CGA, top call heaps, call heaps, session heap):


alter session set tracefile_identifier = 'without_currval';
alter session set events 'immediate trace name heapdump level 29';

In the dump, searching "SEQ CACHE", we found:


  7f0b13d3db18 sz=      504    cprm      "SEQ CACHE      "

Pick this address and make a heapdump_addr:


alter session set tracefile_identifier = 'seq_1';
alter session set events 'immediate trace name heapdump_addr level 2, addr 0x7f0b13d3db18';

Then search around "addr 0x7f0b13d3db18", we can see the cached sequence: SYS.AUDSES$. There is no "SEQ CACHE" for test_seq_1.


7F0B13D3DB10 00000000 00000000 000001F9 00B38F00  [................]
7F0B13D3DB20 147F8A9C 00000000 1450F9A0 00007F0B  [..........P.....]
7F0B13D3DB30 1450F9A0 00007F0B 55410007 53455344  [..P.......AUDSES]
7F0B13D3DB40 00000024 00000000 00000000 00000000  [$...............]
7F0B13D3DB50 00000000 00000000 00000000 00000000  [................]
        Repeat 5 times
7F0B13D3DBB0 00000000 00000000 00070000 53445541  [............AUDS]
7F0B13D3DBC0 00245345 00000000 00000000 00000000  [ES$.............]
7F0B13D3DBD0 00000000 00000000 00000000 00000000  [................]
        Repeat 5 times
7F0B13D3DC30 00000000 00000000 00000000 59530003  [..............SY]
7F0B13D3DC40 00000053 00000000 00000000 00000000  [S...............]
7F0B13D3DC50 00000000 00000000 00000000 00000000  [................]
        Repeat 7 times
7F0B13D3DCD0 00000169 4EC4000C 00542706 00000000  [i......N.'T.....]

For SYS.AUDSES$, a session connected to an Oracle database may obtain one of its session identifiers, the Auditing Session ID (V$SESSION.AUDSID), by use of the built-in USERENV SQL function
(MOS Docu: How Sessions get Their AUDSID Identifier (Doc ID 122230.1)).


select AUDSID, dump(AUDSID, 16) AUDSID_dump, userenv('SESSIONID'), s.sid from v$session s where sid = sys.dbms_support.mysid;
 
    AUDSID  AUDSID_DUMP                   USERENV('SESSIONID')   SID
  --------  ----------------------------  -------------------- -----
  77053883  Typ=2 Len=5: c4,4e,6,27,54                77053883   189

In address line: 7F0B13D3DCD0, we can see AUDSID=77053883 in internal form (little endian): "4EC4000C 00542706".

Now we call test_seq_1.nextval:


select test_seq_1.nextval, dump(1, 16) dump from dual;
 
   NEXTVAL   DUMP
  -------   ------------------
        1   Typ=2 Len=2: c1,2

Make again a session heapdump:


alter session set tracefile_identifier = 'with_currval';
alter session set events 'immediate trace name heapdump level 29';

There is one entry about "SEQ CACHE" (SYS.AUDSES$ is no more cached, and its cache address is re-used).
The "SEQ CACHE" is located in "session heap".


HEAP DUMP heap name="session heap"  desc=0x7f0b14509568
  7f0b13d3db18 sz=      504    cprm      "SEQ CACHE      "
HEAP DUMP heap name="pga heap"  desc=0x7f0b192fd220
HEAP DUMP heap name="top call heap"  desc=0x7f0b192ffb40
HEAP DUMP heap name="top uga heap"  desc=0x7f0b192ffe00
HEAP DUMP heap name="callheap"  desc=0x7f0b192fe160
HEAP DUMP heap name="callheap"  desc=0x7f0b192fe160

Pick address: 7f0b13d3db18 and make a heapdump_addr:


alter session set tracefile_identifier = 'seq_2';
alter session set events 'immediate trace name heapdump_addr level 2, addr 0x7f0b13d3db18';
 
7F0B13D3DB10 00000000 00000000 000001F9 00B38F00  [................]
7F0B13D3DB20 147F8A9C 00000000 1450F9B0 00007F0B  [..........P.....]
7F0B13D3DB30 1450F9B0 00007F0B 4554000A 535F5453  [..P.......TEST_S]
7F0B13D3DB40 315F5145 00000000 00000000 00000000  [EQ_1............]
7F0B13D3DB50 00000000 00000000 00000000 00000000  [................]
        Repeat 5 times
7F0B13D3DBB0 00000000 00000000 000A0000 54534554  [............TEST]
7F0B13D3DBC0 5145535F 0000315F 00000000 00000000  [_SEQ_1..........]
7F0B13D3DBD0 00000000 00000000 00000000 00000000  [................]
        Repeat 5 times
7F0B13D3DC30 00000000 00000000 00000000 004B0001  [..............K.]
7F0B13D3DC40 00000000 00000000 00000000 00000000  [................]
        Repeat 7 times
7F0B13D3DCC0 96BA7940 00000000 906F3D38 00000000  [@y......8=o.....]
7F0B13D3DCD0 00472B44 02C10009 00542706 00000000  [D+G......'T.....]

In address line: 7F0B13D3DCD0, we can see test_seq_1.nextval=1 in internal form (little endian): "02C1".

From above dumps, we can see that each sequence used in the session has one "SEQ CACHE" for its currval with sz= 504.
The cached currval is stored from offset 444:


select to_number('7F0B13D3DCD0', 'XXXXXXXXXXXX') + 4 - to_number('7F0B13D3DB18', 'XXXXXXXXXXXX') offset from dual;
 
   OFFSET
  -------
      444

kdnnxt and kdncur Subroutines

The call stack for nextval and kdncur shows that internal function kdnnxt and kdncur are called.


--==== select test_seq_1.nextval from dual;
 
#0   kdnnxt ()
#1   qersqPopulate ()
#2   qersqRowProcedure ()
#3   qerfiFetch ()
#4   qersqFetch ()
#5   opifch2 ()
#6   kpoal8 ()
#7   opiodr ()
#8   ttcpip ()
#9   opitsk ()
#10  opiino ()
#11  opiodr ()
#12  opidrv ()
#13  sou2o ()
#14  opimai_real ()
#15  ssthrdmain ()
#16  main ()
 
 
--==== select test_seq_1.currval from dual;                          
                                
#0   kdncur ()
#1   qersqStart ()
#2   selexe0 ()
#3   opiexe ()
#4   kpoal8 ()
#5   opiodr ()
#6   ttcpip ()
#7   opitsk ()
#8   opiino ()
#9   opiodr ()
#10  opidrv ()
#11  sou2o ()
#12  opimai_real ()
#13  ssthrdmain ()
#14  main ()

Wednesday, February 23, 2022

How to determine session's CURRVAL of an oracle sequence without error and without modify ?

kdnnxt and kdncur Subroutines