Command to know execution flow while the PL/SQL block is running?

Question

As part my project I had run a PL/SQL block to process more than 13,00,000 records from 12 different tables.

To know the execution flow I have already included SET SERVEROUTPUT ON command.

My requirement is to know the execution flow while the block is still running and the SET SERVEROUTPUT ON only displays the flow after PL/SQL block execution.

SET SERVEROUTPUT ON ;
PL/SQL Block;

Duplicate of [Is there any way to flush output from PL/SQL in Oracle?](https://stackoverflow.com/questions/1472587/is-there-any-way-to-flush-output-from-pl-sql-in-oracle) — Kaushik Nayak, Jan 25 '19 at 05:03

score 1 · Answer 1 · answered Jan 25 '19 at 08:48

Instead of using a dedicated logging table and AUTONOMOUS_TRANSACTION you could also use package DBMS_APPLICATION_INFO.

BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE('Starting at '||TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), 'Starting procedure');

   ... some PL/SQL code

   -- If you like you can set intermediate updates at any time:
   DBMS_APPLICATION_INFO.SET_ACTION('Still running...');

   ... some more PL/SQL code

   ... at the end you may reset application info 
   DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;

While procedure is running you can query information by

SELECT SID, SERIAL#, USERNAME, OSUSER, MODULE, ACTION
from V$SESSION
where USERNAME = ...;

I don't have access to update the Procedure as this is a production activity in my project. — Shanmukha Reddy, Jan 25 '19 at 12:57

Command to know execution flow while the PL/SQL block is running?

1 Answers1