SQLPATH - variable for DBA

Configuring the SQLPATH will make make the job of running the".sql" files more easier.

SQLPATH is a variable which when set to a directory, The SQL*PLUS will search for the files with this directory and sub directory and runs the file.

Also we can set a file -> login.sql , it will be run by default every time you login through SQL*PLUS.
Below is a simple login.sql configuration.
login.sql


SET LINES 200
SET PAGES 10000
SET LONG 100000
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER>"
DEFINE _EDITOR=vi
COL OBJECT_NAME FOR A40

As a DBA we always do certain formatting before working on the SQL Session and if  this is set in login.sql then Oracle will take care of running it all the time we login. :-)

For this just set the following in your Linux/Unix profile ,

SQLPATH=/home/sri/sql:/home/sri/bin  ; export SQLPATH

For Windows configuration steps :

http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch2.htm#i1133354

The information is documented clearly in the following link,
http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch2.htm

after setting up the SQLPATH, now you can start making the sql scripts for the regular monitor queries like sga.sql , temp_usage.sql etc, in the SQLPATH directory and you can run it anytime in the SQL session by


SRI@sridb>!pwd
/home/sri

SRI@sridb>@sga.sql

SQLPATH will help a lot :-)