DBMS_APPLICATION_INFO to manage long-running operation
About
Long running operation is defined as longer than three to five seconds.
If you execute a long-running command, a dynamic performance view VSESSION_LONGOPS will be populated with information with the following information :
DBMS_APPLICATION_INFO can help to answer this question :
What is the session doing, what form is it running, what code module is executing ?
How far along is that stored procedure ?
How far along is that batch job ?
What bin variable values where being used on that query ?
DBMS_APPLICATION_INFO allows you to set up to three columns in your row of the VSESSION table :
the CLIENT_INFO
the ACTION
the MODULE columns.
The value you set in the V$ tables are immediately visible. You do not need to commit them to see them, making them very useful for communcating with the outside world
You can use :
SET_CLIENT_INFO to store useful “state”
SET_MODULE/GET_MODULE to set the module when you enter and exit routines (at least the major). In this fashion, you can query VSESSION and see what routine you are currently.
SET_SESSION_LONGOPS to store any routine that will take more than a couple of second
Reference