Set SQL*Plus Prompt

Since most DBAs will use SQL*Plus more than any tools else, it will be convenience if you can set your SQL*Plus prompt to show your current login user and SID instead of this "SQL>". Especially if you have mulitple instances running under same Oracle installation.

To do this, you need to include following in your glogin.sql

set term off
define sql_prompt=idle
column user_sid new_value sql_prompt
select lower(user) '@' '&_CONNECT_IDENTIFIER' user_sid from dual;
set sqlprompt '&sql_prompt> '
set term on

When you login you will see this,

sqlplus scott/tiger
scott@dev

If you are on 10g, it becomes much easier by only this,

set sqlprompt '_user@&_connect_identifier>'