Proves that recursive session exists and explains why it is not displayed in v $ session

This article describes how to prove that a recursive session exists and explains why it is not displayed in the v $ session. If you need to know it, go to the reference page.

When we use the database, we sometimes encounter similar situations. My session is logged on, but I do some operation and report insufficient sessions. this proves that other sessions are generated in the SQL background, and the existence of recursive sessions is proved through experimental analysis.
The session creation table reports that the session has exceeded
CDB_PDB @ CHF> create table t_xifenfei (id number );
Create table t_xifenfei (id number)

ERROR at line 1:
ORA-00018: maximum number of sessions exceeded

There is a problem here: the current session has been successfully logged on, prove that the current session is enough, but why is it still reported when the table creation operation is executed? Continue analysis by 10046
CDB_PDB @ CHF> alter session set events '10046 trace name context forever, LEVEL 12 ';
 
The session has been changed.
 
CDB_PDB @ CHF> create table t_xifenfei as select * from dual;
 
The table has been created.
 
CDB_PDB @ CHF> select value from v $ diag_info where name = 'default Trace file ';
 
VALUE
--------------------------------------------------------------------------------
E: APPXIFENFEIdiagrdbmscdbcdbtracecdb_ora_6596.trc

Analyze the trace file
CDB_PDB @ CHF> host tkprof E: APPXIFENFEIdiagrdbmscdbcdbtracecdb_ora_6596.trc d:/1.txt
 
