Global Login Script - glogin.sql

When you start SQL*Plus, it runs the glogin.sql site profile setup file and then runs the login.sql user profile setup file.

Using the Site Profile File

The global site profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql. If a site profile already exists at this location, then it is overwritten when you install SQL*Plus. If SQL*Plus is removed, then the site profile file is also removed.

Using the User Profile File

The user profile file is login.sql. SQL*Plus looks for this file in the current directory, and then in the directories specified by the SQLPATH environment variable. The value of this environment variable is a colon-separated list of directories. SQL*Plus searches these directories for the login.sql file in the order that they are listed in the SQLPATH environment variable.

The options set in the login.sql file override those set in the glogin.sql file.

My glogin.sql is as following

-- Defaults for SET AUTOTRACE EXPLAIN report, -- (from Effective Oracle by Design)
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44

--define _editor=notepad
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
column global_name new_value gname

select lower(user) || '@' || substr(global_name, 1,
decode( dot, 0,
length(global_name), dot-1)
) || '->'|| host_name global_name
from (select global_name, instr(global_name,'.') dot from global_name)
,v$instance;

set sqlprompt '&gname> '
set termout on

1. glogin.sql is in $ORACLE_HOME/sqlplus/admin

2. Add these lines into glogin.sql (for Linux or Unix OS you can use vi as the editor, for windows you can use notepad)

-- Defaults for SET AUTOTRACE EXPLAIN report, -- (from Effective Oracle by Design)
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44

--define _editor=notepad
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
column global_name new_value gname

select lower(user) || '@' || substr(global_name, 1,
decode( dot, 0,
length(global_name), dot-1)
) || '->'|| host_name global_name
from (select global_name, instr(global_name,'.') dot from global_name)
,v$instance;

set sqlprompt '&gname> '
set termout on

3. The result

sys@EDBA01->e-dba01>

sys = login user
EDBA01 = global name
e-dba01 = server hostname

To view all SQL environment setting you can use

SHOW ALL command