Friday, July 07, 2017

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'


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:


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';


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.


Unknown said...

sex education ss3

2 cup method said...

Thanks for sharing great content

Unknown said...

Articles that have significant and savvy remarks are more agreeable, at any rate to me. It’s fascinating to peruse what other individuals thought and how it identifies with them or their customers, as their point of view could help you later on.
Thanks for sharing with us..
paybyplate ma pay online

Unknown said...

Great article

Zonahobisaya said...

laman web yang baik : Zonahobisaya
laman web yang baik : Logo
laman web yang baik : Zonahobisaya
laman web yang baik : Terdalam
laman web yang baik : Sinopsis Film
laman web yang baik : Resep Masakan
laman web yang baik : Zonahobisaya
laman web yang baik : Zonahobisaya

JacobHarman said...

A metaverse engineer abilities would ordinarily remember insight and skill for the accompanying innovations: Computer generated Reality (VR) and Expanded Reality (AR) improvement: Oculus, Vive, and Hololens, and advancement systems like Solidarity and Unbelievable Motor. 3D displaying and movement: Autodesk Maya, Blender, and Houdini for making 3D models, activitys and enhanced visualizations. Systems administration and distributed computing: WebRTC, WebSockets, and Amazon Web Administrations, Microsoft Sky blue or Google Cloud Stage to assemble and scale the metaverse. Computerized reasoning (simulated intelligence) and AI (ML): TensorFlow, PyTorch, and OpenAI to make shrewd virtual characters, normal language handling, and direction>> metaverse software development services

jasika said...

When you are writing correct sentences. So sometimes the first draft doesn't turn out quite right. If you want to fix your run-on sentences. So finding them yourself can be difficult. There are many online tools out there. But this isn't always the most reliable option. Because they can easily autocorrect run-on sentences writing correct sentences Changes have been made without alerting you. With our run on sentence corrector tool, you can quickly and easily check any run on sentences in your text by highlighting them with our detection engine and double checking any necessary corrections!

Implement Blockchain for Secure Transportation Transactions said...

This article provides a comprehensive guide on how to implement blockchain technology for secure transportation transactions. The detailed explanations and practical tips make it an invaluable resource for anyone looking to enhance the security and efficiency of their transportation operations. By outlining the benefits of blockchain in ensuring transparency and trust in transactions, this article empowers businesses to leverage this innovative technology for a more secure and reliable transportation system.