Customizing SQL*Plus with login.sql or glogin.sql

When you login into SQL*Plus there are two configuration files that are executed. These files allow you an opportunity to customize your SQL*Plus environment and are named glogin.sql and login.sql. They are invoked when SQL*Plus starts or when the CONNECT command is used.

–glogin.sql file is normally located in the $ORACLE_HOME/sqlplus/admin directory. I believe the login.sql file is optional.

*It is important to note that settings from the login.sql take precedence over settings from glogin.sql

These files are normally addressed by the client based on a couple of factors:

  1. A “Start in” directory specified in a shortcut
  2. A directory the SQLPATH environment variable specifies.

I don’t personally set the SQLPATH environment variable myself, but I do setup a shortcut that specifies a “Start in” directory.

sqlplus_shortcut

Here are the contents of my login.sql file

-- Custom prompt stuff
SET LINES 120
SET HEAD OFF;
select 'YOU ARE CONNECTED TO MACHINE: '||HOST_NAME||' INSTANCE NAME: '|| INSTANCE_NAME FROM GV$INSTANCE;
select 'YOU ARE CONNECTED TO THE DATABASE AS: ' || UPPER(sys_context('USERENV','SESSION_USER')) 
        ||chr(10)
        || '  AND YOU ARE CONNECTED TO THE OS AS: ' || UPPER(sys_context('USERENV','OS_USER'))
from dual;

set termout off
column sysid new_value _sysid
column userid new_value _userid
column instid new_value _instid
break on sysid
break on userid
select UPPER(HOST_NAME) sysid from V$INSTANCE; 
select UPPER(INSTANCE_NAME) instid FROM V$INSTANCE;
select UPPER(sys_context('USERENV','SESSION_USER'))  userid from dual; 
clear breaks
set termout on
set sqlprompt '&_instid:&_userid>' ;

SET HEAD ON;

The final result provides some nice to know information when logging in. The output displays all instances of the cluster by utilizing the GV$INSTANCE view as well as the database user and OS user. Also notice the prompt provides the instance currently connected to and database user id in the form of INSTANCE:USER>
sqlplus

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s