V$PROCESS

V$PROCESS displays information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.

Column	Datatype	Description
ADDR	RAW(4 | 8)	Address of the process state object
PID	NUMBER	Oracle process identifier
SPID	VARCHAR2(24)	Operating system process identifier
USERNAME	VARCHAR2(15)	Operating system process username Note: Any two-task user coming across the network has "-T" appended to the username.
SERIAL#	NUMBER	Process serial number
TERMINAL	VARCHAR2(30)	Operating system terminal identifier
PROGRAM	VARCHAR2(48)	Program in progress
TRACEID	VARCHAR2(255)	Trace file identifier
TRACEFILE	VARCHAR2(513)	Trace file name of the process
BACKGROUND	VARCHAR2(1)	1 for a background process; NULL for a normal process
LATCHWAIT	VARCHAR2(8)	Address of the latch the process is waiting for; NULL if none
LATCHSPIN	VARCHAR2(8)	Address of the latch the process is spinning on; NULL if none
PGA_USED_MEM	NUMBER	PGA memory currently used by the process
PGA_ALLOC_MEM	NUMBER	PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)
PGA_FREEABLE_MEM	NUMBER	Allocated PGA memory which can be freed
PGA_MAX_MEM	NUMBER	Maximum PGA memory ever allocated by the process

Note:

1.       Three columns in the view report the PGA memory allocated and used by an Oracle process: PGA_USED_MEM, PGA_ALLOCATED_MEM, PGA_MAX_MEM.

2.       Scripts using V$PROCESS

--  PGA usage select program, pga_used_mem/1024/1024, pga_alloc_mem/1024/1024,        PGA_FREEABLE_MEM/1024/1024, pga_max_mem/1024/1024 from   v$process order by 5 desc;

-- monitor current user activity in the database by generating a list of the SQL statement being executed in the database select c.spid, b.osuser, b.username, b.sid, b.serial#,        a.sql_text   from v$sqltext a, v$session b, v$process c  where a.address = b.sql_address -- and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */    and b.paddr = c.addr    and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece

-- obtain session information select 'Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||        s.audsid||chr(10)|| 'DB User / OS User : '||s.username||        '   /   '||s.osuser||chr(10)|| 'Machine - Terminal : '||        s.machine||'  -  '|| s.terminal||chr(10)||        ' OS Process Ids : '||        s.process||' (Client)  '||p.spid||' (Server)'|| chr(10)||        'Client Program Name : '||s.program "Session Info"   from v$process p, v$session s  where p.addr = s.paddr    and s.audsid = userenv('SESSIONID')

-- list inactive users to ensure whether to kill a session select s.username||' '||s.osuser userinfo, s.terminal, s.sid, s.serial#,        p.spid, s.process , w.seq# from   v$session s, v$process p, v$session_wait w where  p.addr = s.paddr and    s.sid = w.sid and    w.event = 'SQL*Net message from client' and    s.status = 'INACTIVE' order by s.osuser, s.terminal;

-- script to map connected clients to their associated shadow server process select     p.spid SHADOW_ID,             s.server SHADOW_TYPE,            s.machine CLIENT_NODE,            s.process CLIENT_ID,            s.osuser CLIENT_ACCOUNT,            s.program CLIENT_PROGRAM     from   v$session s, v$process p     where  p.addr = s.paddr     order by p.spid;

3.       Useful join column: V$PROCESS.ADDR join to V$SESSION.PADDR

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-23 Friday