Simon Krenger

In the last weeks, I had a few projects involving Oracle Advanced Replication and immediately stumbled upon the problem of not knowing which user and which instance was on which prompt. Instead of constantly issuing “SHOW USER” and “SELECT * FROM global_name“, I decided to add a few lines to my glogin.sql.

So before investing precious time, I fired up Google and found that other people also had the idea of changing the SQL*Plus prompt. I found the template for my own glogin.sql here and modified it so it fits my needs.

Note that my script does not display the GLOBAL_NAME of the databse, but only the INSTANCE_NAME read from v$instance. This means the prompt is usually a lot shorter:

repadmin@kdb3:SQL>

To change the prompt, append the following lines to your $ORACLE_HOME/sqlplus/admin/glogin.sql:

-- Nicer SQL*Plus prompt
set linesize 160
set pagesize 1000

set term off
set timing on
set feedback on
set echo on

set sqlprompt "_user'@'_connect_identifier:SQL> "
set term on