sys@racdb1> select * from v$session;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3733760267
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1116 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 1116 | 0 (0)| 00:00:01 |
|* 2 | FIXED TABLE FULL | X$KSUSE | 1 | 907 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 209 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
3 - filter("S"."KSUSEOPC"="E"."INDX")
sys@racdb1> select view_definition from v$fixed_view_definition where view_name='V$SESSION';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
select SADDR , SID , SERIAL# , AUDSID , PADDR , USER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKWAIT , STATUS , SERVE
R , SCHEMA# , SCHEMANAME ,OSUSER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID,
SQL_CHILD_NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_SQL_ID, PREV_CHILD_NUMBER , PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTR
Y_SUBPROGRAM_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID, MODULE , MODULE_HASH , ACTION , ACTION_HASH , CLIENT_INFO , FIXED
_TABLE_SEQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_ET , PDML_EN
ABLED , FAILOVER_TYPE , FAILOVER_METHOD , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS, CUR
RENT_QUEUE_DURATION, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT,P
1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,P3,P3RAW,WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, SECONDS_IN_WAIT,STATE,S
ERVICE_NAME, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where inst_id = USERENV('Instance')
sys@racdb1> select view_definition from v$fixed_view_definition where view_name='GV$SESSION';
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, 11), 1, 'ACTIVE', 0, decode(bitand(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 2, 'SNIPED', 3, 'SNIPED',
'KILLED'), decode(s.ksspatyp, 1, 'DEDICATED', 2, 'SHARED', 3, 'PSEUDO', 'NONE'), s.ksuudsid, s.ksuudsna, s.ksuseunm,
s.ksusepid, s.ksusemnm, s.ksusetid, s.ksusepnm, decode(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.ksusepsq, s.ksusepha, s.ksusepsi,
decode(s.ksusepch, 65535, to_number(NULL), s.ksusepch), 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), s.ksusepco),
decode(s.ksusepco, 0, to_number(NULL), s.ksusepcs), s.ksuseapp, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix,
s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt, 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, 2147418112) / 65536),
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, 65535)), s.ksuseseq, s.ksuseopc, e.kslednam, e.ksledp1,
s.ksusep1, s.ksusep1r, e.ksledp2, s.ksusep2, s.ksusep2r, e.ksledp3, s.ksusep3, s.ksusep3r, e.ksledclassid, e.ksledclass#,
e.ksledclass, decode(s.ksusetim, 0, 0, -1, -1, -2, -2, decode(round(s.ksusetim / 10000), 0, -1, round(s.ksusetim / 10000))),
s.ksusewtm,
decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, 'WAITED SHORT TIME',
decode(round(s.ksusetim / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME')), 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')
FROM x$ksuse s, x$ksled e
WHERE bitand(s.ksspaflg, 1) != 0 AND bitand(s.ksuseflg, 1) != 0 AND s.ksuseopc = e.indx
v$session--X$KSUSE s，X$KSLED e
字段对应表：
| v$session | x$ksuse | |||||||||
| s.inst_id | ||||||||||
| SADDR | s.addr | |||||||||
| SID | s.indx | |||||||||
| SERIAL# | s.ksuseser | |||||||||
| AUDSID | s.ksuudses | |||||||||
| PADDR | s.ksusepro | |||||||||
| USER# | s.ksuudlui | |||||||||
| USERNAME | s.ksuudlna | |||||||||
| COMMAND | s.ksuudoct | |||||||||
| OWNERID | s.ksusesow | |||||||||
| TADDR | decode(s.ksusetrn,hextoraw('00'),null,s.ksusetrn) | |||||||||
| LOCKWAIT | decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat) | |||||||||
| STATUS | decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED') | |||||||||
| SERVER | decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE') | |||||||||
| SCHEMA# | s.ksuudsid | |||||||||
| SCHEMANAME | s.ksuudsna | |||||||||
| OSUSER | s.ksuseunm | |||||||||
| PROCESS | s.ksusepid | |||||||||
| MACHINE | s.ksusemnm | |||||||||
| TERMINAL | s.ksusetid | |||||||||
| PROGRAM | s.ksusepnm | |||||||||
| TYPE | decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?') | |||||||||
| SQL_ADDRESS | s.ksusesql | |||||||||
| SQL_HASH_VALUE | s.ksusesqh | |||||||||
| SQL_ID | s.ksusesqi | |||||||||
| SQL_CHILD_NUMBER | decode(s.ksusesch, 65535, to_number(null), s.ksusesch) | |||||||||
| PREV_SQL_ADDR | s.ksusepsq | |||||||||
| PREV_HASH_VALUE | s.ksusepha | |||||||||
| PREV_SQL_ID | s.ksusepsi | |||||||||
| PREV_CHILD_NUMBER | decode(s.ksusepch, 65535, to_number(null), s.ksusepch) | |||||||||
| PLSQL_ENTRY_OBJECT_ID | decode(s.ksusepeo,0,to_number(null),s.ksusepeo) | |||||||||
| PLSQL_ENTRY_SUBPROGRAM_ID | decode(s.ksusepeo,0,to_number(null),s.ksusepes) | |||||||||
| PLSQL_OBJECT_ID | decode(s.ksusepco,0,to_number(null),s.ksusepco) | |||||||||
| PLSQL_SUBPROGRAM_ID | decode(s.ksusepco,0,to_number(null),s.ksusepcs) | |||||||||
| MODULE | s.ksuseapp | |||||||||
| MODULE_HASH | s.ksuseaph | |||||||||
| ACTION | s.ksuseact | |||||||||
| ACTION_HASH | s.ksuseach | |||||||||
| CLIENT_INFO | s.ksusecli | |||||||||
| FIXED_TABLE_SEQUENCE | s.ksusefix | |||||||||
| ROW_WAIT_OBJ# | s.ksuseobj | |||||||||
| ROW_WAIT_FILE# | s.ksusefil | |||||||||
| ROW_WAIT_BLOCK# | s.ksuseblk | |||||||||
| ROW_WAIT_ROW# | s.ksuseslt | |||||||||
| LOGON_TIME | s.ksuseltm | |||||||||
| LAST_CALL_ET | s.ksusectm | |||||||||
| PDML_ENABLED | decode(bitand(s.ksusepxopt, 12),0,'NO','YES') | |||||||||
| FAILOVER_TYPE | decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE') | |||||||||
| FAILOVER_METHOD | decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE') | |||||||||
| FAILED_OVER | decode(s.ksusefs, 1, 'YES', 'NO') | |||||||||
| RESOURCE_CONSUMER_GROUP | s.ksusegrp | |||||||||
| PDML_STATUS | decode(bitand(s.ksusepxopt,4),4,'ENABLED', decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')) | |||||||||
| PDDL_STATUS | decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')) | |||||||||
| PQ_STATUS | decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED')) | |||||||||
| CURRENT_QUEUE_DURATION | s.ksusecqd | |||||||||
| CLIENT_IDENTIFIER | s.ksuseclid | |||||||||
| BLOCKING_SESSION_STATUS | decode(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294 , 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER', 4294967291,'NOT IN WAIT','VALID') | |||||||||
| 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, 2147418112)/65536) | |||||||||
| BLOCKING_SESSION | 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,65535)) | |||||||||
| SEQ# | s.ksuseseq | |||||||||
| EVENT# | s.ksuseopc | |||||||||
| EVENT | e.kslednam | |||||||||
| P1TEXT | e.ksledp1 | |||||||||
| P1 | s.ksusep1 | |||||||||
| P1RAW | s.ksusep1r | |||||||||
| P2TEXT | e.ksledp2 | |||||||||
| P2 | s.ksusep2 | |||||||||
| P2RAW | s.ksusep2r | |||||||||
| P3TEXT | e.ksledp3 | |||||||||
| P3 | s.ksusep3 | |||||||||
| P3RAW | s.ksusep3r | |||||||||
| WAIT_CLASS_ID | e.ksledclassid | |||||||||
| WAIT_CLASS# | e.ksledclass# | |||||||||
| WAIT_CLASS | e.ksledclass | |||||||||
| WAIT_TIME | decode(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksusetim/10000),0,-1,round(s.ksusetim/10000))) | |||||||||
| SECONDS_IN_WAIT | s.ksusewtm | |||||||||
| STATE | decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, 'WAITED SHORT TIME', decode(round(s.ksusetim/10000),0,'WAITED SHORT TIME','WAITED KNOWN TIME') | |||||||||
| SERVICE_NAME | s.ksusesvc | |||||||||
| SQL_TRACE | decode(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED') | |||||||||
| SQL_TRACE_WAITS | decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE') | |||||||||
| SQL_TRACE_BINDS | decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE') | |||||||||
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16976507/viewspace-754440/ ，如需转载，请注明出处，否则将追究法律责任。
