s.username, s.program, s.module,
lo.opname operation_,
TO_CHAR(lo.start_time,'mm/dd/yyyy hh24:mi:ss') start_time,
TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') as_of,
lo.sofar||'/'||lo.totalwork work_ratio,
ROUND(100*lo.sofar/lo.totalwork,5) pct_complete,
lo.target, lo.elapsed_seconds,
lo.time_remaining,
lo.units,
q.sql_text exec_query,
s.status, lo.message
FROM v$session_longops lo,
v$session s,
v$sql q
WHERE lo.sid = s.sid
AND lo.serial# = s.serial#
AND s.sql_address = q.address
AND lo.sofar != lo.totalwork;
SELECT
s.sid, s.serial# serial_No,
s.username, s.program, s.module,
lo.opname operation_,
TO_CHAR(lo.start_time,'mm/dd/yyyy hh24:mi:ss') start_time,
TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') as_of,
lo.sofar||'/'||lo.totalwork work_ratio,
ROUND(100*lo.sofar/lo.totalwork,5) pct_complete,
lo.target, lo.elapsed_seconds,
lo.time_remaining,
lo.units,
q.sql_text exec_query,
s.status, lo.message
FROM v$session_longops lo,
v$session s,
v$sql q
WHERE lo.sid = s.sid
AND lo.serial# = s.serial#
AND s.sql_address = q.address
AND lo.time_remaining > 0;
SELECT
sl.sid, sl.serial#, sl.sofar, sl.totalwork,
ROUND(100*sl.sofar/sl.totalwork,5) pct_complete,
dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl,
v$datapump_job dp
WHERE sl.opname = dp.job_name
AND sl.sofar != sl.totalwork
-- PL/SQL code below shows setup to create data in the v$session_longops,
-- as Oracle completes processing each of the ten steps. Any of the above
-- queries (1 or 2) can be used to analyze the long executing processes.
DECLARE
v_rindex PLS_INTEGER;
v_slno PLS_INTEGER;
v_sofar PLS_INTEGER := 0;
v_totalwork PLS_INTEGER := 10;
BEGIN
v_rindex := Dbms_Application_Info.Set_Session_Longops_Nohint;
WHILE sofar <= v_totalwork LOOP
v_sofar := v_sofar + 1;
DBMS_APPLICATION_INFO.set_session_longops(rindex => v_rindex,
slno => v_slno,
op_name => 'MView Refresh',
target => v_obj,
context => 1000,
sofar => v_sofar,
totalwork => v_totalwork,
target_desc => 'EDW_FACT_TABLE',
units => 'Rows Processed');
END LOOP;
