遇到网友咨询，类似故障重现:Liunx平台ORACLE数据库运行正常，ORACLE_SID正确，但是本地登录提示idle instance，tns方式可以正常登录现象

[oracle@xifenfei ~]$ ps -ef|grep pmon
oracle   26295     1  0 04:11 ?        00:00:01 ora_pmon_XFF
oracle   27997 27966  0 05:48 pts/0    00:00:00 grep pmon
[oracle@xifenfei ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 05:48:30 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL>
[oracle@xifenfei ~]$ sqlplus sys/xifenfei@XFF as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 05:54:49 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

对于这样的现象，通过试验重现，并且通过oradebug ipc进行说明。
补充知识点:oracle本地认证是通过ipc进行的，而ipc是直接访问共享内存段的

系统当前状态
系统未启动然后数据库情况

[oracle@xifenfei ~]$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
[oracle@xifenfei ~]$ ps -ef|grep pmon|grep -v grep
[oracle@xifenfei ~]$

启动数据库
确定环境变量ORACLE_SID,ORACLE_HOME

[oracle@xifenfei ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 04:10:22 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  260046848 bytes
Fixed Size                  1266896 bytes
Variable Size              83888944 bytes
Database Buffers          167772160 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
--做debug ipc
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/oracle/admin/XFF/udump/xff_ora_26852.trc
*** SESSION ID:(152.25) 2013-04-20 04:43:00.983
Dump of unix-generic skgm context
areaflags            000000e7
realmflags           0000000f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00200000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdb87e6c
stackdir                   -1
mode                      640
magic                acc01ade
Handle:             0xe781de0 `/u01/oracle/oracle/product/10.2.0/db_1XFF' --->注意($ORACLE_HOME$ORACLE_SID)
Dump of unix-generic realm handle `/u01/oracle/oracle/product/10.2.0/db_1XFF', flags = 00000000
 Area #0 `Fixed Size' containing Subareas 0-0
  Total size 00000000001354d0 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr   --->主要Shmid
      0        0  3080192 0x00000020000000 0x00000020000000
                              Subarea size     Segment size
                          0000000000136000 000000000fa00000
 Area #1 `Variable Size' containing Subareas 2-2
  Total size 000000000f000000 Minimum Subarea size 00400000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      1        2  3080192 0x00000020800000 0x00000020800000
                              Subarea size     Segment size
                          000000000f000000 000000000fa00000
 Area #2 `Redo Buffers' containing Subareas 1-1
  Total size 00000000006ca000 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
   Area  Subarea    Shmid      Stable Addr      Actual Addr
[oracle@xifenfei ~]$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x6fd58208 3080192    oracle    640        262144000  17

这里证明ipc的处理是通过$ORACLE_HOME$ORACLE_SID进行的,修改ORACLE_HOME，进一步验证

修改ORACLE_HOME

[oracle@xifenfei ~]$ export ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1/
[oracle@xifenfei ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1/
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 04:11:46 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  260046848 bytes
Fixed Size                  1266896 bytes
Variable Size              83888944 bytes
Database Buffers          167772160 bytes
Redo Buffers                7118848 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
[oracle@xifenfei ~]$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x6fd58208 3080192    oracle    640        262144000  18
0x079d1b38 3112964    oracle    640        262144000  13
--启动两个同样的sid实例
[oracle@xifenfei ~]$ ps -ef|grep pmon|grep -v grep
oracle   26211     1  0 04:10 ?        00:00:00 ora_pmon_XFF
oracle   26295     1  0 04:11 ?        00:00:00 ora_pmon_XFF
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/oracle/admin/XFF/udump/xff_ora_27708.trc
*** SESSION ID:(159.5) 2013-04-20 05:32:00.969
Dump of unix-generic skgm context
areaflags            000000e7
realmflags           0000000f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00200000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdb5979c
stackdir                   -1
mode                      640
magic                acc01ade
Handle:             0xd99ede0 `/u01/oracle/oracle/product/10.2.0/db_1/XFF'--->注意
Dump of unix-generic realm handle `/u01/oracle/oracle/product/10.2.0/db_1/XFF', flags = 00000000

进一步证明在linux/unix系统，oracle数据库的内存段是通过ORACLE_HOME和ORACLE_SID结合起来识别的,只要ORACLE_HOME或者ORACLE_SID不一样就不能通过IPC访问实例的内存段,也就不能登录数据库

对数据库有一定了解的人都知道，数据库启动是通过bootstarp$表来实现启动引导的。那这里有两个疑问：
1.引导表bootstarp$名字是否是唯一？
2.引导表的位置是不是在数据文件固定？比如11g/12c在file 1 block 520，10g在file 1 block 377？

跟踪数据库启动

[oracle@rac2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 15 14:16:58 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
SQL> startup mount;
ORACLE instance started.
Total System Global Area  400846848 bytes
Fixed Size                  2288872 bytes
Variable Size             310379288 bytes
Database Buffers           79691776 bytes
Redo Buffers                8486912 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> alter session set db_file_multiblocK_read_count=1;
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Session altered.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_31364.trc
SQL> alter database Open;
Database altered.
SQL> oradebug EVENT 10046 trace name context off
Statement processed.
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='BOOTSTRAP$';
HEADER_FILE HEADER_BLOCK
----------- ------------
          1          520
SQL> select object_id from dba_objects where object_name='BOOTSTRAP$';
 OBJECT_ID
----------
        59

分析trace文件

WAIT #140077386411120: nam='db file sequential read' ela= 56 file#=1 block#=520 blocks=1 obj#=-1 tim=1719385755334
=====================
PARSING IN CURSOR #140077386402760 len=188 dep=1 uid=0 oct=1 lid=0 tim=1719385757322 hv=4006182593 ad='6645d370' sqlid='32r4f1brckzq1'
create table bootstrap$ (
END OF STMT
PARSE #140077386402760:c=2000,e=1711,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1719385757319
EXEC #140077386402760:c=0,e=430,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1719385757909
CLOSE #140077386402760:c=0,e=9,dep=1,type=0,tim=1719385758105
=====================
PARSING IN CURSOR #140077386402760 len=55 dep=1 uid=0 oct=3 lid=0 tim=1719385759507 hv=2111436465 ad='6645bc80' sqlid='6apq2rjyxmxpj'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #140077386402760:c=1000,e=1365,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1719385759505
BINDS #140077386402760:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f664edb8780  bln=22  avl=02  flg=05
  value=59
EXEC #140077386402760:c=3000,e=8859,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=1719385768574
WAIT #140077386402760: nam='db file sequential read' ela= 27 file#=1 block#=520 blocks=1 obj#=59 tim=1719385768753
WAIT #140077386402760: nam='db file sequential read' ela= 23 file#=1 block#=521 blocks=1 obj#=59 tim=1719385769575

这里我们可以发现，数据库启动的时候是读file 1 block 520，object_id为59，为bootstarp$对象

bbed查看root rdba地址

ub4 kcvfhrdb                             @96       0x00400208
SQL> select to_number('208','xxx') from dual;
TO_NUMBER('208','XXX')
----------------------
                   520

这里可以看出来，数据库启动的file header rdba地址和trace文件中的一致，也是指定到bootstarp$;

替换bootstrap$表为xifenfei

SQL> create table xifenfei as select * from bootstrap$;
Table created.
SQL> desc xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LINE#                                     NOT NULL NUMBER
 OBJ#                                      NOT NULL NUMBER
 SQL_TEXT                                  NOT NULL VARCHAR2(4000)
SQL> select sql_text from xifenfei where line#=59;
SQL_TEXT
--------------------------------------------------------------------------------
CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT
" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE
 (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJN
O 59 EXTENTS (FILE 1 BLOCK 520))
--清除bootstarp$记录
SQL> delete from xifenfei where line#=59;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select objECT_ID from dba_objects where object_name='XIFENFEI';
 OBJECT_ID
----------
     20314
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='XIFENFEI';
HEADER_FILE HEADER_BLOCK
----------- ------------
          1        45712
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area  400846848 bytes
Fixed Size                  2288872 bytes
Variable Size             310379288 bytes
Database Buffers           79691776 bytes
Redo Buffers                8486912 bytes
Database mounted.
Database opened.
--删除bootstarp$表
SQL>  drop table bootstrap$;
Table dropped.
SQL>  exec DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP('XIFENFEI');
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 15 14:29:54 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area  400846848 bytes
Fixed Size                  2288872 bytes
Variable Size             310379288 bytes
Database Buffers           79691776 bytes
Redo Buffers                8486912 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> alter session set db_file_multiblocK_read_count=1;
Session altered.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_32727.trc
SQL> alter database Open;
Database altered.
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='BOOTSTRAP$';
no rows selected
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='XIFENFEI';
 OBJECT_ID
----------
     20314

分析trace文件

WAIT #139841534602352: nam='db file sequential read' ela= 23 file#=1 block#=45712 blocks=1 obj#=-1 tim=1720082484775
=====================
PARSING IN CURSOR #139841534593992 len=193 dep=1 uid=0 oct=1 lid=0 tim=1720082488552 hv=2096904950 ad='6645d650' sqlid='bs6v55xygsfrq'
create table bootstrap$ (
END OF STMT
PARSE #139841534593992:c=2000,e=2925,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1720082488550
EXEC #139841534593992:c=0,e=466,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1720082489124
CLOSE #139841534593992:c=0,e=8,dep=1,type=0,tim=1720082489266
=====================
PARSING IN CURSOR #139841534593992 len=55 dep=1 uid=0 oct=3 lid=0 tim=1720082490510 hv=2111436465 ad='6645c050' sqlid='6apq2rjyxmxpj'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #139841534593992:c=1999,e=1211,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1720082490509
BINDS #139841534593992:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f2f64fe8780  bln=22  avl=04  flg=05
  value=20314
EXEC #139841534593992:c=1000,e=1789,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=1720082492533
WAIT #139841534593992: nam='db file sequential read' ela= 20 file#=1 block#=45712 blocks=1 obj#=20314 tim=1720082492685
WAIT #139841534593992: nam='db file sequential read' ela= 25 file#=1 block#=45713 blocks=1 obj#=20314 tim=1720082492986

这里可以看出来，数据库在启动的时候是读取file 1 block 45712，object_id为20314

bbed查看root rdba地址

ub4 kcvfhrdb                             @96       0x0040b290
SQL> select to_number('b290','xxxxxxx') from dual;
TO_NUMBER('B290','XXXXXXX')
---------------------------
                      45712

证明现在的文件头里面的file header rdba 已经修改为file 1 block 45712和trace里面看到的一致,都是XIFENFEI这个表

测试结论
1. 通过使用SWAP_BOOTSTRAP可以置换掉数据库启动开始表bootstrap$变为另外一个表,而且该过程直接修改文件头的kcvfhrdb值
2. 通过试验证明,oracle启动的时候不是程序里面写死的去读file 1的某个block,而是通过读取kcvfhrdb然后启动数据库

我们在数据库的使用过程中,有时候会遇到类似情况,我会话是登录的,但是我进行某种操作,缺报session不足.这种情况证明该sql后台还产生了其他会话,这里通过试验分析证明了递归session的存在
会话创建表报session超

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

这里有个问题:当前会话已经登录成功了，证明当前session是足够的，但是为什么在执行创建表操作之时依然会报ORA-00018呢？通过10046继续分析

CDB_PDB@CHF> alter session set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
会话已更改。
CDB_PDB@CHF> create table t_xifenfei as select * from dual;
表已创建。
CDB_PDB@CHF> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_ora_6596.trc

分析trace文件

CDB_PDB@CHF> host tkprof  E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_ora_6596.trc d:/1.txt
--查看trace文件，发现里面有很多基表操作，拿其中的一个tab$表分析，创建表过程有如下insert操作
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)

尝试人工插入

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
                *
第 1 行出现错误:
ORA-01031: 权限不足

证明当前执行创建表的session无权限直接操作tab$表，证明应该有其他表操作它

v$session视图基表
通过查询V$FIXED_VIEW_DEFINITION视图获得相关sql语句,不同版本可能有出入,但是大体一致

/* 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 ('00'), NULL, ksusetrn),
       DECODE (ksqpswat, HEXTORAW ('00'), 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),
               17, 'BACKGROUND',
               1, 'USER',
               2, 'RECURSIVE',
               '?'),
       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')),
       DECODE (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

注意:v$session查询的肯定是BITAND (ksuseflg, 1)!=0的记录

通过锁住表测试
CDB_PDB@SYS表示sys用户,CDB_PDB@CHF表示chf用户,使用两个session,不同用户测试

CDB_PDB@SYS> show user;
USER 为 "SYS"
--SYS用户锁住表
CDB_PDB@SYS> lock table tab$ IN exclusive MODE;
表已锁定。
CDB_PDB@CHF> show user;
USER 为 "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用户创建表
CDB_PDB@CHF>  create table t_xifenfei_new as select * from dual;
--SYS用户查询
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, 'RECURSIVE',
  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                             135266369
SYS                                     2
--这里我们发现递归sys调用的sql,在v$session视图中被排除了,因此递归sql的session不能在v$session显示
CDB_PDB@SYS> select bitand(2,1) from dual;
BITAND(2,1)
-----------
          0

至此，我们可以验证，我们当前的会话，在创建表的过程中有一个sys的递归session执行了关于基表的操作,但是由于v$session视图对于x$ksuse表中的部分记录进行了过滤因此我们不能在v$session查看到这些递归session

继续分析bitand函数
通过观察v$session的创建语句，我们可以发现如下规律,如果某个session是递归session,那么BITAND (ksuseflg, 19)=2,那当这个值为2的时候，是不是BITAND (ksuseflg, 1)一定为0呢？bitand函数实际上就是把里面的两个参数转换为二进制然后进行and运算,也就是两个对应位都为1的情况才会结果得带1(bitand(3,1)=1,bitand(2,1)=0),这里可以发现19转换为二进制为10011,要使得BITAND (ksuseflg, 19)=2成立,那就是说ksuseflg转换为二进制后,最后一位必须是0;而BITAND (ksuseflg, 1)在这样的情况下,一定为0,因此递归session的一定不会在v$session视图显示.