Wednesday, November 24, 2010

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 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.

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

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

No comments:

Post a Comment