Customize SQL*Plus

SQL*Plus is one the most commonly used tool to access Oracle databases. Although I do use Oracle Grid/Database Controls and few other tools extensively, SQL*Plus is the tool of choice on the servers to perform variety of database administration activities such as ad-hoc queries, monitoring, upgrades, database maintenance, user management, space management, etc. etc. etc. Given its wide-spread usage on day-to-day basis, I have customized SQL*Plus to improve and enhance my user experience!

In this blog, I will show you few productivity boosting tips on how to customize SQL*Plus. Its quite easy. These tips are quite useful to me, and I believe will be useful to you as wll.

SQLPATH Variable

SQLPATH environment variable is equivalent to PATH variable. What PATH variable is to executables, SQLPATH is to SQL scripts. As a DBA, I have my own repository of scripts that I execute quite often, and I am sure you do as well. Simply add your script folders to SQLPATH. SQL*Plus searches for SQL scripts in all directories specified by SQLPATH variable. Quite useful. You don't need to specify the full path of the script. Or you don't need to change directory to the folder where your scripts are stored.

On Windows platform, you also have a choice to add SQLPATH registry variable. Use either environment variable or registry variable.

Login.sql

LOGIN.SQL is equivalent to AUTOEXEC.BAT on Windows or .profile on Unix platforms. SQL*Plus executes login.sql at the startup. At startup, SQL*Plus starts a search for login.sql in your current directory and then it moves on to directories specified by SQLPATH variable. Create login.sql and include it in a directory that is pointed to by SQLPATH variable. You can add any SQL*Plus commands that you like. Another useful customization. The following is my truncated login.sql file.

set termout off

-- formatiing

set pagesize 1000

set linesize 132

alter session set nls_date_foramt = 'MM/DD/YYYY:HH24:MI:SS' ;

-- SQL Prompt

column new_dbid new_value old_dbid noprint

column usr new_value ousr noprint

select upper (substr (global_name, 1, (instr (global_name, '.') -1))) new_dbid

from global_name ;

select user usr from dual ;

set sqlprompt '&ousr &old_dbid> '

column new_dbid print

column usr print

-- Automatic Spool

column tstamp new_value otstamp noprint

select to_char(sysdate,'MMDDYYYYHHMISS') tstamp from dual ;

spool 'E:\MyDoc\Spool\&ousr&otstamp'

column tstamp print

set termout on

Please be careful if you have scheduled unattended batch jobs using SQL*Plus. If database is not up and running, login.sql script will prompt for values for Username and Global Name variables used in the above script, and wait forever.

SQL Prompt

Another useful customization is to customize the SQL*Plus prompt. Login.sql displayed above sets up SQL prompt that comprises of Username and a Database Name.

Automatic Spool File

This customization, included as part of above mentioned login.sql, has been quite useful to me on number of occasions. It automatically creates a spool file for every SQL*Plus session at startup. Your SQL*Plus session history is saved in the spool files that you can reference anytime. Quite useful when you need to reconstruct what happened during that late night session!

Spool filename comprises of username and timestamp. Spool file is created in the folder specified as part of spool command in the above mentioned SQL*Plus.

Update Start in Property on MS Windows

Update Start-in property on Windows so all your spool files will be saved into this folder.

If you have recently installed Oracle11g R2 and are getting the following errors, then you need to update Start in SQL*Plus properties.

JMEHTA> ed
SP2-0110: Cannot create save file "afiedt.buf"

 JMEHTA> save temp.lst
SP2-0110: Cannot create save file "temp.lst"