Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Job Management in UNIX Administration

Oracle UNIX/Linux Tips by Burleson Consulting

Oracle job management in UNIX

This section describes techniques for submitting and monitoring Oracle tasks that have been placed inside UNIX shell scripts. Let?s start by showing how a UNIX shell script is submitted, and then move on to more advanced topics.

Here is an example of a UNIX script that contains SQL*Plus commands to analyze all tables and indexes for an Oracle database. The script accepts the $ORACLE_SID as an input argument.


# Validate the Oracle database name with
# lookup in /var/opt/oracle/oratab
TEMP=`cat /var/opt/oracle/oratab|grep \^$1:|\
cut -f1 -d':'|wc -l`
tmp=`expr TEMP`     # Convert string to number
if [ $tmp -ne 1 ]
   echo "Your input $1 is not a valid ORACLE_SID."
   exit 99

# First, we must set the environment . . . .

ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export PATH
MON=`echo ~oracle/obj_stat`
export MON

# Get the server name
host=`uname -a|awk '{ print $2 }'`

$ORACLE_HOME/bin/sqlplus ?s perfstat/perfstat<<!

set heading off;
set feedback off;
set echo off;
set pages 999;
set lines 120;

-- First, let's get the latest statistics for each table

spool $MON/run_analyze.sql
select 'analyze table '||owner||'.'||table_name||' estimate statistics
sample 5000 rows;'
   owner not in ('SYS','SYSTEM','PERFSTAT');
--  ******************************
--  Analyze all indexes for statistics
--  ******************************
select 'analyze index '||owner||'.'||table_name||' compute statistics;'
   owner not in ('SYS','SYSTEM','PERFSTAT');
spool off;

set echo on;
set feedback on;




Here we see that our SQL*Plus script is encapsulated inside a UNIX script.  We check to ensure that a valid $ORACLE_SID is passed to this script as a parameter, and we then execute SQL*Plus from inside the UNIX script.

Now, let?s see how we might submit this task in UNIX as a background job.

Submitting Oracle jobs in the background

UNIX provides the nohup command to submit a task in the background. This technique is used for long-running Oracle jobs, and is used to free-up your command line prompt, which is especially useful if you are dialed-in to the UNIX server.  In the example below, we have a script that executes a SQL*Plus command, and we submit it for background processing.

nohup run_me.ksh > outfile.lst 2>&1 &

This command has the following components:

1. nohup          This directs UNIX to submit the job

2. run_me.ksh     This is the name of the UNIX executable script

3. >              This re-directs the standard output to a UNIX file

4. outfile.lst    This is the UNIX file location for the script output

5. 2>&1           This re-directs standard error to standard out, showing any error                               messages

6. &              This submit the job as a background task

Watch the execution of a background process

From the example above, we directed the script output to a file called outfile.lst.  We can monitor the execution of the background process by using the UNIX tail ?f command, and see each line of output as it is written to the file.

>tail ?f longfile.lst

To exit the tail ?f command, you enter <crtl> c at any time to return to the UNIX prompt.

UNIX task management techniques

Now that we understand how to submit a single task, let?s look at some more sophisticated UNIX techniques for managing multiple tasks.  Here we will look at techniques for verifying the number of input parameters to a UNIX script and also examine techniques for submitting multiple UNIX tasks at the same time.

parameter checking for Oracle shell scripts

The following code snippet will end a UNIX script with the exit command if the appropriate arguments have not been passed to the script.  In this example, the run_purge.ksh script requires two parameters, a valid ORACLE_SID and a numeric value, specifying the number of days back to purge. For example:

root> run_purge.ksh MYSID 200

Below we see that the script will terminate if the appropriate parameters are not passed to the script.

# Exit if no first parameter $1
if [ -z "$1" ]
   echo "Usage: run_purge.ksh <ORACLE_SID> <#_days> (where value is > 100)"
   exit 99

# Exit if no second parameter $2
if [ -z "$2" ]
   echo "Usage: run_purge.ksh <ORACLE_SID> <#_days> (where value is > 100)"
   exit 99

