Make your SQL Prompt Mean Something

Home

Toolkit:

• Index
• Scripts
• Quick Tips
• Articles / Publications
• Links

My OraFAQ Blog

Contact me

You can make your SQL prompt change to reflect the user/instance you logged in as, by adding a simple SQL script to your %ORACLE_HOME%\sqlplus\admin\glogin.sql file.

Here's an example. Add these lines to your glogin.sql, replacing my SIDs with the SIDs of your instances:

col gname new_value gname
col uname new_value uname
set termout off
set feedback off
select user uname from dual;
select decode ( substr(global_name,1,instr(global_name,'.')),
		'INST1.','ASP', -- a nickname for instance 1
		'INST3.','SAN', -- a nickname for instance 3
		'INST2.','IN2', -- a nickname for instance 2
		substr(global_name,1,instr(global_name,'.')-1) 
              ) gname from global_name;
set sqlprompt "&gname> "
/
set termout on
/
prompt *********************************************
prompt You are logged in as &uname on &gname
prompt *********************************************
/
set feedback on
/

---

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...