Prompt i SQL*Plus

Are you also using SQL*Plus when you work with Oracle? Well, I may have good news for you!

The first thing I change when working in a new database environment is, of course, the SQL-prompt. The prompt could for instance show you which user you are logged in as and which instance in the prompt itself.

The variable sqlprompt could be defined either in the file glogin in the directory $ORACLE_HOME/sqlplus/admin or in local file login.sql. You could also change the same files on the client you are working from.

SQL-prompt in Oracle10g and Oracle11g

 SET sqlprompt “_USER’@'_CONNECT_IDENTIFIER SQL> “

And the result is:

 SYS@DW SQL> 

You may read more about this in the Oracle documentation, Oracle10g and Oracle11g.

SQL-prompt in Oracle9i

This method is not working for Oracle9i, it is a bit more complicated. One solution may look like this:

-- Defined by Ingemar J. Haverstad
SET TERMOUT OFF

DEFINE gname=idle

COLUMN global_name new_value gname

SELECT LOWER(user) || '@' ||
  SUBSTR(global_name, 1,
                decode(dot, 0, LENGTH(global_name), dot-1)) global_name
  FROM (SELECT global_name, INSTR(global_name, '.') dot
FROM global_name);

SET sqlprompt '&gname SQL> '

SET TERMOUT ON

SQL-prompt in Data Guard

This leads us to a variation I’m often using with Data Guard. The challenge in this case is to know if you are working in the primary or standby database. Following lines makes it easier:

 -- Defined by Ingemar J. Haverstad
SET TERMOUT OFF

DEFINE g_name=idle

COLUMN global_name new_value g_name

SELECT '[' || database_role || '] ' ||
  LOWER(user) || '@' ||
  db_unique_name global_name
FROM v$database;

SET sqlprompt '&g_name SQL> '

SET TERMOUT ON

The result will look like this while connected to the primary database:

 [PRIMARY] sys@CHICAGO SQL>

And, in the standby database:

 [PHYSICAL STANDBY] sys@BOSTON SQL>

Nice, isn’t it!

SQL-prompt in a Real Application Cluster (RAC)

Something similar could also be used in Real Application Cluster environments. It is obviously good to know which instance you are logged on to, at least as a database administrator. I will show this with an example:

 -- Defined by Ingemar J. Haverstad
SET TERMOUT OFF

DEFINE g_name=idle

COLUMN global_name new_value g_name

SELECT
  LOWER(user) || '@' ||
  instance_name global_name
FROM v$instance;

SET sqlprompt '&g_name SQL> '

SET TERMOUT ON

The result should look like this when the system user is logged on to the first instance in the cluster:

 system@PROD1 SQL>

And, of course, like this on the second instance:

 system@PROD2 SQL>

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*