Config Oracle SqlPlus to have Linux user@db prompt

If you're working on multiple databases its handy to know from prompt which db your working on, saves you from having to continually have to do select name from v$database to check.

You can add USER@DB prompt to Oracle's SqlPlus client by editing

$ORACLE_HOME/sqlplus/admin/glogin.sql 

and adding the following entry

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "

While your there you might as well add following to set your default editor to be vi (or whichever you prefer)

DEF _EDITOR=vi

glogin.sql in $ORACLE_HOME/sqlplus/admin is obviously a global login set of sql commands which are executed for all users when SqlPlus is executed.

You can have personalised settings in a file users.sql, if this is in your home directory and you always run SqlPlus from there then it will find it and use its settings, otherwise you have to save it somewhere in your ${SQLPATH} env var.

There are 2 other var combos I know of that you can use for your SQL Prompt:

_PRIVILEGE (show if you are logged in as SYSDBA etc)
_DATE (Will display the current date/time depending on date format)

So following settings
ALTER SESSION SET NLS_DATE_FORMAT='HH24:MM';
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER_PRIVILEGE_DATE> "


will give you something like USER@DB AS SYSDBA 16:00> as your sql prompt.