Ask TEM Dba

This script displays instance background process information. The script works when the database is MOUNTed or OPENed.

-- Oracle 8I
select
        A.SID,
        A.SERIAL#,
        A.PROGRAM,
        P.PID,
        P.SPID,
        A.OSUSER,       /* Who Started INSTANCE */
        A.TERMINAL,
        A.MACHINE,
        A.LOGON_TIME,
        B.NAME,
        B.Description
from
        v$session       A,
        v$process       P,
        v$bgprocess     B
where
        A.PADDR=B.PADDR
AND     A.PADDR=P.ADDR
and     A.type='BACKGROUND'
;

--Output-- ( UNIX )
----------

SID SERIAL# PROGRAM             OSUSER PID SPID  TERMINAL MACHINE
LOGON_TIME  NAME DESCRIPTION
  1       1 oracle@sunO817 (PMON) oracle   2 24782 UNKNOWN  sunO817
15-Nov-2003 PMON process cleanup
  2       1 oracle@sunO817 (DBW0) oracle   3 27864 UNKNOWN  sunO817
15-Nov-2003 DBW0 db writer process 0
  3       1 oracle@sunO817 (LGWR) oracle   4 28380 UNKNOWN  sunO817
15-Nov-2003 LGWR Redo etc.
  4       1 oracle@sunO817 (CKPT) oracle   5 28638 UNKNOWN  sunO817
15-Nov-2003 CKPT checkpoint
  5       1 oracle@sunO817 (SMON) oracle   6 28896 UNKNOWN  sunO817
15-Nov-2003 SMON System Monitor Process
  6       1 oracle@sunO817 (RECO) oracle   7 29154 UNKNOWN  sunO817
15-Nov-2003 RECO distributed recovery
  9       1 oracle@sunO817 (ARC0) oracle  10 29928 UNKNOWN  sunO817
15-Nov-2003 ARC0 Archival Process 0
 22     585 oracle@sunO817 (ARC1) oracle  16 53922 UNKNOWN  sunO817
16-Nov-2003 ARC1 Archival Process 1    

-- Oracle9I+
select
        A.SID,
        A.SERIAL#,
        A.PROGRAM,
        P.PID,
        P.SPID,
        A.OSUSER,       /* Who Started INSTANCE */
        A.TERMINAL,
        A.MACHINE,
        A.LOGON_TIME,
        B.NAME,
        B.Description
        ,P.PGA_USED_MEM
        ,P.PGA_FREEABLE_MEM
        ,P.PGA_MAX_MEM
from
        v$session       A,
        v$process       P,
        v$bgprocess     B
where
        A.PADDR=B.PADDR
AND     A.PADDR=P.ADDR
--and   A.type='BACKGROUND'
--Alternative (you can use BACKGROUND column from v$process )
--------------
AND     P.BACKGROUND=1
;

--Output-- ( Win2k )
----------

SID SERIAL# PROGRAM    OSUSER PID SPID TERMINAL MACHINE  LOGON_TIME NAME
DESCRIPTION
  1       1 ORACLE.EXE SYSTEM   2 1312 ADVTEST0 ADVTEST0 20-Nov-03  PMON
process cleanup
  2       1 ORACLE.EXE SYSTEM   3 972  ADVTEST0 ADVTEST0 20-Nov-03  DBW0
db writer process 0
  3       1 ORACLE.EXE SYSTEM   4 1276 ADVTEST0 ADVTEST0 20-Nov-03  LGWR
Redo etc.
  4       1 ORACLE.EXE SYSTEM   5 1228 ADVTEST0 ADVTEST0 20-Nov-03  CKPT
checkpoint
  5       1 ORACLE.EXE SYSTEM   6 792  ADVTEST0 ADVTEST0 20-Nov-03  SMON
System Monitor Process
  6       1 ORACLE.EXE SYSTEM   7 1448 ADVTEST0 ADVTEST0 20-Nov-03  RECO
distributed recovery