V$process

Enable a 10046 Trace for an Oracle Session Purpose A 10046 trace returns wait events only if the level includes them. Enabling the trace at level 1 and then wondering why the file shows no waits is the most common 10046 mistake in production diagnosis — the output has elapsed times and call counts, but none of the db …

List Files Opened by an Oracle Process with lsof -p Purpose lsof (LiSt Open Files) is the UNIX tool for inspecting which processes have which files open. For an Oracle DBA it is the fastest answer to four common questions: which datafiles is this server process touching, which trace file is PMON writing into right now, …

Display Session status associated with the specified os process id SQL Code 1select s.username 2, s.sid 3, s.serial# 4, p.spid 5, last_call_et 6, status 7from V$SESSION s 8, V$PROCESS p 9where s.PADDR = p.ADDR 10and p.spid='&pid' 11/ Sample Oracle Output 1Enter value for pid: 9999 2old 10: and p.spid='&pid' 3new 10: …

Select Oracle user info including os pid SQL Code 1col "SID/SERIAL" format a10 2col username format a20 3col osuser format a15 4col program format a50 5select s.sid || ',' || s.serial# "SID/SERIAL" 6, s.username 7, s.osuser 8, p.spid "OS PID" 9, s.program 10from v$session s 11, v$process p 12Where s.paddr = p.addr …