=================================================
Website      : http://www.arshltd.com
Author       : Arsh Ltd
Category     : Database Administration
Title        : SQL - setting your sql environment - login and glogin files
Description  : Discusses how to set your SQL environment
=================================================

Background: It turns out to be quite handy and safe to set your sql environment properly. This means setting your SQL prompt so that you can clearly distinguish between your critical database and your non-critical ones. It also saves you time doing all the settings e.g. setting page header, line sizes, serveroutput on or off etc.

Setting SQL prompt You can set your SQL prompt to show various things like current time, current user (with which you are connected to database), the global_name of the database etc. You can do this by issuing following command in SQL

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "

Here “_USER” is the current user name and “_CONNECT_IDENTIFIER _PRIVILEGE” is the connect string that you have used to connect.

This prompt setting turns out to be quite handy if your are doing some important activities since you will know where you are connected to and it will cut down on mistakes.

Note that in above you can also provide number of other built in keywords e.g. “_DATE” will give you current date.

Login.sql In order to save you time and effort each time when you connect to SQL session, you can enter your choices of SQL settings in a personal login file called login.sql. This file gets executed when you connect to sql session. You can create this file in $ORACLE_HOME/dbs. A sample such file is as follows -

column global_name new_value gname
set termout off
select 
  lower(user) || '@' ||
  decode(global_name,'oracledev.mydomain.com’, ‘development’, 
         ’oracleprod.mydomain.com’, ’production’, global_name) global_name 
from global_name;
set termout on
set sqlprompt '&gname> '

The above code in your $oracle_home/dbs/login.sql will result in the following prompt –

system@development>

As you can see it becomes very handy to have such a prompt and it clearly distinguishes between your development and production environment.

Glogin.sql There is another file called glogin.sql (also called global login) which also gets executed on connection to sql session. You can store your global settings here. The location this file can be found is $ORACLE_HOME/sqlplus/admin.

You can define your columns, your formats etc in case you use some queries which uses them or scripts in this file e.g.

COLUMN mycolheading FORMAT a10 HEADING mynewheading

You can also have your SQL session settings like –

set timing on
set linesize 170
set heading on
set termout on

Summary: As you can see from above discussion, that saving your most commonly used settings in these files saves you a lot of time and also is a safe way of connecting to SQL prompt as the prompt itself will remind you of the database you are connected to.