OracleSessions

Everything about one session

Prompts for a SID and shows the user, OS details, program, current and previous SQL_ID, wait event and the OS process ID behind it.

Prompts for input

Not yet verified. How scripts are tested

ora-session-detail.sql

1CLEAR COLUMNS2SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF VERIFY OFF3COLUMN sid_serial  HEADING "SID,SERIAL#" FORMAT A144COLUMN username    FORMAT A155COLUMN osuser      HEADING "OS USER"     FORMAT A156COLUMN machine     FORMAT A25 TRUNCATE7COLUMN program     FORMAT A30 TRUNCATE8COLUMN module      FORMAT A30 TRUNCATE9COLUMN status      FORMAT A810COLUMN sql_id      FORMAT A1311COLUMN prev_sql_id HEADING "PREV SQL_ID" FORMAT A1312COLUMN event       FORMAT A40 TRUNCATE13COLUMN logged_on   HEADING "LOGGED ON"   FORMAT A1614COLUMN os_pid      HEADING "OS PID"      FORMAT A1015 16SELECT s.sid || ',' || s.serial# AS sid_serial,17       s.username, s.osuser, s.machine, s.program, s.module,18       s.status, s.sql_id, s.prev_sql_id, s.event,19       TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI') AS logged_on,20       p.spid AS os_pid21FROM v$session s22JOIN v$process p ON p.addr = s.paddr23WHERE s.sid = &sid;

Save it as ora-session-detail.sql and run it with SQL> @ora-session-detail.

Open in denrepo

Helps with

ORA-01000: maximum open cursors exceeded

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.
Find the session behind an OS process IDWhen top or ps shows an Oracle process burning CPU, enter its PID to see which session and SQL it belongs to.
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.