Change login prompt at SQL*Plus
                                                 Last update (2008-02-17 21:36:19)
                                                                                                                   Date added (2008-02-17 21:15:18)

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> '
However, this is only run ONCE (when you login). As from Oracle10g, the login.sql and glogin.sql scripts are automatically executed by SQL*Plus every time you issue the connect command.