# Exit is parm is not greater than 100
tmp=`expr $2`            # Convert string to number
if [ $tmp -lt 100 ]
   echo "Argument two is less than 100.  Aborting Script."
   exit 99

Make sure that the UNIX user is oracle

This statement will ensure that all UNIX scripts are only executed by the Oracle UNIX user.  This technique ensures that only oracle executes the script and offers extra security and protection against unauthorized execution.  Note the use of the UNIX whoami command to capture the current user ID.

if [ `whoami` != 'oracle' ]
   echo "Error: You must be oracle to execute.  Exiting."
   Exit 99

Validate an $ORACLE_SID being passed to a UNIX script

This code snippet is useful when you want to ensure that a valid database name is passed to a UNIX script.  Note that the /etc/oratab file is for HPUX and AIX, and you may need to change it to /var/opt/oratab if you are using Solaris.


# Exit if no first parameter $1 passed
if [ -z "$1" ]
   echo "Please pass a valid ORACLE_SID to this script"
   exit 99

# Validate the Oracle database name with lookup in /etc/oratab
TEMP=`cat /etc/oratab|grep \^$1:|cut -f1 -d':'|wc -l`
tmp=`expr TEMP`            # Convert string to number
if [ $tmp -ne 1 ]
   echo "Your input $1 is not a valid ORACLE_SID.  Retry."
   exit 99

Multiplexing Oracle tasks in UNIX

If you have a large number of tasks to perform in a short amount of time, you can save time by running the jobs simultaneously in UNIX.

A great example is Oracle DBA table reorganizations. When you process table reorganizations in parallel, the total time required to reorganize all the tables is no more than the time required for the largest table. For example, if you need to reorganize 100 gigabytes of table data in a single weekend, the parallel job submission approach is the only way to go.

Below is a Korn shell script you can use to execute the reorganization. The script uses the UNIX nohup command to submit simultaneous CTAS reorganizations at the same time.

# Written by Donald Keith Burleson
# usage: nohup don_reorg.ksh > don_reorg.lst 2>&1 &

# Ensure that running user is oracle . . . . .
oracle_user=`whoami|grep oracle|grep -v grep|wc -l`;
oracle_num=`expr $oracle_user`
if [ $oracle_num -lt 1 ]
 then echo "Current user is not oracle. Please su to oracle and retry."

# Ensure that Oracle is running . . . . .
oracle_up=`ps -ef|grep pmon|grep -v grep|wc -l`;
oracle_num=`expr $oracle_up`
if [ $oracle_num -lt 1 ]
 then echo "ORACLE instance is NOT up. Please start Oracle and retry."

# Submit parallel CTAS reorganizations of important tables
nohup reorg.ksh CUSTOMER  >customer.lst  2>&1 &
nohup reorg.ksh ORDER     >order.lst     2>&1 &
nohup reorg.ksh ITEM      >item.lst      2>&1 &
nohup reorg.ksh LINE_ITEM >line_item.lst 2>&1 &
nohup reorg.ksh PRODUCT   >product.lst   2>&1 &

A UNIX script to ensure than a daemon is running

The following script is used to ensure that a UNIX vmstat monitor is always running.  This script can be scheduled via cron every 15 minutes.  The scripts checks to see if the UNIX daemon is running, and re-starts it if it has failed.

In this example, we have a UNIX daemon called get_vmstat_linus.ksh that collects vmstat information from UNIX and places it into a STATSPACK extension table called stats$vmstat.  We always want to make sure that this script is running, and the following script verifies that it is executing.


# First, we must set the environment . . . .

vmstat=`echo ~oracle/vmstat`
export vmstat
ORACLE_SID=`cat ${vmstat}/mysid`

ORACLE_HOME=`cat /etc/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export PATH

# If it is not running, then start it . . .
check_stat=`ps -ef|grep get_vmstat|grep -v grep|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -le 0 ]
 then nohup $vmstat/get_vmstat_linux.ksh > /dev/null 2>&1 &


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.




Oracle training Excel
Oracle performance tuning software 


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.