Database Login Information In Your Sqlplus Prompt

Have you ever run the wrong script in SQL*Plus in the wrong database?

It is common to have many databases in PeopleSoft development environments, and developers must often connect to different databases. To help reduce the chance of accidentally working in the wrong environment I have my login script on SQL*Plus that puts information about my session in the command prompt. When you connect to a database SQL*Plus automatically runs %ORACLE_HOME%/sqlplus/admin/glogin.sql. I have put my own login script gfclogin.sql into the same directory. It is called from glogin.sql like this:

@@gfclogin.sql

The double @ means that the called script is in the same directory as the calling script. gfclogin.sql runs a number of SQL queries and stores the output in the SQLPROMPT. Note that from SQL*Plus version 10, the login script is run on every connections, previously it was only run for the initial connection. This has one negative side effect. It will overwrite and SQL statement in the SQL*Plus command buffer when you reconnect (from SQL*Plus 10)

The some of the queries in the script only work if the connecting user has SELECT_CATALOG_ROLE (or individual privilege to query v$session, v$process, v$database. If the user doesn't have these privileges the prompt will just contain the user name

SCOTT>

On a PeopleSoft database it will also report the name of the PeopleSoft database from PS.PSDBOWNER, thus:

SYSADM-HCM89>

If the user has SELECT_CATALOG_ROLE it will also show the session ID, session serial number, database name, name of OS user running the database, and the node name of the database server.

SCOTT.147:4264.GOFASTER.SYSTEM.GO-FASTER-4>

or

SYSADM-HCM89.148:5420.HCM89.SYSTEM.GO-FASTER-4>