@@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