Setting SQL*Plus environment at startup

There are two files that we can update for automatic configuration of SQL*Plus on startup, so we have the same settings every time we connect with SQL*Plus. These scripts are executed each time we start SQL*Plus.

• glogin.sql - for Site Wide settings, will be run on successful Oracle DB connection.
•  login.sql - optional, for user specific settings, if available will run immediately after glogin.sql.

The "glogin.sql" file will be located at ORACLE_HOME\sqlplus\admin\glogin.sql.

For "login.sql" file, SQL*Plus searches in your current working directory, and then the directories specified in the SQLPATH environment variable. Note that SQLPATH is an Environment variable or Windows registry entry to specify the location of SQL scripts.

The glogin.sql file in my environment will have the following entries in the end:

-- SQL*Plus Settings done manually
-- The default editor SQL*Plus should use when you type ed in sql prompt
define _editor = "C:\Program Files (x86)\TextPad 5\TextPad.exe"

-- Display of date format
alter session set nls_date_format = 'dd/mm/yy hh24:mi:ss';

-- Whether to display the output when we run a script, enables DBMS_OUTPUT and sets buffer size
set serveroutput on size unlimited

-- The number characters displayed on one line
set linesize 80

-- Sets the number of rows on each page of output before the headings are printed
set pagesize 9999

-- Displays time taken for each SQL or PL/SQL block executed
set timing on

-- Sets the sql prompt to include the user and connect identifier
set sqlprompt "_user'@'_connect_identifier>"
-- End glogin.sql

We can see all the setting with the below command:

 SYSTEM@ora10g>show all  

And if we want to see specific setting e.g. pagesize:

 SYSTEM@ora10g>show pagesize