User ID/Database Name Prompt in SQL*Plus

When working with multiple database instances and logging on with several user ID's, it is very easy to get confused as to who and what is active in each of those SQL*Plus sessions that you have going at the current time.  To change the SQL prompt to show the user ID and database (instance) name, just put the following at the end of the $ORACLE_HOME/sqlplus/admin/glogin.sql file (by Dave Hill and Julie Dunnam; note that the prompt is only set at startup, and is not changed if you connect to another user ID during the SQL*Plus session):

set termout off
define new_prompt='nolog'
column new_prompt new_value new_prompt
select username || ': ' || substr(global_name,1,instr(global_name || '.','.')-1)
     new_prompt from user_users, global_name;
set sqlprompt "&new_prompt> "
set termout on

On Oracle8, some of our users were getting a buffer overflow on the above code, so, you might want to try the following instead:

set termout off
define new_prompt='nolog'
column new_prompt new_value new_prompt
select username || ': ' || substr(substr(global_name,1,30),1,
     instr(substr(global_name || '.',1,30),'.')-1) new_prompt from user_users, global_name;
set sqlprompt "&new_prompt> "
set termout on

Be aware that the prompt is only set at the startup of sqlplus, so, if you connect to another user ID, the prompt will still show the original login user ID.

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.