Change login prompt at SQL*Plus

Summary 

Every time you start SQL*Plus, Oracle searches for a file called "login.sql" anywhere along the path as defined by the ORACLE_PATH variable, and if not found, the glogin.sql file (the "global login file" which applies to all users by default). This is very useful because if you have dozens of database to monitor you know every time "where you are" 

So create a login.sql file at $ORACLE_HOME/bin directory with the following entries to sets the prompt to user@database

column global_name new_value gname
set termout off
set timing on
set pages 200
set linesize 200
set autotrace on explain statistics
select lower(user) || '@' ||
case when instr(global_name,'.')>1 
     then substr(global_name,1,instr(global_name,'.')-1) 
     else global_name end
global_name from global_name;
set termout on
set sqlprompt '&gname> '