Oracle: SQL for displaying current processes, userids

use the followings sql for displaying current running processes, userids:

SET TERMOUT    ON
SET HEADING    ON


SET PAGESIZE   40
SET LINESIZE   180
SET NEWPAGE    0
SET VERIFY     OFF
SET ECHO       OFF
SET UNDERLINE  =
SET FEEDBACK   ON
SET LONG       1000
SET EMBED      ON


COLUMN sid_ser        FORMAT a10            HEADING ' SID/Ser'
COLUMN sid            FORMAT 99999          HEADING ' SID'
COLUMN username       FORMAT a6             HEADING 'Oracle|User'
COLUMN osuser         FORMAT a10    TRUNC   HEADING 'O/S User'
COLUMN machine        FORMAT a10            HEADING 'Machine'
COLUMN program        FORMAT a20            HEADING 'Program'
COLUMN F_Ground       FORMAT 99999          HEADING 'F''Ground|Process'
COLUMN B_Ground       FORMAT 99999          HEADING 'B''Ground|Process'
COLUMN sql_text       FORMAT a45 word_wrap  HEADING 'SQL Text'
COLUMN disk_reads     FORMAT 99,999         HEADING 'Disk|Reads|(*1000)'
COLUMN buffer_gets    FORMAT 9,999,999      HEADING 'Buffer|Gets|(*1000)'
COLUMN rows_processed FORMAT 9,999,999      HEADING 'Rows|Processed|(*1000)'
COLUMN sorts          FORMAT 99,999         HEADING 'Sorts'
COLUMN executions     FORMAT 9,999,999,999      HEADING 'Exctn'


TTITLE Center 'SQL Currently Executing'


SELECT /*+ ORDERED */
       s.sid || ',' || s.serial# as sid_ser,
       s.username,
       s.osuser,
       x.sql_text,
       x.disk_reads     / 1000 AS disk_reads,
       x.buffer_gets    / 1000 AS buffer_gets,
       x.rows_processed / 1000 AS rows_processed,
       x.sorts
       ,x.executions
       ,x.address
  FROM v$session S,
       v$process P,
       v$sql X
 WHERE LOWER(s.osuser)      LIKE LOWER(NVL('&os_user%', '%'))
   AND s.username           LIKE UPPER(NVL('&oracle_user%', '%'))
   AND s.sid                LIKE NVL('&sid', '%')
   AND s.type                 != 'BACKGROUND'
   AND s.sql_address           = x.address
   AND s.sql_hash_value        = x.hash_value
--   AND s.username         NOT IN ('SYS','SYSTEM')
   AND s.paddr                = p.addr
 ORDER
     BY S.sid
;

No comments: