找到系统视图定义的几种操作

今天药忘吃喽~ 2022-10-15 10:57 50阅读 0赞

一个微信群上有个朋友问“有什么形式能将v$session的代码导出来”？还真没想过这个问题。

导出来的用途，我们暂且放到一边儿，但就这个问题，有什么解决方案？

根据群中朋友、老师的相互探讨给出了几种方案。

我们知道，v$session是Oracle的动态性能视图，但其实他，不是真正的视图，可以参考《[v$和v\_$的一些玄机][v_v]》，v$session是个同义词，

SQL> select object_name, object_type from dba_objects where object_name in ('V$SESSION', 'V_$SESSION');
    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ -----------------------
    V_$SESSION                     VIEW
    V$SESSION                      SYNONYM

11g中能通过get\_ddl得到v\_$session的定义，但只是知道他是从真正的视图v$session创建而来的，

![364e7dbf382810e5efbdb8f5bbb5367d.png][]

而在19c中直接通过get\_ddl不能得到定义，

SQL> select dbms_metadata.get_ddl('VIEW', 'V_$SESSION', 'SYS') from dual;
    ERROR:
    ORA-31603: object "V_$SESSION" of type VIEW not found in schema "SYS"
    ORA-06512: at "SYS.DBMS_METADATA", line 6731
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
    ORA-06512: at "SYS.DBMS_METADATA", line 6718
    ORA-06512: at "SYS.DBMS_METADATA", line 9734
    ORA-06512: at line 1
    no rows selected

另外一个视图，v$fixed\_view\_definition可以给出视图的定义，但是因为view\_definition只存储4000个字节，因此有可能不会显示完整，

SQL> desc v$fixed_view_definition
    
    
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     VIEW_NAME                                          VARCHAR2(128)
     VIEW_DEFINITION                                    VARCHAR2(4000)
     CON_ID                                             NUMBER

