Setting APPINFO in SQL*Plus

Ever used the MODULE column of V$SESSION view? If you haven't, you are missing out on a very important piece of instruemntation code built right into the Oracle database session management. This allows you to assign a completely arbitary name, as you would see will properly describe for your application. Later when you want to identify that session, you can check the MODULE column in V$SESSION. Even though the userid is the same for all these sessions; the module column will help you identify the session.

Let's see how it works by a little example. Here is how you check the module information.

select module
from v$session
where username = 'SYS'

MODULE
-----------------------------
sqlplus.exe
sqlplus.exe

In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:

SQL> exec dbms_application_info.set_module('MyModule','MyAction')

PL/SQL procedure successfully completed.

Now if you check the module:

MODULE
---------------
MyModule
sqlplus.exe

As you can see the session where executed the packaged procedure has the descriptive module name. If you want to enable tracing, check the sessions stats, debug what's going on, etc., the ability to identify the session uniquely and accurately could be lifesaver.

But we had to execute the package dbms_application_info. The principles of least privileges says that we don't want to grant more privileges than absolutely necessary. While no one will argue against this being convenient, it will probably be hard to justify as "absolutely" necessary. So, what could you do to identify the session via the module? The generic module name "sqlplus.exe" is pretty much useless.

There is a much simpler solution. The SQL*Plus parameter appinfo comes to rescue. By default the setting is off. You can confirm that by issuing the following:

SQL> show appinfo
appinfo is OFF and set to "SQL*Plus"

To set to a value you want, use the follwoing:

SQL> set appinfo MyApp

Now if you check the sessions from another sessions:

SQL> select module
2 from v$session
3 where username = 'SYS';

MODULE
-------------------------------
MyApp
sqlplus.exe

See how the module is set to MyApp, which allows you to locate the session from many from the same user. And you could do that without calling the package.

What are the practical implications? The best usecase, I think is using this in the automatic login scripts such as glogin.sql. Put the following line in gloin.sql in $ORACLE_HOME/sqlplus/admin directory.

set appinfo "AcmeModule"

Now any session using that Oracle Home will have the Module column set to AcmeModule.

However you can also create local files called login.sql in individual directories and set appinfo apprpriately. For instance, suppose you have three major directories where you generally run the SQL scripts from. Create login.sql files on each directory. Directory "C:\App1" has a login.sql file with the following content:

set appinfo "App1"

Create login.sql files in the other directories with the appropriate contents. Now when someone connects to the database from SQL*Plus from those directories, the module will be set appropriately.
You will be able to know what directory the user has been in calling the scripts.

A caveat on the above, though. In Oracle 12.2, login.sql file is not executed, if a glogin.sql file is present. It is silently ignored. To make sure the local login.sql file is read, you have to explcitly set the variable ORACLE_PATH or SQLPATH to that directory.

Hope you find use of this little known setting in SQL*Plus.