72 lines
2.9 KiB
SQL
72 lines
2.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display OLAP Sessions
|
|
* Parameters : NONE
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 30-Jul-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
PROMPT **********************************
|
|
PROMPT * Analytic Workspace Sessions
|
|
PROMPT **********************************
|
|
|
|
COLUMN owner HEADING "" FORMAT a20
|
|
COLUMN aw_name HEADING "AW Name" FORMAT a27
|
|
COLUMN aw_version HEADING "AW|Ver" FORMAT a5
|
|
COLUMN attach_mode HEADING "Attach|Mode" FORMAT a6
|
|
COLUMN session_id HEADING "SID" FORMAT 99999
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN session_handle HEADING "Session|Handle" FORMAT 99999999
|
|
COLUMN session_state HEADING "Session|State" FORMAT a10
|
|
COLUMN userid HEADING "User" FORMAT a10
|
|
COLUMN total_transaction HEADING "Total|Tran" FORMAT 99999
|
|
COLUMN total_transaction_cpu_time HEADING "Total|Tran|CPUTime" FORMAT 999999999
|
|
COLUMN total_transaction_time HEADING "Total|Tran|Time" FORMAT 999999999
|
|
|
|
COLUMN logon_time HEADING "Logon Time" FORMAT a18
|
|
COLUMN username HEADING "UserName" FORMAT a20
|
|
COLUMN osuser HEADING "OS User" FORMAT a15 TRUNCATED
|
|
COLUMN MACHINE HEADING "Machine" FORMAT a20 TRUNCATED
|
|
COLUMN process HEADING "Process" FORMAT a11
|
|
COLUMN program HEADING "Program" FORMAT a18 TRUNCATED
|
|
COLUMN event HEADING "Event" FORMAT a30 TRUNCATED
|
|
COLUMN last_call_et HEADING "LastCall|(sec)" FORMAT 999,999
|
|
--COLUMN last_call_et HEADING "LastCall" FORMAT a12
|
|
COLUMN sql_child_number HEADING "SQL|Child|No" FORMAT 99
|
|
|
|
select a.owner || '.' || a.aw_name aw_name
|
|
, a.aw_version
|
|
, DECODE(o.attach_mode,'READ WRITE','RW','MULTIWRITE','MW','READ ONLY','RO',o.attach_mode) attach_mode
|
|
, o.session_id
|
|
, o.inst_id
|
|
, s.status
|
|
, s.last_call_et
|
|
, s.username
|
|
, s.osuser
|
|
, s.program
|
|
, s.machine
|
|
, s.sql_id
|
|
, s.sql_child_number
|
|
from gv$aw_olap o
|
|
, dba_aws a
|
|
, gv$aw_session_info aws
|
|
, gv$session s
|
|
WHERE a.aw_number = o.aw_number
|
|
AND o.inst_id = aws.inst_id
|
|
AND o.session_id = aws.session_id
|
|
AND aws.inst_id = s.inst_id
|
|
AND aws.session_id = s.sid
|
|
ORDER BY aw_name, o.session_id
|
|
;
|
|
|
|
|
|
@@footer
|