Proof that recursive session exists and explains why it is not displayed in V$session

We are in the process of using the database, sometimes encountered similar situation, my session is logged in, but I do some kind of operation, lack of newspaper session is insufficient. This proves that the SQL backend also generated other sessions, which proved the existence of recursive conversation through experimental analysis.
Session Creation table report sessions super
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

Here's the problem: the current session has been logged in successfully, proving that the current sessions are sufficient, but why do you still report ORA-00018 when you perform the CREATE table operation? Continue analysis through 10046
Cdb_pdb@chf> alter session SET Events ' 10046 TRACE NAME context FOREVER, Level 12 ';

The session has changed.

Cdb_pdb@chf> CREATE TABLE T_xifenfei as SELECT * from dual;

Table has been created.

Cdb_pdb@chf> Select value from V$diag_info where Name= ' Default Trace File ';

VALUE
--------------------------------------------------------------------------------
E:appxifenfeidiagrdbmscdbcdbtracecdb_ora_6596.trc

Parse trace file
cdb_pdb@chf> host tkprof  e:appxifenfeidiagrdbmscdbcdbtracecdb_ora_6596.trc d:/1.txt
&NBSP
--View the trace file, found that there are many base table operations, take one of the tab$ table analysis, create a table procedure with the following insert Operation
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)

Attempt to insert manually
cdb_pdb@chf> INSERT INTO sys.tab$ select * from sys.tab$ where rownum=1;
INSERT INTO sys.tab$ select * sys.tab$ where rownum=1
*
Line 1th Error:
ORA-01031: Insufficient Permissions

proves that the session currently executing the CREATE table does not have permission to directly manipulate the tab$ table, proving that there should be other tables to manipulate it

V$session View Graph Table
Query V$fixed_view_definition view to get the relevant SQL statements, different versions may vary, but generally consistent
/* Formatted on 2013/11/8 23:09:30 (QP5 v5.227.12220.39754) * *
SELECT inst_id,
Addr
Indx
Ksuseser,
Ksuudses,
Ksusepro,
Ksuudlui,
Ksuudlna,
KSUUDOCT,
Ksusesow,
DECODE (KSUSETRN, Hextoraw ('), NULL, KSUSETRN),
DECODE (Ksqpswat, Hextoraw ('), NULL, Ksqpswat),
DECODE (Bitand (Ksuseidl, 11),
1, ' ACTIVE ',
0, DECODE (Bitand (KSUSEFLG, 4096), 0, ' INACTIVE ', ' CACHED '),
2, ' sniped ',
3, ' sniped ',
' Killed '),
DECODE (Ksspatyp,
1, ' dedicated ',
2, ' SHARED ',
3, ' PSEUDO ',
' NONE '),
Ksuudsid,
Ksuudsna,
KSUSEUNM,
Ksusepid,
KSUSEMNM,
Ksusetid,
KSUSEPNM,
DECODE (Bitand (KSUSEFLG, 19),
' BACKGROUND ',
1, ' USER ',
2, ' recursive ',
'?'),
Ksusesql,
KSUSESQH,
KSUSEPSQ,
Ksusepha,
Ksuseapp,
Ksuseaph,
Ksuseact,
Ksuseach,
KSUSECLI,
Ksusefix,
Ksuseobj,
Ksusefil,
KSUSEBLK,
KSUSESLT,
Ksuseltm,
KSUSECTM,
DECODE (Bitand (KSUSEPFL), 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),
, ' ENABLED ',
DECODE (Bitand (KSUSEPFL,), ' forced ', ' DISABLED '),
DECODE (Bitand (KSUSEPFL, 64),
, ' forced ',
DECODE (Bitand (KSUSEPFL, 128), 128, ' DISABLED ', ' ENABLED '),
DECODE (Bitand (KSUSEPFL, 512),
The ' 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 is definitely bitand (KSUSEFLG, 1)!=0 Records

Test by locking table
Cdb_pdb@sys represents the Sys user, CDB_PDB@CHF represents the CHF user, uses two sessions, different user tests
Cdb_pdb@sys> Show user;
USER is "SYS"

--sys User Lock table
cdb_pdb@sys> Lock Table tab$ in exclusive MODE;

The table is locked.

Cdb_pdb@chf> Show user;
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

--CHF User CREATE Table
Cdb_pdb@chf> CREATE TABLE T_xifenfei_new as SELECT * from dual;

--sys User Inquiries
Cdb_pdb@sys> SELECT S.addr,
2 S.indx SID,
3 S.ksuseser serial#,
4 Ksuudsna Username,
5 DECODE (Bitand (KSUSEFLG, 19),
6, ' BACKGROUND ',
7 1, ' USER ',
8 2, ' recursive ',
9 '? ')
Ten TYPE
One from X$ksuse s
WHERE Ksusepro = ' 000007ff1e10f228 ';

ADDR SID serial# USERNAME TYPE
---------------- ---------- ---------- ------------------------------ ----------
000007FF1E1EBEA0 CHF USER
000007ff1e1d7f90 183 SYS Recursive

Cdb_pdb@sys> SELECT Ksuudsna Username,
2 KSUSEFLG
3 from X$ksuse s
4 WHERE ksusepro = ' 000007ff1e10f228 ';

USERNAME KSUSEFLG
------------------------------ ----------
CHF 135266369
SYS 2

--Here we find that the SQL called by the recursive Sys is excluded in the v$session view, so recursive SQL session cannot be displayed in V$session
Cdb_pdb@sys> Select Bitand (2,1) from dual;

Bitand (2,1)
-----------
0

At this point, we can verify that our current session, which has a recurrence of SYS in the process of creating a table, performs operations on the base table, but because the v$session view filters some of the records in the X$ksuse table, we cannot v$ Session check see these recursive sessions

Continue profiling Bitand Functions
By looking at V$session's creation statements, we can find the following rule, if a session is recursive session, then Bitand (KSUSEFLG, 19) = 2, when this value is 2, is not Bitand (KSUSEFLG, 1) Must be 0? The Bitand function actually converts the two parameters inside into binary and then the and operation, where two corresponding bits are 1, the result is 1 (bitand (3,1) =1,bitand (2,1) =0), where 19 is converted to binary 10011. To make Bitand (KSUSEFLG, 19) =2 set up, that is to say KSUSEFLG converted to binary, the last one must be 0, and Bitand (KSUSEFLG, 1) In such cases, must be 0, so the recursive session must not be in the v$ The session view is displayed.