Change the SQL prompt

Edit Glogin.Sql to reflect which database you are in.

This Content Component encountered an error

Quite often I am working with a number of SQL*Plus windows open, and it becomes difficult to guess which session goes to which database. I have edited Glogin.Sql to reflect which database I am in. This edit puts the database into the SQL prompt. I now know which SQL*Plus session is pointing to what database!

--  Set up a process to change the SQL prompt to reflect the 
--  instance you are in. 
-- 
Column Global  New_Value  _GLobal  Noprint 

Break on Global 

SELECT ''''||REPLACE (GLOBAL_NAME, '.WORLD')||'> ''' GLOBAL 
FROM GLOBAL_NAME 
/ 

SET SQLPROMPT &_GLOBAL 

Reader Feedback

Marvin B. writes: "Great tip-I really like the concept. I found one quirk though: The field GLOBAL_NAME in table GLOBAL_NAME isn't correct in situations where a database has been cloned (copied) and the value for "db_name = SID" wasn't changed in "initSID.ora". An alternative I tried is to select INSTANCE_NAME from V$INSTANCE, and it seems to work."

Michael V. writes: "The problem with scripts like this, which are intended to go into a LOGIN.SQL or GLOGON.SQL, is that Oracle now has removed svrmgrl and wants us to use 'sqlplus /nolog'. Logging in with SQLPLUS /NOLOG results in a really unclear prompt."

Greg G. writes: "GLOGIN.SQL and LOGIN.SQL are only accessed when the SQL*Plus program is loaded. Beware the SQL prompt will NOT change if you DISCONNECT from your original database session and CONNECT to a different database."

Lokman M. writes: "I use sqlplus in scripts to startup the database (since svrmgrl is obsolete). If glogin.sql tries to query the database for global_name, the script will hang. "

Dave B. writes: "Users without select access to GLOBAL_NAME or v$database, etc. will also choke on sqlplus startup. My modified version includes database name and machine, since I work in multiple environments:

set time on
col user_id new_value user_id
col user_name new_value user_name
select user_id, lower(username) user_name from user_users where username=user;
col db_name new_value db_name
col db new_value db
select distinct(machine) db from v$session where type='BACKGROUND';
select name db_name from v$database;
set sqlprompt "&db_name@&db:SQL> "

I also put this into a seperate file which I can run manually so that sqlplus startup won't interfere with automated scripts."

Leon R. writes "Using this modified script would provide additional benefits, including user name and remove domain name:

set termout off 
col new_prompt new_value new_prompt_value 
SELECT user||':'||substr(global_name,1,  instr(global_name||'.','.',1)-1)
new_prompt 
FROM global_name ;
set sqlprompt '≠w_prompt_value> ' 
set termout on 

Laura M. writes: Unfortunately I have some experience with this tip. It sounds like a wonderful idea, one I jumped on immediately when I found it a while back. However with some testing I found it incomplete and dangerous. The reason is because the glogin.sql script only activates when you first enter a SQL*Plus session, so you then see the correct instance you are logged into. However if you issue a 'CONNECT .....' to attach to another instance within the session, GLOGIN.SQL is NOT run, so the prompt remains the same even though you are now pointing to a new instance. So, if you've lost track of where you are, you could be updating production data since your sqlprompt leads you to believe you are pointing to the test instance. I almost abandoned this feature, until I found out how I could create my own 'CONNECT' command which would initiate the GLOGIN.SQL. I did this by creating a CONNECT.SQL in oracle_homeBIN (since this is the default directory which the SQLPlus GUI uses) and oracle_homeDBS (which is the default directory which SQLPlus DOS uses) which contains the following:

CONNECT &1 
@%ORACLE_HOME%SQLPLUSADMINGLOGIN 

So when I want to issue my connect script I specify @CONNECT instead of CONNECT. Note, since I only allow one parameter to be passed into the script, I need to be careful how I format the command:

@CONNECT userid@sid 
or 
@CONNECT userid/pswd@sid 
or 
@CONNECT 'userid/pswd@sid AS SYSDBA' 

Hope this proves useful to others. I am ever hopeful that Oracle will fill the gap themselves. What a useful feature it can be!

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in January 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close