V$PROCESS - Find OS PID, PGA Memory & Runaway Processes

V$PROCESS

Overview

V$PROCESS contains one row for every Oracle server process currently attached to the instance, including background processes (PMON, SMON, DBWn, etc.) and dedicated or shared server processes handling client connections. The most critical use of this view is obtaining the operating system process ID (SPID) for a given Oracle session, which is required to investigate or kill a process at the OS level. It also exposes PGA memory breakdown per process, making it essential for diagnosing memory pressure.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$PROCESS or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
ADDR	RAW(8)	Address of the process object in the SGA — primary join key to V$SESSION.PADDR
PID	NUMBER	Oracle internal process ID
SPID	VARCHAR2(24)	Operating system process ID — use this to identify the process with OS tools
SOSID	VARCHAR2(32)	Operating system session ID (Windows thread ID or Unix process group)
USERNAME	VARCHAR2(15)	OS username running the process (typically oracle)
SERIAL#	NUMBER	Process serial number — incremented each time the slot is reused
TERMINAL	VARCHAR2(30)	OS terminal name
PROGRAM	VARCHAR2(48)	OS program name (e.g., oracle, tnslsnr, sqlplus)
TRACEID	VARCHAR2(255)	Trace file identifier
TRACEFILE	VARCHAR2(513)	Full path to the session trace file for this process
BACKGROUND	VARCHAR2(1)	1 if this is a background process, NULL for foreground
LATCHWAIT	VARCHAR2(16)	Address of latch the process is waiting for (NULL if not waiting)
LATCHSPIN	VARCHAR2(16)	Address of latch the process is spinning on
PGA_USED_MEM	NUMBER	PGA memory currently in use by this process (bytes)
PGA_ALLOC_MEM	NUMBER	PGA memory currently allocated (used + freeable) (bytes)
PGA_FREEABLE_MEM	NUMBER	Allocated PGA memory that could be freed (bytes)
PGA_MAX_MEM	NUMBER	Maximum PGA ever allocated to this process (bytes)
CON_ID	NUMBER	Container ID (0 = CDB-wide, or the PDB’s CON_ID)

Essential Queries

Basic Usage

-- All foreground (client) processes with basic info
SELECT
    p.pid,
    p.spid                                          AS os_pid,
    p.username                                      AS os_user,
    p.program                                       AS os_program,
    ROUND(p.pga_used_mem  / 1048576, 1)             AS pga_used_mb,
    ROUND(p.pga_alloc_mem / 1048576, 1)             AS pga_alloc_mb,
    ROUND(p.pga_max_mem   / 1048576, 1)             AS pga_max_mb
FROM
    v$process p
WHERE
    p.background IS NULL     -- exclude background processes
ORDER BY
    p.pga_alloc_mem DESC
FETCH FIRST 30 ROWS ONLY;

Monitoring Query

-- Top PGA consumers linked to session and current SQL
SELECT
    p.spid                                          AS os_pid,
    s.sid,
    s.serial#,
    s.username                                      AS oracle_user,
    s.status,
    s.wait_class,
    s.event,
    ROUND(p.pga_used_mem      / 1048576, 1)        AS pga_used_mb,
    ROUND(p.pga_alloc_mem     / 1048576, 1)        AS pga_alloc_mb,
    ROUND(p.pga_freeable_mem  / 1048576, 1)        AS pga_free_mb,
    ROUND(p.pga_max_mem       / 1048576, 1)        AS pga_max_mb,
    s.sql_id,
    s.module,
    s.machine
FROM
    v$process p
    JOIN v$session s ON s.paddr = p.addr
WHERE
    s.type = 'USER'
    AND p.pga_alloc_mem > 52428800          -- processes holding more than 50 MB
ORDER BY
    p.pga_alloc_mem DESC
FETCH FIRST 25 ROWS ONLY;

Combined with Other Views

-- Full session-to-OS-process mapping with SQL text
SELECT
    p.spid                                          AS os_pid,
    s.sid,
    s.serial#,
    s.username                                      AS oracle_user,
    s.status,
    s.seconds_in_wait,
    s.event,
    s.wait_class,
    ROUND(p.pga_alloc_mem / 1048576, 1)            AS pga_alloc_mb,
    s.logon_time,
    s.machine,
    s.program                                      AS client_program,
    s.module,
    s.action,
    SUBSTR(sq.sql_text, 1, 100)                    AS current_sql,
    p.tracefile
FROM
    v$process p
    JOIN v$session s  ON s.paddr  = p.addr
    LEFT JOIN v$sql sq ON sq.sql_id      = s.sql_id
                      AND sq.child_number = 0
WHERE
    s.type = 'USER'
ORDER BY
    s.status,
    s.seconds_in_wait DESC;

Advanced Analysis

-- Process memory analysis: breakdown of PGA usage vs freeable vs high-water mark
SELECT
    p.spid                                              AS os_pid,
    s.sid,
    s.username                                          AS oracle_user,
    ROUND(p.pga_used_mem      / 1048576, 2)            AS pga_used_mb,
    ROUND(p.pga_alloc_mem     / 1048576, 2)            AS pga_alloc_mb,
    ROUND(p.pga_freeable_mem  / 1048576, 2)            AS pga_freeable_mb,
    ROUND(p.pga_max_mem       / 1048576, 2)            AS pga_max_mb,
    ROUND((p.pga_alloc_mem - p.pga_used_mem - p.pga_freeable_mem)
          / 1048576, 2)                                AS pga_overhead_mb,
    CASE
        WHEN p.pga_max_mem > 0
        THEN ROUND(p.pga_alloc_mem / p.pga_max_mem * 100, 0)
        ELSE NULL
    END                                                AS pct_of_peak,
    s.event,
    s.sql_id
FROM
    v$process p
    JOIN v$session s ON s.paddr = p.addr
WHERE
    s.type = 'USER'
    AND p.pga_max_mem > 104857600       -- sessions that peaked above 100 MB PGA
ORDER BY
    p.pga_max_mem DESC
FETCH FIRST 20 ROWS ONLY;

-- Finding a runaway process: locate session by OS PID and get its trace file
-- Replace &os_pid with the PID reported by top/ps on Linux
SELECT
    p.spid                                          AS os_pid,
    p.pid                                           AS oracle_pid,
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.seconds_in_wait,
    s.event,
    s.wait_class,
    s.sql_id,
    s.module,
    s.action,
    s.machine,
    p.tracefile,
    ROUND(p.pga_alloc_mem / 1048576, 1)            AS pga_alloc_mb
FROM
    v$process p
    LEFT JOIN v$session s ON s.paddr = p.addr
WHERE
    p.spid = '&os_pid';

-- Summary of total PGA usage across all processes
SELECT
    ROUND(SUM(p.pga_used_mem)     / 1048576, 1)    AS total_pga_used_mb,
    ROUND(SUM(p.pga_alloc_mem)    / 1048576, 1)    AS total_pga_alloc_mb,
    ROUND(SUM(p.pga_freeable_mem) / 1048576, 1)    AS total_pga_freeable_mb,
    ROUND(SUM(p.pga_max_mem)      / 1048576, 1)    AS total_pga_peak_mb,
    COUNT(CASE WHEN p.background IS NULL  THEN 1 END) AS foreground_procs,
    COUNT(CASE WHEN p.background = '1'    THEN 1 END) AS background_procs,
    COUNT(*)                                        AS total_procs
FROM
    v$process p;

Common Use Cases

• OS-level process identification — The SPID column is the bridge from Oracle session to OS process. Use it with kill, gdb, pstack, perf, or Windows Task Manager to investigate at the OS layer.
• Trace file location — TRACEFILE gives the exact path to the diagnostic trace file for a process, eliminating the need to search the ADR base manually.
• PGA memory pressure diagnosis — When PGA_AGGREGATE_LIMIT is being hit (ORA-04036), query this view to identify which processes are holding the most PGA.
• Latch contention investigation — LATCHWAIT and LATCHSPIN expose which processes are spinning or waiting on specific latches, supplementing V$LATCH data.
• Background process health — Filter on BACKGROUND = ‘1’ to verify all expected background processes (DBWn, LGWR, CKPT, SMON, PMON, ARCn, etc.) are running.
• Session-to-process join — V$SESSION.PADDR = V$PROCESS.ADDR is one of the most commonly used joins in Oracle DBA scripting — always the starting point for OS-level diagnostics.

Related Views

• V$SESSION — Join V$SESSION.PADDR = V$PROCESS.ADDR to link Oracle sessions to their OS process
• V$ACTIVE_SESSION_HISTORY — ASH samples reference SESSION_ID which links back through V$SESSION to V$PROCESS
• V$PGASTAT — Instance-level PGA statistics and policy compliance
• V$SQL_MONITOR — For monitored statements, join through V$SESSION to get the OS process running them
• V$LATCH — Correlate LATCHWAIT/LATCHSPIN addresses with V$LATCH.ADDR for latch identification

Version Notes

• Oracle 7/8i: View has existed since early Oracle versions as a core diagnostic view. SPID has been stable across all versions.
• Oracle 10g: TRACEFILE column added, providing the full ADR-relative or absolute path to the process trace file.
• Oracle 11g: PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, and PGA_MAX_MEM columns added, making per-process PGA analysis possible directly from this view.
• Oracle 12c: SOSID column added for Windows thread-level identification. CON_ID added for multitenant environments — background processes report CON_ID = 0.
• Oracle 19c: TRACEFILE paths reflect the new ADR structure under $ORACLE_BASE/diag. On Linux, the process user (USERNAME) is typically the oracle OS user.
• Oracle 21c / 23ai: In 23ai, additional diagnostic attributes may appear for processes handling JSON Relational Duality views and other new workload types. The ADDR column remains the stable join key across all releases.