set up sqlplus environment

set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,
decode(dot,0,length(global_name),dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '
set termout on

if you have database with the same sid and global_name is not setup, you can include hostname in sqlplus prompt.


define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
--column global_name new_value gname
set termout off
define gname=idle
define hostname=idle
column global_name new_value gname
column host_name new_value hostname
select lower(user) || '@' || substr(global_name, 1,
  decode(dot,0, length(global_name), dot - 1) ) global_name
  from (select global_name, instr(global_name, '.') dot from global_name );
select  substr(host_name, 1,
  decode(dot,0, length(host_name), dot - 1) ) host_name
  from (select host_name, instr(host_name, '.') dot from v$instance );
set sqlprompt '&gname..&hostname> '
set termout on

above lines need be put into login.sql file
and the path to the login.sql file need be defined as SQLPATH environment