Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle dbms_application_info tips

Oracle Tips by Burleson Consulting

The dbms_application_info procedure is used to add information to the v$session and v$session_longops, to allow you to track the execution time of long-running batch jobs.

The dbms_application)info package has these oroicedyres:

set_modules:  Registers the program and adds initial functionality.

set_action:  Produces details on subsequent activity against Oracle tables.

set_client_info:  Allows you t place additional documentation information about the execution program.

v$session view: Add three columns to v$session about the job module, action and client_info columns.

set_session_longops:  For v$session_longops to work effectively, you must know the total amount of work you are planning to do and how much work you have done so far.

DBMS_APPLICATION_INFO.set_session_longops(rindex     => v_rindex,
                                         slno        => v_slno,
                                         op_name     => 'Batch Load',
                                         target      => v_obj,
                                         context     => 0,
                                         sofar       => v_sofar,
                                         totalwork   => v_totalwork,
                                         target_desc => 'BATCH_LOAD_TABLE',
                                         units       => 'rows processed');

v$session_longops view:  For hv$session_longops to display ongoing details, you can ussie this SQL:



Setting up a Test Environment Using Oracle dbms_application_info

This database procedure uses the Oracle dbms_system package and the Oracle dbms_application_info packages to write a user defined string to the alert log at the start and end of the job.  The body of the procedure loops 100 times with a sleep of one second in each loop.  It uses the Oracle dbms_application_info package to write information to the v$session and v$session_longops views. 

Explanations of the dbms_application_info and dbms_system packages are included as they can simplify the identification and monitoring of sessions related to scheduled jobs.

module_name => 'my_job_proc',
action_name => p_text || ': Start.');

l_rindex := Dbms_Application_Info.Set_Session_Longops_Nohint;
l_total := 100;

FOR i IN 1 .. l_total LOOP
action_name => p_text || ': Sleep ' || i || ' of ' || l_total || '.');

rindex => l_rindex,
slno => l_slno,

Description of the DBMS_APPLICATION_INFO package:

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                OUT

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                OUT
 ACTION_NAME                    VARCHAR2                OUT

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION_NAME                    VARCHAR2                IN

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                IN

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT
For more information on dbms_application_info, see the related links below:

DBMS_APPLICATION_INFO Package v$session_longops

DBMS_APPLICATION_INFO - Oracle shared pool high executions

For more dbms_application_info tips see:

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo,  Donald Burleson, and Steve Callan). 

Buy direct from the publisher and save 30%!




oracle dba poster

Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.