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:
- A “Start in” directory specified in a shortcut
- 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.
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>