Oracle: Environmental Variables and Scripting

(For more resources on Oracle, see here.)

Unix scripting will be one of the most powerful tools in your arsenal and only with constant use will you become proficient. Your standards for scripting need to produce code that is robust, modular, consistent, and scalable. This article won't cover everything you need to know about scripting. There are many excellent lessons available from your operating system vendor. See the following documents for more information specific to how Oracle products behave in a Unix environment:

  • Note: 131207.1 Subject: How to Set Unix Environment Variables
  • Note: 1011994.6 Subject: How do Applications Act in Different Unix Shells.
  • How to Integrate the Shell, SQL*Plus Scripts and PL/SQL in any Permutation? [ID 400195.1] (Excellent document)

It is suggested that the reader should read the Oracle Database Concepts 11g Release 2 (11.2) of the documentation, the book every DBA should start with.

Usually cron is used for scheduling in Unix and the AT command with Windows.

For Oracle utilities that run outside the database (or which must only run when the database is down), Unix shell scripting is best used. A well-written script would parse /etc/oratab for a list of ORACLE_SIDs on a single server for tasks such as parsing log files, consistent backups, exports, dbverify, and RMAN. If you have a solitary database, then DBMS_SCHEDULER can be used with a combination of SQL and PL/SQL integrated with OS commands.

Occasionally, DBAs rename the oraenv located in $ORACLE_HOME/bin when they have added custom code to the /usr/local/bin/oraenv (default location on Linux), so that they can make sure they know exactly which one is executed at run time. If you have any problems related to running a modified oraenv file, check which one is being executed and adjust the $PATH variable as needed.

The following is a list of some of the Oracle-provided Unix commands with a quick synopsis of their purpose. Most of them are located in $ORACLE_HOME/bin:

  • wrap—encrypts stored procedures for advanced use
  • oerr—displays oracle errors. Syntax: oerr ora 12154
  • Sysresv—instance and shared memory segments
  • Tkprof—formats output trace file into readable format
  • Dbshut—shell script to shut down all instances
  • dbstart—shell script to start up all instances at boot
  • Dbhome—sets ORACLE_HOME
  • Oraenv—sets environmental variables for ORACLE_SID
  • trcasst—trace assistant

Guidelines for scripting

These are general suggestions for someone with some experience with a Unix operating system. You will need more background information to understand this section. The best sources of information will be the software vendor of your operating system, because there are small differences between the different versions and flavors of Unix. As with any code, you must test on a non-production system first, as inexperience may lead to unexpected results.

Separating the configuration file

Use the OS-provided default profile for environmental variables, but use a separate configuration file to set the $ORACLE_SID variable. There are several key environmental variables that are commonly used with Oracle software, all of which are found in the documentation specific to the operating system. Optimal Flexible Architecture (OFA) mentions setting the $ORACLE_SID in the profile, but if you have more than one database or Oracle product installed on a system, it is best done interactively. A default profile is loaded when you first log in to Unix. So if the $ORACLE_SID is loaded when you log on, what happens when you want to change ORACLE_SID(s)? This is when the environment becomes mixed. It just keeps appending the $PATH variable each time you execute the oraenv script. Which set of executables will you find first? It will be those executables for which you ran oraenv the first time.

At this point I wanted to execute SQL*Plus out of the 11g directory, but was able to determine that the 10gR2 client was first in the $PATH. How did I know that? Use the which Unix command to find out.

Oracle: Environmental Variables and Scripting

It is also a good practice to use a separate terminal window for each ORACLE_HOME. Normally, you will operate with multiple windows open, one for each ORACLE_HOME or ORACLE_SID in different colors to visually remind you which one is production.

The example profile is provided in the code: example_profile.txt. The profile sets the entire user environment at first logon. This one is specific to the ksh or korn shell on the Linux operating system and will also work for bash. Differences in bash include that the line history is scrollable with the up and down arrows instead of vi commands.

To set the ORACLE_SID and activate all of the variables located in profile, source the file oraenv (bash, Bourne, or korn shell) or coraenv (C shell). Source means the variables will be in effect for the entire session and not just the current line in the command window. You indicate source by putting a '.' (dot) in front of the file. As the oraenv file is located in /usr/local/bin (on Linux) and this location is in the $PATH, typing it at the command line works. Putting key Oracle files, such as oraenv, oratab, and oraInst.loc, in locations that ORACLE_HOMEwill not be affected by standard Oracle installations is also part of the OFA. The oraenv script is installed into /usr/local/ bin/ automatically when running .runInstaller for the first time. Notice the prompt that you will see if you use the command as in the profile listed above:

Oracle: Environmental Variables and Scripting

