SQLPLUS set appinfo option to see what script is being run



Sometimes it's very useful to know which particular script is being run by a sqlplus session (perhaps if you have a batch that consists of a whole series of sql scripts, or trying to find out where the slow script is originating from).

This is simple to enable via a set command in the sqlplus session but is not on by default. The command is

 set appinfo on 

which essentially just causes sqlplus to make DBMS_APPLICATION_INFO calls each time it executes a new script.

Lets do a quick demo and create a simple test script and run in session '1':

cat test.sql # show content of script which is just 'set pause on'
set pause on



SYS>@test -- now run it and session pauses but does nothing


Here we just pause the session and do nothing, we then connect session '2' just to see that nothing is present in v$session and indeed we just see 'sqlplus':

SYS@>select module ,program from v$session where program like 'sqlplus%'
  2  /
sqlplus@(TNS V1-V3)                                      sqlplus@server (TNS V1-V3)


If we now do the test again but add in

set appinfo on before running test.sql in session '1' we now see in session '2'

01@ test.sql                                                     sqlplus@server (TNS V1-V3)

Which gives us the script name (test.sql) and the 'depth' (01).

The depth just refers to the fact if this script has been called from another script - for example the 2 sql files shown below

cat test.sql
@test2.sql
 

cat test2.sql
set pause on


Will display

02@ test2.sql

in the module column reflecting the fact it was called from another sqlplus script and is at depth '2'

I can see this being very useful in tracking code deployments and batch schedules for DBA's to quickly go to the problem script



2 comments:

  1. Maybe it is a silly question, but is it possible to execute SET APPINFO inside a PL/SQL script, like 'EXECUTE IMMEDIATE' ?

    ReplyDelete
  2. Hi,
    It's a sqplus command so can;t be done in this way in PL/SQL.
    However you can use the DBMS_APPLICATION_INFO package to set information about what is happening - though it wont be automatic - you'll have to make a call each time to set whatever information you want to show.

    For an example of that see here: https://oracle-base.com/articles/8i/dbms_application_info


    Cheers,
    Rich

    ReplyDelete