Skip to content
Oracle DBA Scripts & Database Utilities

Oracle Process Information (vprocess.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive information about Oracle processes:

  • Lists all Oracle server and background processes
  • Shows process IDs (Oracle PID and OS SPID)
  • Displays user connections and terminal information
  • Identifies background processes vs. user sessions
  • Shows any latch waits affecting processes

The Script

Section titled “The Script”
rem  vprocess.sql
rem
ttitle 'Oracle Processes'
rem
col pid format 990 heading 'PID'
col spid format a8 heading 'SERVER|PROCESS'
col username format a8 heading 'USERNAME'
col serial# format 90 heading 'SER'
col terminal format a10 heading 'TERMINAL'
col background format a1 heading 'B'
col program format a31 heading 'PROGRAM'
col latchwait format a8 heading 'LATCH|WAIT'
col latchspin format a8 heading 'LATCH|SPIN'
rem
select pid, spid, username, serial#,
       terminal, background, program, latchwait
  from v$process;

Usage

Section titled “Usage”
-- Basic usage
@vprocess.sql

Required Privileges

Section titled “Required Privileges”
SELECT ON V$PROCESS

Sample Output

Section titled “Sample Output”
Oracle Processes


 PID SERVER   USERNAME SER TERMINAL   B PROGRAM                         LATCH
     PROCESS                                                            WAIT
---- -------- -------- --- ---------- - ------------------------------- --------
   1 12345              0             Y oracle@server1 (PMON)
   2 12346              0             Y oracle@server1 (PSP0)
   3 12347              0             Y oracle@server1 (VKTM)
   4 12348              0             Y oracle@server1 (GEN0)
   5 12349              0             Y oracle@server1 (DIAG)
   6 12350              0             Y oracle@server1 (DBRM)
  15 12359    oracle   123 pts/1      N sqlplus@server1 (TNS V1-V3)
  16 12360    appuser  456 unknown    N jdbc thin client

Key Output Columns

Section titled “Key Output Columns”
  • PID - Oracle internal process ID
  • SERVER PROCESS - Operating system process ID (SPID)
  • USERNAME - OS username (for user sessions)
  • SER - Serial number
  • TERMINAL - Terminal or connection source
  • B - Background process flag (Y/N)
  • PROGRAM - Program name or connection type
  • LATCH WAIT - Address if process is waiting on a latch

Understanding the Output

Section titled “Understanding the Output”

Background Processes (B = Y):

  • PMON - Process Monitor
  • SMON - System Monitor
  • DBWR - Database Writer
  • LGWR - Log Writer
  • CKPT - Checkpoint Process
  • VKTM - Virtual Keeper of Time
  • DIAG - Diagnosability Process

User Processes (B = N):

  • sqlplus - SQL*Plus connections
  • jdbc thin client - JDBC connections
  • Oracle.exe - Windows Oracle processes

Common Use Cases

Section titled “Common Use Cases”

Process Monitoring

@vprocess.sql
-- Monitor active Oracle processes
-- Identify user vs. background processes

Performance Troubleshooting

@vprocess.sql
-- Check for processes waiting on latches
-- Identify problematic connections

Connection Analysis

@vprocess.sql
-- See all active database connections
-- Identify connection sources and types

Capacity Planning

@vprocess.sql
-- Count active processes
-- Monitor process usage patterns

Troubleshooting Tips

Section titled “Troubleshooting Tips”

High Process Count:

  • Check PROCESSES parameter setting
  • Identify applications not closing connections
  • Look for connection pooling issues

Latch Waits:

  • Processes showing latch waits may indicate contention
  • Investigate specific latch types
  • Consider tuning or application changes

Background Process Issues:

  • Missing background processes indicate database problems
  • Extra background processes normal in RAC environments
  • Check alert log for process-related errors
Section titled “Related Scripts”