@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display database sessions
*  Parameters : 1 - Instance number (Use % for all instances)
*               2 - Where clause to filter the data.
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Aug-04  Vishal Gupta  First Draft
*  21-May-12  Vishal Gupta  Formatted out and added input aparameter.
*/



/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE INST_ID
UNDEFINE WHERE_CLAUSE


DEFINE INST_ID="&&1"
DEFINE WHERE_CLAUSE="&2"


COLUMN  _INST_ID          NEW_VALUE  INST_ID            NOPRINT

set term off
SELECT DECODE('&&INST_ID','','%','&&INST_ID')             "_INST_ID"
FROM DUAL
;
set term on



/************************************
*  CONFIGURATION PARAMETERS
************************************/
-- To hide sql, set show_sql="--"
--DEFINE SHOW_SQL="--"
DEFINE SHOW_SQL=""
DEFINE SHOW_BACKGROUND="--"


PROMPT **************************************************************
PROMPT * D A T A B A S E   S E S S I O N S               
PROMPT *                                                   
PROMPT * Input Parameters 
PROMPT *    - Instance#     = "&&INST_ID"
PROMPT *    - Where Clause  = "&&WHERE_CLAUSE"
PROMPT **************************************************************

COLUMN sid               HEADING "SID"                 FORMAT 99999 
COLUMN inst_id           HEADING "I#"                  FORMAT 99 
COLUMN spid              HEADING "SPID"                FORMAT a6
COLUMN status            HEADING "Status"              FORMAT a8
COLUMN state             HEADING "State"               FORMAT a12 TRUNCATED
COLUMN logon_time        HEADING "Logon Time"          FORMAT a18
COLUMN username          HEADING "UserName"            FORMAT a20
COLUMN osuser            HEADING "OS User"             FORMAT a15 TRUNCATED
COLUMN service_name      HEADING "ServiceName"         FORMAT a20 TRUNCATED
COLUMN MACHINE           HEADING "Machine"             FORMAT a20 TRUNCATED
COLUMN process           HEADING "Process"             FORMAT a11 
COLUMN program           HEADING "Program"             FORMAT a18 TRUNCATED
COLUMN sql_exec_duration HEADING "SQL|Exec|Duration"   FORMAT a11 
COLUMN event             HEADING "Event"               FORMAT a30 TRUNCATED
COLUMN last_call_et      HEADING "LastCall"            FORMAT a12  JUSTIFY RIGHT
COLUMN sql_child_number  HEADING "SQL|Child|No"        FORMAT 99

SELECT s.sid
     , s.inst_id
     , p.spid
     , s.status
     --, s.state
     --, s.last_call_et
     , LPAD(DECODE(FLOOR(last_call_et / 3600),0, '', FLOOR(last_call_et / 3600) || 'h ' )
           || LPAD(FLOOR(MOD(last_call_et , 3600 ) / 60),2) || 'm ' 
           || LPAD(MOD(last_call_et, 60 ) ,2) || 's' 
           , 12) last_call_et
     , to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') logon_time
     , s.username
     , s.osuser
     , s.service_name
     , s.machine
     , s.process
     , s.program
     , LPAD(REPLACE(REPLACE(LPAD(FLOOR((sysdate-sql_exec_start)*24),2) || 'h ' 
             || LPAD(FLOOR(MOD((sysdate-sql_exec_start)*24,1)*60),2) || 'm ' 
             || LPAD(FLOOR(MOD((sysdate-sql_exec_start)*24*60,1)*60),2) || 's' 
            ,' 0h  0m ',''),' 0h ',''),11)
       sql_exec_duration 
     , s.sql_id
     , s.sql_child_number
     , w.event
  FROM gv$session s
     , gv$session_wait   w
     , gv$process p
 WHERE s.sid   = w.sid  (+)
   AND s.inst_id = w.inst_id (+)
   AND s.inst_id = p.inst_id (+)
   AND s.paddr = p.addr (+)
&&SHOW_BACKGROUND   AND s.type <> 'BACKGROUND'
   AND s.inst_id like '&&INST_ID'
   &&WHERE_CLAUSE
ORDER BY s.last_call_et desc   
;



@@footer