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:
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:
And, of course, like this on the second instance: