Sql*Plus – Scripts and SQLPATH

As oracle DBA’s I find that most of us are command line sadists more than GUI folk. Therefore we write and run lots of scripts and if we can store those scripts in a common location our lives are easier. Here is my method.

  • Create a Common Directory
  • Set the SQLPATH Environment Variable

Create a Common Directory

I run my databases on Linux and NetApp filers so I create a volume on the NetApp filer named /dba. This way I can mount /dba on each of my Linux servers creating a common mount point available to all servers. Then what I do is create a series of directories for various purposes. The purpose of this post is for a scripts directory. So, on all of my Linux servers I now have access to:

/dba/scripts

For windows you may want to mount a drive such as

m: which has the directory /dba/scripts

Set the SQLPATH Environment Variable

When running scripts in Sql*Plus it is easiest if I can just type in the name of the script (without the full path and filename). The SQLPATH environment variable is how this can be accomplished. To use the directory I mentioned above the SQLPATH is set like this.

export SQLPATH=/dba/scripts

If you are using windows you will do something like this.

set SQLPATH=m:\dba\scripts
(TIP: The same directory can be utilized by both Linux and Windows machines)

On Linux I put the export SQLPATH=/dba/scripts in my .bashrc file so it is set each time I start a shell.

As I put new posts on this blog about the scripts I use in my daily life as a DBA they will all be stored in this directory if they are SQL scripts. For shell scripts I use a different directory.>

Post a Comment