-- Check the trace file and find that there are many base table operations in it. Take one of the tabs $ table for analysis. The table creation process involves the following insert operations:
Insert into tab $ (obj #, ts #, file #, block #, bobj #, tab #, intcols, kernelcols, clucols,
Audit $, flags, pctfree $, pctused $, initrans, maxtrans, rowcnt, blkcnt, empcnt,
Avgspc, chncnt, avgrln, analyzetime, samplesize, cols, property, degree, instances,
Dataobj #, avgspc_flb, flbcnt, trigflag, spare1, spare6)
Values
(: 1,: 2,: 3,: 4, decode (: 5, 0, null,: 5), decode (: 6, 0, null,: 6),: 7 ,: 8, decode (: 9, 0, null,
: 9),: 10,: 11,: 12,: 13,: 14,: 15,: 16,: 17,: 18,: 19,: 20,: 21,: 22,: 23,: 24,: 25,
Decode (: 26,1, null,: 26), decode (: 27,1, null,: 27),: 28,: 29,: 30,: 31,: 32,: 33)

Try to insert manually
CDB_PDB @ CHF> insert into sys. tab $ select * from sys. tab $ where rownum = 1;
Insert into sys. tab $ select * from sys. tab $ where rownum = 1
*
Row 3 has an error:
ORA-01031: insufficient Permissions

This proves that the current session for creating a table does not have the permission to directly operate the tab $ table, proving that there should be other tables to operate on it.

V $ session view base table
Query the V $ FIXED_VIEW_DEFINITION view to obtain related SQL statements. Different versions may be different, but are generally consistent.
/* Formatted on 23:09:30 (QP5 v5.227.12220.20.54 )*/
SELECT inst_id,
Addr,
Indx,
Ksuseser,
Ksuudses,
Ksusepro,
Ksuudlui,
Ksuudlna,
Ksuudoct,
Ksusesow,
DECODE (ksusetrn, HEXTORAW ('00'), NULL, ksusetrn ),
DECODE (ksqpswat, HEXTORAW ('00'), NULL, ksqpswat ),
DECODE (BITAND (ksuseidl, 11 ),
1, 'active ',
0, DECODE (BITAND (ksuseflg, 4096), 0, 'inactivity', 'cached '),
2, 'sniped ',
3, 'sniped ',
'Killed '),
DECODE (ksspatyp,
1, 'dicated ',
2, 'shared ',
3, 'sudo ',
'None '),
Ksuudsid,
Ksuudsna,
Ksuseunm,
Ksusepid,
Ksusemnm,
Ksusetid,
Ksusepnm,
DECODE (BITAND (ksuseflg, 19 ),
17, 'background ',
1, 'user ',
2, 'cursion ',
'? '),
Ksusesql,
Ksusesqh,
Ksusepsq,
Ksusepha,
Ksuseapp,
Ksuseaph,
Ksuseact,
Ksuseach,
Ksusecli,
Ksusefix,
Ksuseobj,
Ksusefil,
Ksuseblk,
Ksuseslt,
Ksuseltm,
Ksusectm,
DECODE (BITAND (ksusepfl, 16), 0, 'No', 'yes '),
DECODE (ksuseft,
2, 'session ',
4, 'select ',
8, 'transactional ',
'None '),
DECODE (ksusefm,
1, 'Basic ',
2, 'preconnect ',
4, 'preparse ',
'None '),
DECODE (ksusefs, 1, 'yes', 'no '),
Ksusegrp,
DECODE (BITAND (ksusepfl, 16 ),
16, 'enabled ',
DECODE (BITAND (ksusepfl, 32), 32, 'Forced ', 'Disabled ')),
DECODE (BITAND (ksusepfl, 64 ),
64, 'Forced ',
DECODE (BITAND (ksusepfl, 128), 128, 'Disabled ', 'enabled ')),
Decodes (BITAND (ksusepfl, 512 ),
512, 'Forced ',
DECODE (BITAND (ksusepfl, 256), 256, 'Disabled ', 'enabled ')),
Ksusecqd,
Ksuseclid
FROM x $ ksuse
Where bitand (ksspaflg, 1 )! = 0 and bitand (ksuseflg, 1 )! = 0

Note: The v $ session query must be BITAND (ksuseflg, 1 )! Records = 0

Test by locking the table
CDB_PDB @ SYS indicates the sys user, and CDB_PDB @ CHF indicates the chf user. Two sessions are used for testing by different users.
CDB_PDB @ SYS> show user;
USER is "SYS"
 
-- SYS user locks the table
CDB_PDB @ SYS> lock table tab $ IN exclusive MODE;
 
The table is locked.
 
CDB_PDB @ CHF> show user;
The USER is "CHF"
CDB_PDB @ CHF> select sid from v $ mystat where rownum = 1;
 
SID
----------
57
 
CDB_PDB @ CHF> select paddr from v $ session where sid = 57;
 
PADDR
----------------
000007FF1E10F228
 
-- The CHF user creates a table.
CDB_PDB @ CHF> create table t_xifenfei_new as select * from dual;
 
-- SYS user query
CDB_PDB @ SYS> SELECT s. addr,
2 s. indx sid,
3 s. ksuseser SERIAL #,
4 ksuudsna username,
5 DECODE (BITAND (ksuseflg, 19 ),
6 17, 'background ',
7 1, 'user ',
8 2, 'recorsion ',
9 '? ')
10 TYPE
11 FROM x $ ksuse s
12 WHERE ksusepro = '000007ff1e10f228 ';
 
Addr sid serial # USERNAME TYPE
----------------------------------------------------------------------------
000007FF1E1EBEA0 57 23 CHF USER
000007FF1E1D7F90 67 183 SYS RECURSIVE
 
CDB_PDB @ SYS> SELECT ksuudsna username,
2 ksuseflg
3 FROM x $ ksuse s
4 WHERE ksusepro = '000007ff1e10f228 ';
 
USERNAME KSUSEFLG
----------------------------------------
CHF 1, 135266369
SYS 2
 
-- Here we find that the SQL called by recursive sys is excluded from the v $ session view, so the session of recursive SQL cannot be displayed in the v $ session
CDB_PDB @ SYS> select bitand (2, 1) from dual;
 
BITAND (2, 1)
-----------
0

So far, we can verify that our current session, when creating a table, has a sys recursive session that executes operations on the base table, however, because the v $ session view filters some records in the x $ ksuse table, we cannot view these recursive sessions in the v $ session.

Continue to analyze bitand Functions
By observing the creation Statement of v $ session, we can find the following rule. If a session is a recursive session, BITAND (ksuseflg, 19) = 2, when the value is 2, is BITAND (ksuseflg, 1) always 0? The bitand function is actually to convert the two parameters into binary values and then perform the and operation, that is, if the two corresponding bits are 1, the result will be 1 (bitand () = 1, bitand (10011) = 0), here we can find that 19 is converted to binary, to make BITAND (ksuseflg, 19) = 2 true, that is, after ksuseflg is converted to binary, the last bit must be 0, while BITAND (ksuseflg, 1) must be 0. Therefore, recursive sessions will not be displayed in the v $ session view.