A note about prompts: Every person will want to customize their own prompt so; look around for various versions that tickle your fancy. This one is better than most examples to compare to. Notice the small difference in the prompt before and after? ORACLE_SID is now defined because oraenv is executed, which also runs dbhome (also located in /usr/local/bin), but these scripts require a valid entry in /etc/oratab. If you type the ORACLE_SID incorrectly on Unix, this will be case sensitive. It will ask where the ORACLE_HOME is if there is no entry in oratab. Making an ORACLE_SID lowercase conforms to the OFA standard (see the install guide for your OS for more information). Some DBAs use an uppercase ORACLE_SID because it makes it more prominent for any type of naming convention and is meant to reduce human error.

You can use an ORACLE_SID entry in the oratab file to reference other Oracle products such as the Grid Control Intelligent Agent. The ea, which is an ORACLE_SID in the following oratab example, is what I use to indicate the Enterprise Manager Agent. The third letter after the ORACLE_HOME (N) indicates when Oracle-supplied utilities (like db_shut and db_start) are not to execute against this ORACLE_HOME. I personally use the N for my own scripting to indicate which utilities shouldn't run against this ORACLE_HOME. What this does is take advantage of Oracle-provided files—oratab and oraenv—to accomplish other types of tasks. This is only a suggested use. There are other ways of setting environmental variables for non-database products.

Oracle: Environmental Variables and Scripting

You will need to create a test database to work through all of the examples and practice scenarios. How should you create the test database? Use the Oracle-provided Database Configuration Assistant (DBCA) utility to create a test database. There are default templates provided that will work for most of the tasks outlined in this article. If you are interested in duplicating some of the advanced tasks (like Data Guard), then it will require the installation of the Enterprise Edition of Oracle Database. All tasks in this article were done with version of Oracle Database with some references to, which had just been released.

Host commands relative location

This will be important as you begin scripting. Host commands are relative to the location of the executable. As a general rule, you should execute database-specific utilities (imp, exp, datapump, RMAN, and so forth) on the server where the database is located in the correct ORACLE_HOME. This reduces the amount of issues such as core dumps and version compatibilities. This is different from what is usually thought of as a client utilities such as SQL*Plus.

There are exceptions to this rule, for it is recommended to run a compiled code (C, C++, Cobol) on a separate server rather than a database. See the following document for setting the TWO_TASK variable when using a separate node for compiled programs. TWO_TASK is an environmental variable. Subject: How to Customize Pro*C and Pro*Cobol Makefiles And On Linux/Unix [Doc ID: 602936.1].

Notice the WARNING! message that is set using the new 11g sqlnet.ora parameter SEC_USER_UNAUTHORIZED_ACCESS_BANNER. The sqlnet.ora file is part of the SQL*Net components of Oracle RDBMS, which handle the communication between clients and the database.

> sqlplus /nolog
SQL*Plus: Release - Production on Thu Nov 5 19:00:29
Copyright (c) 1982, 2008, Oracle. All rights reserved.
@> connect / as sysdba
WARNING! This computer system is the property of YOUR
and may be accessed only by authorized users.
Unauthorized use of this system is strictly prohibited and may be
subject to criminal prosecution.

If you wanted to execute something that is available on the operating system level, then you would use a host command (either Windows or Unix), or on Unix the ! symbol. The output below shows that I am logged into the newdb as sys and lists (ls command) the files located in the $ORACLE_HOME/sqlplus/admin directory:

Oracle: Environmental Variables and Scripting

Notice how the SQL prompt is populated with the ORACLE_SID and the username that is connected to the database. This is done by adding a line to the glogin.sql file, which can be done within SQL*Plus as shown below (I used the text editor vi):

Oracle: Environmental Variables and Scripting

Host commands work based on the location of SQL*Plus. If you want to execute these same commands from a Windows desktop connecting to a remote Unix database, then it would require a Windows equivalent command like Notepad or another editor. If you have Cygwin installed and configured on the Windows desktop, then it would allow you to run Unix-equivalent commands like vi.

Separating the variable part of the script into its own configuration file

There are scripts that will need some variables set, but you don't necessarily want to use the profile to set a variable at every login. The variables may need to contain commands specific to applications such as RMAN, SQL*Plus or specific to certain tasks. This is where a configuration file comes in handy, which is a personal preference as to what you call them. Be sure not to use reserved keywords for variables, as that leads to unexpected results. In the example below, we use emal instead of the word email.

To call this configuration file, you source it starting with the prerequisite . (dot) within a script. This file can also be used in the custom code section of the ioraenv file.

Oracle: Environmental Variables and Scripting

(For more resources on Oracle, see here.)

Don't hardcode values reference a configuration file and password file at runtime

Values include items such as a directory path, ORACLE_SID, e-mail, or file locations as shown in the above newdb.conf file. Passwords should be in a separate hidden file-protected location (chmod 400, which is only readable by Oracle) to be read at runtime by simply inserting a small piece of code and an appropriate variable in each script:

PASS=`grep system /u01/app/oracle/.oracle.passwd | cut -f 2`;
export PASS
$ORACLE_HOME/bin/exp userid=$PASS parfile=$HOME/export$db.par

The following line actually pulls the ORACLE_SID out of the oratab file. This is useful for executing against multiple databases on a single node. See the script labeled coalesce_tablespace.ksh for a complete example.

cat /etc/oratab | while read LINE
case $LINE in
\#*) ;; #comment-line in oratab
# Proceed only if third field is 'Y'.
if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`

This small script shows how a local script on a database node can be run without a password, as the database has a password file and the startup initialization parameter remote_login_passwordfile=EXCLUSIVE . A password file is created for a specific database with a default file name format usually found in $ORACLE_HOME/ dbs/orapw<SID>.

#Source the configuration file as follows.
. $OTILS/newdb.conf
$ORACLE_HOME/bin/sqlplus -s <<EOF
connect / as sysdba
set pagesize 0 feedback off termout off linesize 200
spool output.txtselect tablespace_name from dba_tablespaces;
spool off

Using a hidden password file for scripting is more secure than embedding clear text passwords in the script itself. There is also a relatively new feature in Oracle Database 10gR2 and above called the External Password Store. This component is also part of the Advanced Networking Option (options are always an additional license cost) and is another way to store passwords. If used for password functionality only, the External Password Store is free of charge. license.112/e10594/editions.htm#CJACGHEB: This document details the differences between the different editions of Oracle. Search for Secure External Password Store; it is included with the Enterprise Edition of Oracle.

It requires the creation of an Oracle Wallet that stores encrypted username and password combinations for batch jobs, scripts, and even application code. This is one of the most secure ways to connect to the database while providing a single location for changing passwords on a regular basis. See the Oracle Documentation, Oracle Database, and Security Guide for your release for instructions on creating and maintaining a password store. See the following white paper for instructions that don't require a MOS account: pdf/twp_db_security_secure_ext_pwd_store.pdf

Putting variables at the top of the script with curly braces

This makes it easier to spot for editing and troubleshooting. Mixed case would make them stand out even more. Curly braces will resolve to the value of a variable that had already been defined at the top of a particular script.

$ORACLE_HOME # variable at the top of a script
${ORACLE_HOME} # Refers back to the variable

Moving functions to a centralized file to be reused

All scripts can use any of the functions within by referencing the function name. The example below illustrates this:

# otils.fnc
function verify_directories {
if [ ! -d ${MY_BASEDIR} ];then
mkdir ${MY_BASEDIR}

if [ ! -d ${MY_BASEDIR}/scripts ];then
mkdir ${MY_BASEDIR}/scripts

if [ ! -d ${MY_BASEDIR}/archive ];then
mkdir ${MY_BASEDIR}/archive

And an example shell script that will call the separate function is simply run and sourced by putting a dot in front of the filename. In this case, the otils.fnc file can be found in the $PATH environmental variable.

#! /bin/ksh

. otils.fnc

Validating the use of the script

To validate a script that should only be run by a certain user ID or on a certain host, only use whoami and uname. See the Move functions to a centralized file to be reused section for the idea of a centralized function file, as this would be a good candidate.

if [ `whoami` != "oracle" ]; then
echo "error. Must be oracle to run this script"
exit 1


if [ `uname -a | awk '{print $1}'` != "databasenode" ]; then
echo "error. This script only to run on databasenode server"
exit 1

The if-fi part of this code is used to evaluate something. If it is true, then do the next step. The fi indicates where the end of this section of code should stop.

Using SQL to generate code

The script provided for this article called cleanout.sql generates a file that is then run by SQL*Plus, which demonstrates the capability to run certain host commands from within the database. This is done by using the keyword host within the script, and on Unix you can also use the exclamation point (!) to indicate host commands. The Oracle user will be limited to executables that are in the $PATH and have the appropriate file permissions.

This ability to use SQL*Plus to generate SQL commands as well as execute OS commands by embedding SQL syntax in a script is a powerful tool for a DBA. This type of execution will dynamically generate spooled SQL commands. The resulting spooled SQL file can then run in the same script or edit it as needed before running manually.

The following scripts are especially handy for one-time mass updates, such as switching users to a new profile or expiring passwords. The single ampersand symbol (&) will substitute the value for that variable you provide a single time; two ampersands will substitute every time that variable is found in a script.

The first script, grant_table_role.sql, grants all privileges for a list of tables to a role that belongs to a certain schema. The second script inserts a username into a table generated by pulling the first part of an e-mail address, that is, everything before the @ symbol. The chr(10) puts in a newline and the pipe symbols || append each item.

set echo off
set feedback off
set heading off
set pagesize 0
set timing off
set trimspool on
set verify off
spool grant_table_&USERROLE.&TABLEOWNER..sql
select 'grant select,insert,update,delete,references on ' ||table_name
|| ' to &&USERROLE; '
from dba_tables where owner = '&&TABLEOWNER';
spool off

set echo off
set feedback off
set heading off
set pagesize 0
set timing off
set trimspool on
set verify off
spool update_email_users.sql
select 'update table_name set table_name_pidm =' ||EMAIL_PIDM || ', '
||'table_name_username' ||' = ' || chr (10) ||
(select substr(email_address,1, instr(email_address,'@')-1) from
email_table)|| 'table_name_user_id= BATCH, table_name_activity_date
from general_emal;
spool off
@ update_email_users.sql

In the e-mail example above, the script that was created in the first step is automatically called after the spool-off command.

--This script is a generic create user script.
set echo off
set feedback off
set heading off
set pagesize 0
set timing off
set trimspool on
set verify off
spool create_mass_users.sql
select 'create user ' ||username || ' identified by '
||'"'||TO_CHAR(BIRTH_DATE,'MMDDYY')||'"'||' ;'|| chr(10)||
'alter user ' || username || chr (10) ||
' default tablespace USERS;' || chr(10) ||
'alter user ' || username ||
' temporary tablespace TEMP;' ||chr(10)||
' grant CONNECT to ' ||username || ';' ||chr (10)||
'grant USR_ROLE to '||username|| ';' ||chr (10)||
'grant USR_QUERY to '||username|| ';' ||chr (10)||
'grant USR_STAFF to ' ||username|| ';' ||chr (10) ||
' alter user '||username||' default role CONNECT, USR_STAFF ;'
from table_name, person_table
where table_name_pidm=person_table_pidm;
spool off
-- commented out the last statement to double check before running.
-- @ create_mass_users.sql

The only difficult part about this process is that the number of quotes needed depends on the data retrieved. If it is a literal string (data that has to have quotes to do the insert), then you will need a corresponding set of two single quotes to do this. See the example create_users.sql.

All of the queries used in these examples may not work for you, depending on the data you are trying to retrieve. They are intended to provide examples of how to use the embedded SQL technique for different types of DBA tasks.

It takes some practice, but this is a tool that will make your job easier. The various set commands are specific to SQL*Plus. These settings remove extra characters that end up in the final script. Removing those extra characters from the final script allows it to be run in SQL*Plus without editing the file first.

Unix "ps" Command Exposes Password When Using Export/Import, SQL*Plus, Tkprof [ID 372964.1] provides important information about concealing passwords, which is easily found with the Unix ps command when running scripts.

Helpful Unix commands

The following table lists some helpful Unix commands:


Unix commands

Show files greater than (>) 2 GB

find /u02/oradata -size +200000000c -ls

Show files less than (<) 2 GB

find /u02/oradata -size -200000000c -ls

Remove trace files not modified in

the last 30 days

find /u01/app/oracle -name "*.trc" -

mtime +30 -exec rm {} \;

Find scripts where new users are


find $ORACLE_HOME -type f -exec grep

-il "identified by" {} \;

Find sqlplus scripts that might

have passwords in them

find /u01/app/oracle/admin -type f -

exec grep -il "sqlplus" {} \;

Find all files that are owned by


find /u01 /u02 /u03 -user oracle -print

Remove all export files that are

larger than 1 MB and older than

30 days

find $ADMIN -name "*.dmp" -size

+1048576c -mtime +30 -exec rm {} \;

Find linked files

find /u01/app/oracle /u*/oradata -type

l -ls

Find files modified in the last 24


find /u01/app/oracle/admin/orcl -type f

-mtime -1 -print

Show total size of all

subdirectories with Disk Usage

command on a disk (sorted in

reverse order)

cd /u02/oradata

du -s * | sort -nr

du -sh will provide a shortened readout

in gigabytes

Show total size of all data files in


cd /u02/oradata/orcl

du -k * | sort -nr

Run more than one command on

a single line by using a semicolon

between the commands

date; who

To debug a script with sh or bash

and display the environmental


sh -vx or bash -vx script.bash

Bash shell debugging set

command, a negative in front

turns it on, a plus sign turns it off

set-x: Display commands and their arguments as

they are executed. set-x set+x


set-v: Display shell input lines as they are read

.#!/bin/bash -xv

There is more on SQL Toolkit Free Command Line Utilities for Oracle SQL*Plus at: http://www.runner


This article provided us with various guidelines for effective scripting.

Further resources on this subject:

You've been reading an excerpt of:

Oracle Database 11g - Underground Advice for Database Administrators

Explore Title