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