OracleSessions
Find the session behind an OS process ID
When top or ps shows an Oracle process burning CPU, enter its PID to see which session and SQL it belongs to.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF VERIFY OFF3COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A144COLUMN username FORMAT A155COLUMN program FORMAT A30 TRUNCATE6COLUMN status FORMAT A87COLUMN sql_id FORMAT A138COLUMN event FORMAT A35 TRUNCATE9 10SELECT s.sid || ',' || s.serial# AS sid_serial,11 s.username, s.program, s.status, s.sql_id, s.event12FROM v$process p13JOIN v$session s ON s.paddr = p.addr14WHERE p.spid = '&os_pid';Save it as ora-spid.sql and run it with SQL> @ora-spid.
More Oracle scripts: Sessions
- Long operations with time remaining (all instances)Long operations still running on any instance, with percent done and elapsed, remaining and estimated total minutes. RMAN is left out because it has…
- SQL active for 30 minutes or more (all instances)Active application sessions whose current call has run for at least 1,800 seconds, with instance, program, OS process ID, SQL_ID, plan hash value and…
- Active user sessionsUser sessions doing work right now on every instance: who, from which OS user and machine, the module, what they're waiting on, the SQL_ID, when they…
- Session count by user and machineWho is connected, from where, and how many are active. The quickest way to spot a connection pool that has run away.
- Everything about one sessionPrompts for a SID and shows the user, OS details, program, current and previous SQL_ID, wait event and the OS process ID behind it.
- Long-running operations in progressFull scans, sorts, RMAN and index builds that Oracle tracks in V$SESSION_LONGOPS, with percent done and estimated seconds remaining.