OracleSessions
Long-running operations in progress
Full scans, sorts, RMAN and index builds that Oracle tracks in V$SESSION_LONGOPS, with percent done and estimated seconds remaining.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A144COLUMN opname HEADING "OPERATION" FORMAT A30 TRUNCATE5COLUMN target FORMAT A35 TRUNCATE6COLUMN pct_done HEADING "DONE %" FORMAT 990.97COLUMN elapsed_s HEADING "ELAPSED S" FORMAT 999,9908COLUMN remain_s HEADING "REMAINING S" FORMAT 999,9909COLUMN sql_id FORMAT A1310 11SELECT sid || ',' || serial# AS sid_serial,12 opname, target,13 ROUND(100 * sofar / NULLIF(totalwork, 0), 1) AS pct_done,14 elapsed_seconds AS elapsed_s,15 time_remaining AS remain_s,16 sql_id17FROM v$session_longops18WHERE sofar < totalwork19ORDER BY start_time;Save it as ora-longops.sql and run it with SQL> @ora-longops.
Part of these runbooks
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.
- 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.