A batch job that runs for an hour should say how far it has got. DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS writes your own progress into V$SESSION_LONGOPS, the view Oracle uses for long operations such as large scans, so anyone can see the work done, the total, and an estimate of the time left.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
v_rindex := dbms_application_info.set_session_longops_nohint; -- start a new row dbms_application_info.set_session_longops( rindex => v_rindex, -- in out: identifies the row slno => v_slno, -- in out: internal, pass it back each time op_name => 'operation name', target_desc => 'what is processed', sofar => units_done, totalwork => total_units, units => 'unit name');
Report Progress
The loop reports four steps of a repricing run in percent, then queries the view.
Example:
declare
v_rindex binary_integer := dbms_application_info.set_session_longops_nohint;
v_slno binary_integer;
begin
for i in 1 .. 4 loop
dbms_application_info.set_session_longops(
rindex => v_rindex, slno => v_slno, op_name => 'Repricing routes',
sofar => i * 25, totalwork => 100, units => 'percent');
end loop;
end;
/
select opname, sofar, totalwork, units from v$session_longops
where sid = sys_context('USERENV', 'SID') and opname = 'Repricing routes';Output:
PL/SQL procedure successfully completed. OPNAME SOFAR TOTALWORK UNITS ___________________ ________ ____________ __________ Repricing routes 100 100 percent
The row shows the operation at 100 of 100 percent. While the loop was running, another session would have seen it climb from 25.
The Message Monitoring Tools Show
Example:
declare
v_rindex binary_integer := dbms_application_info.set_session_longops_nohint;
v_slno binary_integer;
begin
for i in 1 .. 4 loop
dbms_session.sleep(0.5); -- one step of real work
dbms_application_info.set_session_longops(
rindex => v_rindex, slno => v_slno, op_name => 'Loading fares',
target_desc => 'FARES', sofar => i, totalwork => 4, units => 'files');
end loop;
end;
/
-- the message monitoring tools show
select message, elapsed_seconds
from v$session_longops
where sid = sys_context('USERENV', 'SID') and opname = 'Loading fares'
order by start_time desc
fetch first 1 row only;Output:
PL/SQL procedure successfully completed. MESSAGE ELAPSED_SECONDS _______________________________________________ __________________ Loading fares: FARES : 4 out of 4 files done 2
With a target description and units, Oracle builds a readable MESSAGE, and ELAPSED_SECONDS and TIME_REMAINING are calculated from the timing of your calls.
Things to Know
- Call SET_SESSION_LONGOPS_NOHINT once per operation and pass the same RINDEX and SLNO variables on every update.
- Use units that progress evenly, such as rows or files, so the time estimate is meaningful.
- Rows stay in V$SESSION_LONGOPS after the work finishes, so filter by operation name and start time.
Related Guides
Conclusion
SET_SESSION_LONGOPS publishes the progress of your own long-running PL/SQL in V$SESSION_LONGOPS, with work done, total, a message, and a time estimate. Update it at each step of a batch job so its progress is visible without asking the developer.
