Oracle Long Operations Monitor
This script displays information about currently running long operations in the database. It joins V$SESSION and V$SESSION_LONGOPS to show session details, elapsed time, remaining time, and percentage progress, helping DBAs monitor long-running tasks and track execution progress.
oraclesqlmonitoring-alertsv1.0.0
0 stars0 downloads14 views0 comments
By OracleDba • Created
Code
(27 lines)123456789101112131415161718192021222324252627
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/longops.sql
-- Author : Tim Hall
-- Description : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax : @longops
-- Last Modified: 03/07/2003
-- -----------------------------------------------------------------------------------
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
AND time_remaining > 0;