How to check active processes in Oracle

In Oracle, you can check currently active process. Following is the query with output. This query is shown for all users. So you can filter processes by USERNAME.

SET pagesize 55
SET linesize 170
col SQL format a80
col SERVER heading 'SERVER' format a10
col EVENT heading 'WAITING' format a30 fold_after
col OSUSER heading 'OSUSER' format a8
col USERNAME heading 'USERNAME' format a8
col PID heading 'OSPID' format 99999
col DISK_READS heading 'DISK I/O' format 99999999
col BUFFER_GETS heading 'BUFFER|GETS' format 99999999
 
SELECT  SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
     V$SESSION.OSUSER OSUSER,
     V$SESSION.SERVER SERVER,
     V$SQLAREA.DISK_READS DISK_READS,
     V$SQLAREA.BUFFER_GETS BUFFER_GETS,
        SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
     V$SESSION.PROCESS PID,
     V$SESSION_WAIT.EVENT EVENT,
        V$SQLAREA.SQL_TEXT SQL
FROM    V$SESSION_WAIT, V$SQLAREA, V$SESSION
WHERE   V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
     V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
        V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
     V$SESSION.STATUS = 'ACTIVE' AND
     V$SESSION_WAIT.EVENT != 'client message'
ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;

Here goes a sample output from the above query,

SQL> SET pagesize 55
SQL> SET linesize 170
SQL> col SQL format a80
SQL> col SERVER heading 'SERVER' format a10
SQL> col EVENT heading 'WAITING' format a30 fold_after
SQL> col OSUSER heading 'OSUSER' format a8
SQL> col USERNAME heading 'USERNAME' format a8
SQL> col PID heading 'OSPID' format 99999
SQL> col DISK_READS heading 'DISK I/O' format 99999999
SQL> col BUFFER_GETS heading 'BUFFER|GETS' format 99999999
SQL>
SQL> SELECT  SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
  2       V$SESSION.OSUSER OSUSER,
  3       V$SESSION.SERVER SERVER,
  4       V$SQLAREA.DISK_READS DISK_READS,
  5       V$SQLAREA.BUFFER_GETS BUFFER_GETS,
  6          SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
  7       V$SESSION.PROCESS PID,
  8       V$SESSION_WAIT.EVENT EVENT,
  9          V$SQLAREA.SQL_TEXT SQL
 10  FROM    V$SESSION_WAIT, V$SQLAREA, V$SESSION
 11  WHERE   V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
 12       V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
 13          V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
 14       V$SESSION.STATUS = 'ACTIVE' AND
 15       V$SESSION_WAIT.EVENT != 'client message'
 16  ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;

                                          BUFFER
USERNAME OSUSER   SERVER      DISK I/O      GETS LOCKWAIT OSPID        WAITING
-------- -------- ---------- --------- --------- -------- ------------ ------------------------------
SQL
--------------------------------------------------------------------------------
SYS      USER-PC\ DEDICATED          0        51          2072:5608    SQL*Net message to client
         Administ
         rator
SELECT  SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,      V$SESSION.OSUSER OSUSER,
    V$SESSION.SERVER SERVER,      V$SQLAREA.DISK_READS DISK_READS,      V$SQLARE
A.BUFFER_GETS BUFFER_GETS,         SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
  V$SESSION.PROCESS PID,      V$SESSION_WAIT.EVENT EVENT,         V$SQLAREA.SQL_
TEXT SQL FROM    V$SESSION_WAIT, V$SQLAREA, V$SESSION WHERE   V$SESSION.SQL_ADDR
ESS = V$SQLAREA.ADDRESS AND      V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE
 AND         V$SESSION.SID = V$SESSION_WAIT.SID (+) AND      V$SESSION.STATUS =
'ACTIVE' AND      V$SESSION_WAIT.EVENT != 'client message' ORDER BY V$SESSION.LO
CKWAIT ASC, V$SESSION.USERNAME