Using GLOGIN.SQL

Many a time, DBAs and Developers tend to run scripts, accidentally, in instances that they do not actually intend to. In extreme cases, this might result in disastrous consequences, sometimes even leading to recovering/restoring the database from a backup.

This mistake can easily be avoided if the sql prompt displays the username and/or the SID in lieu of just "SQL>". This can be achieved using a variety of methods.

The easiest method (according to me) is detailed below.

Whenever sqlplus is invoked, 2 files are executed: glogin.sql and login.sql (in the order mentioned). These 2 files are located under $ORACLE_HOME/sqlplus/admin.

Modify the file glogin.sql and add the below line:

set sqlprompt "_user'@'_connect_identifier > "

The net result of this is that the sqlprompt will appear as below whenever you login to sqlplus:

system@ORCL>

where system is the username and ORCL is the SID.

Note: One drawback of this method, if it may be called so, is that whenever one logs in to sqlplus using /nolog option, only the '@' character is displayed as the connection to the database is yet to be established. As soon as the connection is established, the username and SID are again populated in the sqlprompt. Incidentally, this is a new feature in 10g, wherein the glogin.sql and login.sql files are executed after the connect command.