Change SQL prompt oracle SQL*Plus (pre 10g)

Here we discussed how to change prompt for release 10g and up. Now we will discuss how to achieve same in pre 10g releases

Enter following commands in glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory or execute them one by one at SQL Prompt.

col username new_value username
col dbname new_value dbname
set termout off
SELECT lower(user) username,
       substr(global_name, 1, instr(global_name, '.')-1) dbname
FROM   global_name
/
set termout on
set sqlprompt '&&username@&&dbname> '

Change SQL Prompt in oracle SQL*Plus (10g and up)

Below is an example to change the SQL*Plus prompt (10g and up), simple and yet very useful.
SET SQLPROMPT command is used to change the default SQL> prompt

1) Display username

set sqlprompt '_user>' 

2) Display database name

set sqlprompt '_connect_identifier_privilege>' 

Step 2 and 3 can be combined together to display username and database name together.
3) Display username and database name (e.g. apps@dbname> )

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"

4) To set the time at sqlprompt

set time on 

Now the best part is to avoid typing this command everytime you open a new SQL*Plus session, edit glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory as follows.

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"
set time on

Related Post: For Pre 10g Releases

Display line number in VI editor in Unix

Following command can be used to display line numbers

:set number 

Following command can be used to hide the line numbers

:set nonumber