Database Reference
In-Depth Information
Information on processes and sessions is available from the X$ tables underlying
V$PROCESS
and
V$SESSION
. These are
X$KSUSE
and
X$KSUPR
respectively. It requires some perseverance to
construct the largish view that meets the goals set above. Following is the DDL to create the
view, which I have called
X_$SESSION_WAIT
(script file name
x_session_wait.sql
):
CREATE OR REPLACE view x_$session_wait AS
SELECT s.inst_id AS inst_id,
s.indx AS sid,
se.ksuseser AS serial#,
-- spid from v$process
p.ksuprpid AS spid,
-- columns from v$session
se.ksuudlna AS username,
decode(bitand(se.ksuseidl,11),1,'ACTIVE',0,
decode(bitand(se.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,
'SNIPED', 'KILLED') AS status,
decode(ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE') AS server,
se.ksuseunm AS osuser,
se.ksusepid AS process,
se.ksusemnm AS machine,
se.ksusetid AS terminal,
se.ksusepnm AS program,
decode(bitand(se.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?') AS type,
se.ksusesqh AS sql_hash_value,
se.ksusepha AS prev_hash_value,
se.ksuseapp AS module,
se.ksuseact AS action,
se.ksuseclid AS client_identifier,
se.ksuseobj AS row_wait_obj#,
se.ksusefil AS row_wait_file#,
se.ksuseblk AS row_wait_block#,
se.ksuseslt AS row_wait_row#,
se.ksuseltm AS logon_time,
se.ksusegrp AS resource_consumer_group,
-- columns from v$session_wait
s.ksussseq AS seq#,
e.kslednam AS event,
e.ksledp1 AS p1text,
s.ksussp1 AS p1,
s.ksussp1r AS p1raw,
e.ksledp2 AS p2text,
s.ksussp2 AS p2,
s.ksussp2r AS p2raw,
e.ksledp3 AS p3text,
s.ksussp3 AS p3,
s.ksussp3r AS p3raw,
