SET LINES 200

COLUMN SID            HEADING SID                          FORMAT 99999
COLUMN USERNAME       HEADING USERNAME                     FORMAT a15 TRUNCATE
COLUMN OSUSER         HEADING OSUSER                       FORMAT a10 TRUNCATE
COLUMN PROGRAM        HEADING PROGRAM                      FORMAT a18 TRUNCATE
COLUMN MACHINE        HEADING MACHINE                      FORMAT A30 TRUNCATE
COLUMN SQL_ADDRESS    HEADING SQL_ADDRESS                  FORMAT a16
COLUMN EVENT          HEADING EVENT_NAME                   FORMAT a35 WORD_WRAP
COLUMN SR             HEADING SR                           FORMAT a2 TRUNCATE
COLUMN STATE          HEADING SESSION_STATE                FORMAT a7
COLUMN STATUS         HEADING SE                           FORMAT a2
COLUMN LAST_CALL      HEADING 'CALLET'                     FORMAT a6
COLUMN PLSQLOID       HEADING 'PLSQL|ID'                   FORMAT 999999
COLUMN WAIT_TIME      HEADING WAIT_TIME                    FORMAT a9 JUSTIFY RIGHT
COLUMN SQL_START      HEADING 'SQL_START'                  FORMAT a9
COLUMN SECS_IN_WAIT   HEADING 'SECS|WAIT'                  FORMAT 999999
COLUMN COMMAND        HEADING COMMAND FORMAT A7 TRUNCATE


SELECT s.inst_id,
       s.sid,
       s.username,
       s.osuser,
       s.machine,
       s.sql_id,
       s.sql_child_number,
       CASE
         WHEN w.state != 'WAITING' THEN 'WORKING'
         ELSE 'WAITING'
       END                                 AS state,
       CASE
         WHEN s.state != 'WAITING' THEN 'On CPU / runqueue'
         ELSE s.event
       END                                 AS event,
       w.seconds_in_wait                   secs_in_wait,
       LPAD(TRIM(CASE
                   WHEN w.wait_time_micro = 0 THEN ''
                   WHEN ROUND(w.wait_time_micro / 1e6, 1) > 60 THEN
                   To_char(( w.wait_time_micro /
                             1e6 ) / 60, '999G990D0')
                   || 'm'
                   WHEN ROUND(w.wait_time_micro / 1e6, 1) >= 1 THEN To_char(
                   w.wait_time_micro / 1e6, '999G990D0')
                                                                    || 's'
                   WHEN ROUND(w.wait_time_micro / 1e3, 1) >= 1 THEN To_char(
                   w.wait_time_micro / 1e3, '999G990')
                                                                    || 'ms'
                   ELSE To_char(w.wait_time_micro, '999G990')
                        || 'us'
                 END), 9, ' ')             wait_time,
       w.seq#,
       w.p1,
       w.p2,
       (SELECT command_name
        FROM   v$sqlcommand c
        WHERE  c.command_type = s.command) command,
       LPAD(TRIM(CASE
                   WHEN sql_id IS NULL
                         OR sql_exec_start IS NULL THEN ''
                   WHEN SYSDATE = sql_exec_start THEN '<1,0s'
                   WHEN ROUND(( ( SYSDATE - sql_exec_start ) * 86400 ), 1) = 0
                 THEN
                   '<1,0s'
                   WHEN ROUND(( ( SYSDATE - sql_exec_start ) * 86400 ), 1) = 1
                 THEN
                   '<1,0s'
                   WHEN ROUND(( ( SYSDATE - sql_exec_start ) * 86400 ) / 3600, 1
                        ) >= 1
                 THEN
                   To_char(( ( SYSDATE - sql_exec_start ) * 86400 ) / 3600,
                   '999G990D0'
                   )
                   ||
                 'h'
                   WHEN ROUND(( ( SYSDATE - sql_exec_start ) * 86400 ) / 60, 1)
                        >= 1
                 THEN
                   To_char
                   (( ( SYSDATE - sql_exec_start ) * 86400 ) / 60, '999G990D0')
                   ||
                   'm'
                   WHEN ROUND(( ( SYSDATE - sql_exec_start ) * 86400 ), 1) > 1
                 THEN
                   To_char(( ( SYSDATE - sql_exec_start ) * 86400 ), '999G990D0'
                   )
                   || 's'
                   ELSE To_char(( ( SYSDATE - sql_exec_start ) * 86400 ),
                        '999G990')
                        || 's'
                 END), 9, ' ')             sql_start,
       LPAD(ROUND(last_call_et / 60)
            || ':'
            || MOD(last_call_et, 60), 6)   last_call,
       plsql_entry_object_id               plsqloid,
       CASE
         WHEN s.status = 'ACTIVE' THEN 'A'
         WHEN s.status = 'INACTIVE' THEN 'I'
         WHEN s.status = 'KILLED' THEN 'K'
         WHEN s.status = 'CACHED' THEN 'C'
         WHEN s.status = 'SNIPED' THEN 'S'
         ELSE s.status
       END                                 status
FROM   gv$session_wait w,
       gv$session s,
       gv$instance i
WHERE  w.sid = s.sid
       AND s.inst_id = w.inst_id
       AND s.TYPE <> 'BACKGROUND'
       AND s.wait_class <> 'Idle'
       AND w.event <> 'SQL*Net message from client'
       AND w.event NOT LIKE 'wait for unread message on%'
       AND w.event NOT LIKE 'PX Deq%'
       AND i.inst_id = s.inst_id
       AND w.sid <> (SELECT sid
                     FROM   v$mystat
                     WHERE  ROWNUM = 1)
ORDER  BY s.inst_id,
          last_call_et DESC;