Site icon Radu Pârvu

Unlock Oracle’s DBMS_APPLICATION_INFO: A Guide

Radu PARVU

Introduction Words

In this new web note, I will continue with another great Oracle feature that is relatively unknown and too often ignored: DBMS_APPLICATION_INFO

The target audience is formed of people in teams running complex PL/SQL code—batch jobs, APIs, ETL pipelines.

Sometimes, it’s easy to lose visibility into what exactly is happening inside the database. That’s where DBMS_APPLICATION_INFO becomes extremely valuable.

Think of it as lightweight observability built directly into Oracle.


This package allows you to instrument your code so that database sessions expose meaningful runtime information (module, action, client info). This data is typically visible in performance views like V$SESSION, V$SQLAREA, and V$ACTIVE_SESSION_HISTORY.

What is DBMS_APPLICATION_INFO ?

DBMS_APPLICATION_INFO is a built-in PL/SQL package used to:

This helps oracle DBAs and developers to debug and troubleshoot when they need to answer questions like:

Core Concepts

MODULE

Represents the high-level component:

ACTION

Represents the specific step inside the module:

CLIENT_INFO

Free-text field for extra context

Basic Usage

in Application we should be Setting module and action:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'ORDER_PROCESSING',
    action_name => 'VALIDATION'
  );
END;
/
Updating only the action

Based on the designed application flow, we can also update only the action:

BEGIN
  DBMS_APPLICATION_INFO.SET_ACTION('INSERT_ORDER');
  END;
  /
Clearing values

Sometime, it is necessary to clear the values:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;
  /

Real-world Example: Sample Order Processing Procedure

The best usage would be to insert calls to DBMS_APPLICATION_INFO by setting the module and ACTION in the application code, for example calls like: 

...
DBMS_APPLICATION_INFO.SET_MODULE('ORDER_PROCESSING', 'START');
  -- Example Step 1: Validation
  DBMS_APPLICATION_INFO.SET_ACTION('VALIDATE_ORDER');
  validate_order(p_order_id);
  -- Example Step 2: Pricing
  DBMS_APPLICATION_INFO.SET_ACTION('CALCULATE_PRICE');
  calculate_price(p_order_id);
...

And so on .. you get the idea: we instrument the code by setting those values according to the logic flow!

Why this approach is important

After you set the values, at any point in time, you can query:

SELECT module, action
FROM v$session
WHERE sid = SYS_CONTEXT('USERENV', 'SID');
-- And see something like:
MODULE              ACTION
------------------  -------------------
ORDER_PROCESSING    PROCESS_PAYMENT

This is extremely useful in debugging and production monitoring.

Using CLIENT_INFO

You can set the client_ info values 

BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('ORDER_ID=1001;USER_ID=42');
END;
/

and find back those in the data dictionary, which is extremely useful for troubleshooting purposes!

SELECT client_info
FROM v$session
WHERE sid = SYS_CONTEXT('USERENV', 'SID');

Here is my lab environment example:

Future article: Tracking Long Operations

I have found the usage of the DBMS_APPLICATION_INFO package as extremely useful when troubleshooting the tracking and progress of long operations, this would be good example for a future post, if you are interested, please drop me a line at: database@blog.parvu.org

Advanced Pattern: Wrapper Procedure

You can centralize instrumentation:

create or replace PROCEDURE set_step(p_module VARCHAR2, p_action VARCHAR2) IS
  BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(p_module, p_action);
  END;
  /

Then:

BEGIN
  set_step('ADVANCED_EXAMPLE', 'BLOG_DEMO');
  END;

Integration with Monitoring Tools

DBMS_APPLICATION_INFO integrates naturally with Oracle monitoring tools and environments. This would also be a good topic for a future article – please let me know!

In past, due to the seamless integration, we managed to complete important actions during performance troubleshooting exercises, like

Best Practices

1.Always set MODULE at entry points:– API procedures
– batch jobs
– scheduler jobs
2.Avoid vague names like:– STEP1, PROCESS
– instead, use: VALIDATE_INPUT, LOAD_CUSTOMERS
3.Keep names consistentUse the application defined naming conventions:
MODULE = system/component
ACTION = verb + object
4.Reset valuesAlways clear at the end:

DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
5.Don’t overuse updatesUpdating action in tight loops can hurt performance!
Common Mistakes

Not setting module/action at all → You lose observability!

Setting once and never updating You only see “START” forever!

Using meaningless labels → E.g., “PROCESSING” tells you nothing!

Forgetting to clear values → Misleading session data!

Performance Impact statement

The usage of this package has very low overhead when used properly

Please note:

Conclusion & Final Thoughts

DBMS_APPLICATION_INFO is one of the simplest yet most underused tools in Oracle development!.

…it gives you instant visibility into runtime behavior without needing external logging systems.

Used correctly, it can drastically reduce debugging time and improve production monitoring clarity.

I find it especially you’re building:

If you troubleshoot system running cases as those and find out that the code is not instrumented, you should ask yourself or the architect team, why that is not the case!

As always, I will be more than happy to continue a conversationon this topic based on your real questions! Please leave a comment or email me: database@blog.parvu.org

Exit mobile version