明显没显示完整，

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SESSION';
    
    
    VIEW_DEFINITION
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetrn),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,9),1,'ACTIVE',0,
    decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO',4,'POOLED','NONE'),  s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid, s.ksusemnm,s.ksusemnp,s.ksusetid,s.ksusepnm, decode(bitand(s.ksuse
    flg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_number(null), s.ksusesch),  s.ksusesesta,  decode(s.ksuseseid, 0, to_number(null), s.ksuseseid),    s.ksusepsq, s.ksusepha, s.ksusepsi
    ,  decode(s.ksusepch, 65535, to_number(null), s.ksusepch),  s.ksusepesta,  decode(s.ksusepeid, 0, to_number(null), s.ksusepeid),  decode(s.ksusepeo,0,to_number(null),s.ksusepeo),  decode(s.ksusepeo,0,to_number(null),s.ksusepes),  decode(s.ksusepco,0,
    to_number(null),         decode(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepco,                to_number(null))),  decode(s.ksusepcs,0,to_number(null),           decode(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepcs,
      to_number(null))),  s.ksuseapp, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt,  s.ksuseorafn, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxopt, 12),0,'NO','YES'),decode(s.ksuseft, 2,'SES
    SION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),de
    code(bitand(s.ksusepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),  s.ksusecqd, s.ksuseclid,  decode(s.ksuseblocker,429496
    7295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null
    ),4294967291,  to_number(null),bitand(s.ksuseblocker, 2147221504)/262144),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuse
    blocker, 262143)),  decode(s.ksusefblocker,4294967295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID'),decode(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294
    967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksusefblocker, 2147221504)/262144),decode(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number
    (null),4294967291,  to_number(null),bitand(s.ksusefblocker, 262143)),  w.kslwtseq,w.kslwtevt,e.kslednam,e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass, decode(w
    .kslwtinwait,        0,decode(bitand(w.kslwtflags,256),                 0,-2,                 decode(round(w.kslwtstime/10000),                  0,-1,                        round(w.kslwtstime/10000))),        0), decode(w.kslwtinwait,0,round((
    w.kslwtstime+w.kslwtltime)/1000000),  round(w.kslwtstime/1000000)), decode(w.kslwtinwait,1,'WAITING',  decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME',   decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME',    'WAITED KNOWN TIME'))),w.kslw
    tstime, decode(w.kslwtinwait,0,to_number(null),  decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), w.kslwtltime,s.ksusesvc, decode(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuse

另外一种思路，就是从10053得到定义，如下所示，可以知道，v$session来自于"SYS"."X$KSUSE" "S","SYS"."X$KSLED" "E","SYS"."X$KSLWT" "W"这三个X$基表，

SQL:******* UNPARSED QUERY IS *******
    SELECT "S"."INST_ID" "INST_ID","S"."ADDR" "SADDR","S"."INDX" "SID",
    "S"."KSUSESER" "SERIAL#","S"."KSUUDSES" "AUDSID",
    "S"."KSUSEPRO" "PADDR","S"."KSUUDLUI" "USER#",
    "S"."KSUUDLNA" "USERNAME","S"."KSUUDOCT" "COMMAND",
    "S"."KSUSESOW" "OWNERID",DECODE("S"."KSUSETRN",HEXTORAW('00'),NULL,"S"."KSUSETRN") "TADDR",
    DECODE("S"."KSQPSWAT",HEXTORAW('00'),NULL,"S"."KSQPSWAT") "LOCKWAIT",
    DECODE(BITAND("S"."KSUSEIDL",9),1,'ACTIVE',0,DECODE(BITAND("S"."KSUSEFLG",4096),0,'INACTIVE','CACHED'),'KILLED') "STATUS",
    DECODE("S"."KSSPATYP",1,'DEDICATED',2,'SHARED',3,'PSEUDO',4,'POOLED','NONE') "SERVER","S"."KSUUDSID" "SCHEMA#",
    "S"."KSUUDSNA" "SCHEMANAME","S"."KSUSEUNM" "OSUSER","S"."KSUSEPID" "PROCESS","S"."KSUSEMNM" "MACHINE",
    "S"."KSUSEMNP" "PORT","S"."KSUSETID" "TERMINAL","S"."KSUSEPNM" "PROGRAM",
    DECODE(BITAND("S"."KSUSEFLG",19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?') "TYPE",
    "S"."KSUSESQL" "SQL_ADDRESS","S"."KSUSESQH" "SQL_HASH_VALUE","S"."KSUSESQI" "SQL_ID",
    DECODE("S"."KSUSESCH",65535,TO_NUMBER(NULL),"S"."KSUSESCH") "SQL_CHILD_NUMBER",
    "S"."KSUSESESTA" "SQL_EXEC_START",DECODE("S"."KSUSESEID",0,TO_NUMBER(NULL),"S"."KSUSESEID") "SQL_EXEC_ID",
    "S"."KSUSEPSQ" "PREV_SQL_ADDR","S"."KSUSEPHA" "PREV_HASH_VALUE","S"."KSUSEPSI" "PREV_SQL_ID",
    DECODE("S"."KSUSEPCH",65535,TO_NUMBER(NULL),"S"."KSUSEPCH") "PREV_CHILD_NUMBER",
    "S"."KSUSEPESTA" "PREV_EXEC_START",DECODE("S"."KSUSEPEID",0,TO_NUMBER(NULL),"S"."KSUSEPEID") "PREV_EXEC_ID",
    DECODE("S"."KSUSEPEO",0,TO_NUMBER(NULL),"S"."KSUSEPEO") "PLSQL_ENTRY_OBJECT_ID",
    DECODE("S"."KSUSEPEO",0,TO_NUMBER(NULL),"S"."KSUSEPES") "PLSQL_ENTRY_SUBPROGRAM_ID",
    DECODE("S"."KSUSEPCO",0,TO_NUMBER(NULL),DECODE(BITAND("S"."KSUSSTMBV",POWER(2,11)),POWER(2,11),
    "S"."KSUSEPCO",TO_NUMBER(NULL))) "PLSQL_OBJECT_ID",DECODE("S"."KSUSEPCS",0,TO_NUMBER(NULL),
    DECODE(BITAND("S"."KSUSSTMBV",POWER(2,11)),POWER(2,11),"S"."KSUSEPCS",TO_NUMBER(NULL))) "PLSQL_SUBPROGRAM_ID",
    "S"."KSUSEAPP" "MODULE","S"."KSUSEAPH" "MODULE_HASH","S"."KSUSEACT" "ACTION","S"."KSUSEACH" "ACTION_HASH",
    "S"."KSUSECLI" "CLIENT_INFO","S"."KSUSEFIX" "FIXED_TABLE_SEQUENCE",
    "S"."KSUSEOBJ" "ROW_WAIT_OBJ#","S"."KSUSEFIL" "ROW_WAIT_FILE#",
    "S"."KSUSEBLK" "ROW_WAIT_BLOCK#","S"."KSUSESLT" "ROW_WAIT_ROW#",
    "S"."KSUSEORAFN" "TOP_LEVEL_CALL#","S"."KSUSELTM" "LOGON_TIME",
    "S"."KSUSECTM" "LAST_CALL_ET",DECODE(BITAND("S"."KSUSEPXOPT",12),0,'NO','YES') "PDML_ENABLED",
    DECODE("S"."KSUSEFT",2,'SESSION',4,'SELECT',8,'TRANSACTIONAL','NONE') "FAILOVER_TYPE",
    DECODE("S"."KSUSEFM",1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE') "FAILOVER_METHOD",
    DECODE("S"."KSUSEFS",1,'YES','NO') "FAILED_OVER","S"."KSUSEGRP" "RESOURCE_CONSUMER_GROUP",
    DECODE(BITAND("S"."KSUSEPXOPT",4),4,'ENABLED',DECODE(BITAND("S"."KSUSEPXOPT",8),8,'FORCED','DISABLED')) "PDML_STATUS",
    DECODE(BITAND("S"."KSUSEPXOPT",2),2,'FORCED',DECODE(BITAND("S"."KSUSEPXOPT",1),1,'DISABLED','ENABLED')) "PDDL_STATUS",
    DECODE(BITAND("S"."KSUSEPXOPT",32),32,'FORCED',DECODE(BITAND("S"."KSUSEPXOPT",16),16,'DISABLED','ENABLED')) "PQ_STATUS",
    "S"."KSUSECQD" "CURRENT_QUEUE_DURATION","S"."KSUSECLID" "CLIENT_IDENTIFIER",DECODE("S"."KSUSEBLOCKER",4294967295,'UNKNOWN',4294967294,'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',4294967291,'NOT IN WAIT','VALID') "BLOCKING_SESSION_STATUS",
    DECODE("S"."KSUSEBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL),4294967292,TO_NUMBER(NULL),4294967291,TO_NUMBER(NULL),BITAND("S"."KSUSEBLOCKER",2147221504)/262144) "BLOCKING_INSTANCE",
    DECODE("S"."KSUSEBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL),4294967292,TO_NUMBER(NULL),4294967291,TO_NUMBER(NULL),BITAND("S"."KSUSEBLOCKER",262143)) "BLOCKING_SESSION",
    DECODE("S"."KSUSEFBLOCKER",4294967295,'UNKNOWN',4294967294,'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',4294967291,'NOT IN WAIT','VALID') "FINAL_BLOCKING_SESSION_STATUS",DECODE("S"."KSUSEFBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL),4294967292,TO_NUMBER(NULL),4294967291,TO_NUMBER(NULL),BITAND("S"."KSUSEFBLOCKER",2147221504)/262144) "FINAL_BLOCKING_INSTANCE",
    DECODE("S"."KSUSEFBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL),4294967292,TO_NUMBER(NULL),4294967291,TO_NUMBER(NULL),BITAND("S"."KSUSEFBLOCKER",262143)) "FINAL_BLOCKING_SESSION","W"."KSLWTSEQ" "SEQ#","W"."KSLWTEVT" "EVENT#","E"."KSLEDNAM" "EVENT","E"."KSLEDP1" "P1TEXT","W"."KSLWTP1" "P1","W"."KSLWTP1R" "P1RAW","E"."KSLEDP2" "P2TEXT","W"."KSLWTP2" "P2","W"."KSLWTP2R" "P2RAW","E"."KSLEDP3" "P3TEXT","W"."KSLWTP3" "P3","W"."KSLWTP3R" "P3RAW","E"."KSLEDCLASSID" "WAIT_CLASS_ID","E"."KSLEDCLASS#" "WAIT_CLASS#","E"."KSLEDCLASS" "WAIT_CLASS",
    DECODE("W"."KSLWTINWAIT",0,DECODE(BITAND("W"."KSLWTFLAGS",256),0,(-2),DECODE(ROUND("W"."KSLWTSTIME"/10000),0,(-1),ROUND("W"."KSLWTSTIME"/10000))),0) "WAIT_TIME",DECODE("W"."KSLWTINWAIT",0,ROUND(("W"."KSLWTSTIME"+"W"."KSLWTLTIME")/1000000),ROUND("W"."KSLWTSTIME"/1000000)) "SECONDS_IN_WAIT",DECODE("W"."KSLWTINWAIT",1,'WAITING',DECODE(BITAND("W"."KSLWTFLAGS",256),0,'WAITED UNKNOWN TIME',
    DECODE(ROUND("W"."KSLWTSTIME"/10000),0,'WAITED SHORT TIME','WAITED KNOWN TIME'))) "STATE","W"."KSLWTSTIME" "WAIT_TIME_MICRO",DECODE("W"."KSLWTINWAIT",0,TO_NUMBER(NULL),DECODE(BITAND("W"."KSLWTFLAGS",64),64,0,"W"."KSLWTTREM")) "TIME_REMAINING_MICRO","W"."KSLWTLTIME" "TIME_SINCE_LAST_WAIT_MICRO","S"."KSUSESVC" "SERVICE_NAME",DECODE(BITAND("S"."KSUSEFLG2",32),32,'ENABLED','DISABLED') "SQL_TRACE",
    DECODE(BITAND("S"."KSUSEFLG2",64),64,'TRUE','FALSE') "SQL_TRACE_WAITS",DECODE(BITAND("S"."KSUSEFLG2",128),128,'TRUE','FALSE') "SQL_TRACE_BINDS",DECODE(BITAND("S"."KSUSEFLG2",65536)+BITAND("S"."KSUSEFLG2",131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC') "SQL_TRACE_PLAN_STATS","S"."KSUUDSAE" "SESSION_EDITION_ID","S"."KSUSECRE" "CREATOR_ADDR","S"."KSUSECSN" "CREATOR_SERIAL#","S"."KSUSEECID" "ECID","S"."KSUUDTXLP" "SQL_TRANSLATION_PROFILE_ID",("S"."KSUSEPGAMAN"+"S"."KSUSEPGAAUTO")*1024 "PGA_TUNABLE_MEM",
    DECODE(BITAND("S"."KSUUDFLG",512),512,'ENABLED','DISABLED') "SHARD_DDL_STATUS","S"."CON_ID" "CON_ID","S"."KSUSEXNM" "EXTERNAL_NAME",DECODE(BITAND("S"."KSUSEFLG3",2048),2048,'TRUE','FALSE') "PLSQL_DEBUGGER_CONNECTED" FROM "SYS"."X$KSUSE" "S","SYS"."X$KSLED" "E","SYS"."X$KSLWT" "W" 
    WHERE BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND "S"."INDX"="W"."KSLWTSID" AND "W"."KSLWTEVT"="E"."INDX"

除此之外，老虎刘老师推荐了另外一种“骚”操作，通过检索oracle这个二进制文件，其中包含了v$session定义，

[oracle@bisal bin]$ strings oracle | grep kslwtstime
    select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, 
    decode(s.ksusetrn,hextoraw('00'),null,s.ksusetrn),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),
    decode(bitand(s.ksuseidl,9),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),'KILLED'),
    decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO',4,'POOLED','NONE'),  
    s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid, s.ksusemnm,s.ksusemnp,s.ksusetid,s.ksusepnm, 
    ecode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, 
    decode(s.ksusesch, 65535, to_number(null), s.ksusesch),  s.ksusesesta,  
    decode(s.ksuseseid, 0, to_number(null), s.ksuseseid),  s.ksusepsq, s.ksusepha, s.ksusepsi,  
    decode(s.ksusepch, 65535, to_number(null), s.ksusepch),  s.ksusepesta,  decode(s.ksusepeid, 0, to_number(null), s.ksusepeid),  
    decode(s.ksusepeo,0,to_number(null),s.ksusepeo),  decode(s.ksusepeo,0,to_number(null),s.ksusepes),  decode(s.ksusepco,0,to_number(null),         
    decode(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepco,                to_number(null))),  decode(s.ksusepcs,0,to_number(null),         
    decode(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepcs,                to_number(null))),  s.ksuseapp, s.ksuseaph, s.ksuseact, 
    s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt,  s.ksuseorafn, s.ksuseltm, s.ksusectm,
    decode(bitand(s.ksusepxopt, 12),0,'NO','YES'),decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),
    decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),
    s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),
    decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),
    decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),  s.ksusecqd, s.ksuseclid,  
    decode(s.ksuseblocker,4294967295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID'),
    decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker, 2147221504)/262144),
    decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker, 262143)),  
    decode(s.ksusefblocker,4294967295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID'),
    decode(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksusefblocker, 2147221504)/262144),
    decode(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksusefblocker, 262143)),  
    w.kslwtseq,w.kslwtevt,e.kslednam,e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,
    e.ksledclass#,e.ksledclass, decode(w.kslwtinwait,        0,decode(bitand(w.kslwtflags,256),                 0,-2,                 decode(round(w.kslwtstime/10000),                        0,-1,                        round(w.kslwtstime/10000))),        0), 
    decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000),  round(w.kslwtstime/1000000)), 
    decode(w.kslwtinwait,1,'WAITING',  decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME',   decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME',    'WAITED KNOWN TIME'))),
    w.kslwtstime, decode(w.kslwtinwait,0,to_number(null),  decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), w.kslwtltime,s.ksusesvc, 
    decode(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE'),
    decode(bitand(s.ksuseflg2,65536) + bitand(s.ksuseflg2,131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC'),s.ksuudsae,s.ksusecre,s.ksusecsn,
    s.ksuseecid,s.ksuudtxlp,(s.ksusepgaman+s.ksusepgaauto)*1024,decode(bitand(s.ksuudflg,512),512,'ENABLED','DISABLED'),s.con_id,s.ksusexnm,
    decode(bitand(s.ksuseflg3,2048),2048,'TRUE','FALSE') 
    from x$ksuse s, x$ksled e, x$kslwt w 
    where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.indx=w.kslwtsid and w.kslwtevt=e.indx

‍

这个问题其实没什么实际的作用，只是给出了系统视图定义获取的几种方式，真正的难点在于X$基表字段的作用，但往往这些都是Oracle的商业机密，在可公开的文档中很少会提及到。

**近期更新的文章：**

《[NULL判断对SQL的影响][NULL_SQL]》

《[什么是快乐星球？什么又是“标量子查询”呢？][Link 1]》

《[VMWare增加磁盘空间的操作][VMWare]》

《[曾经运维生涯中的几个“最”][Link 2]》

《[一条半连接SQL的优化过程][SQL]》

《[如何找到隐式转换的SQL？][SQL 1]》

《[为什么日期不建议使用VARCHAR2或者NUMBER？][VARCHAR2_NUMBER]》

《[查看时间戳类型的绑定变量][Link 3]》

《[写代码如何事半功倍？][Link 4]》

《[支持超过4000字节的varchar2类型][4000_varchar2]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[v_v]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c2ddf52cd4ed4a1a6de5d89f7a6638d8d9378c66739e63ebfd7f670002d76212eb88a2&idx=1&mid=2247491633&scene=21&sn=56f7465a50792828e5c679c69c2f6182#wechat_redirect
[364e7dbf382810e5efbdb8f5bbb5367d.png]: /images/20221014/cfb26d03e59446c38e3ccb82a820002d.png
[NULL_SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7c5ddf52ed323d754ab80899ff4fc065453dc23e16b96e367d2f06d6dff3205950028ef&idx=1&mid=2247492150&scene=21&sn=128e815af4e8a0d7986889fc571b7233#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7deddf52ec8a050d5d9acaf8f707289707b5530b7b1eb27b2e4577cea179b989b43d60a&idx=1&mid=2247492141&scene=21&sn=540622bdb1c79b8ce62f75ae70c827da#wechat_redirect
[VMWare]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7e8ddf52efe6fd6ca95fc4405d1bdeff10c528b571824fed58a57b11063c0960066d675&idx=1&mid=2247492123&scene=21&sn=518d5e367e0dbd5486f2d29745b41012#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7f2ddf52ee49775b374898978d2f13624f1e51dcbfecdfdd32925332132e4eaa88cc7bd&idx=1&mid=2247492097&scene=21&sn=f2c338461919594062136eb10a70e9c4#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a404ddf52d127b63d6a15e3d204db28426064a005e58ea83a6b89f30256eab76d464aad2&idx=1&mid=2247492087&scene=21&sn=743c7ae9ad0d77073f291ae5abc2ef29#wechat_redirect
[SQL 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a414ddf52d02b74d3e476d6c4daf1aa9ba6a5be958a4f19d9b880a0029b43895eebceb2f&idx=1&mid=2247492071&scene=21&sn=05655f3f6ec05fd54b94097552708259#wechat_redirect
[VARCHAR2_NUMBER]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a411ddf52d07968c523b8869089f79e531474503306a0561ad1be8ed36625ef8ad8d3b4a&idx=1&mid=2247492066&scene=21&sn=06e18747abd40914a505c0c62da889c0#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a421ddf52d377a89c56aaadf746d892ae18c21d70b31c9f150b2b0c81483be5c841c7f71&idx=1&mid=2247492050&scene=21&sn=2bcfb42048e4d1ddf4f7eafa5523b7f2#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a432ddf52d2475aa2c81573af45a075b055cc1d4171c4036b73bc8c9a0648f2ea2952904&idx=1&mid=2247492033&scene=21&sn=d471b5da9c7c18e065fe38b299737db6#wechat_redirect
[4000_varchar2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a449ddf52d5feea2ac1b64a23f9214b1f939e84e5360dc857deeb7dadff780ad488a1c9c&idx=1&mid=2247492026&scene=21&sn=f31d0e69ea77eb0b33ff32da933a3fe6#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect