Enhancing SQL*Plus   Donald K. Burleson

In complex Oracle environments it is not uncommon for the Oracle professional to get confused about their “current” Oracle session as defined by the $ORACLE_SID environment variable.  This can cause serious problems when commands are accidentally entered into the wrong database!

The solution to this problem is to enhance the standard SQL*Plus prompt of “SQL>” to include the database name.

To change the SQL prompt to show the user ID and database (instance) name, Oracle provides a special SQL*Plus file called glogin.sql. By changing the directives of the glogin.sql file in the $ORACLE_HOME/sqlplus/admin directory, you can enhance the SQL*Plus prompt to include the database name.

set termout off
define new_prompt='nolog'
column value new_value new_prompt

select

   username ||

   ': ' ||

   substr(substr(global_name,1,30),1,
     instr(substr(global_name,1,30),'.')-1)

   value

from

   user_users,

   global_name;

set sqlprompt "&new_prompt> "
set termout on

This will change your prompt as follows:

set ORACLE_SD=PROD;

export ORACLE_SID

sqlplus system/manager

PROD> select name from v$database;

PROD

1 Row selected.

PROD>

Note that this prompt is set at entry to SQL*Plus and it will not change if you connect to another database using the SQL*Plus connect statement. The following example clarifies this limitation of the SQL*Plus prompt.

set ORACLE_SD=PROD;

export ORACLE_SID

sqlplus system/manager

PROD> select name from v$database;

PROD

1 Row selected.

PROD> connect system/manager@TEST

Connected.

PROD> select name from v$database;

TEST

1 Row selected.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm