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

 E-mail Us
 Oracle Articles
New 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 batch shell scripting

Oracle Database Tips by Donald Burleson

It's not uncommon to use Linux shell scripts (ksh or bash) scripts to invoke Oracle processes remotely, usually using a crontab (on in Windows with the AT command).  It's easy to embed Oracle commands into a batch shell script and use the shell command language to analyze and/or format the output from Oracle.  I also recommend getting the free Oracle Linux command reference poster.

Here are some of my Oracle shell scripts that can be executed batch:

UNIX ksh Scripts for the Oracle DBA
Disk I/O scripts in Oracle
Oracle to capture UNIX/Linux vmstat information
Setting Oracle script variables in Linux/UNIX
Scheduled job completion scripts
Oracle Streams Replication scripts
Linux, The script commands
Oracle - The UNIX ls command - list files
Linux, Invoking Crontab with various options
STATSPACK monitor disk IO shell script

In my O'Reilly book "Oracle UNIX for DBA pocket reference" and my Oracle Press book "Oracle UNIX Administration handbook" I have loads of sample Oracle shell scripts, but the principle is quite simple. 

Here is a simple example of a batch shell script that accesses Oracle and writes the output to a file named myfile.txt:

# First, we must set the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH
$ORACLE_HOME/bin/sqlplus system/manager<<!
spool myfile.txt
select * from v\$database;

You can also execute external OS batch shell scripts to collect server metrics (vmstat, iostat) and store the results in Oracle tables.


while true
   iostat -x  300 1|\
      sed 1,2d|\
      awk  '{ printf("%s %s %s\n", $1, $4, $5) }' |\
      echo $HDISK
      echo $VMSTAT_IO_R
      echo $VMSTAT_IO_W
      sqlplus -s / <<!
      insert into
         (SYSDATE, 300, '$HDISK', $VMSTAT_IO_R,$VMSTAT_IO_W);
   sleep 300

If you have external scripts, you can also write scripts to ensure that they are always collecting data:


# First, we must set the environment . . . .
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'`
MON=`echo ~oracle/iostat`
# If it is not running, then start it . . . 
check_stat=`ps -ef|grep get_iostat|grep -v grep|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -lt 1 ]
 then nohup $MON/get_iostat_solaris.ksh > /dev/null 2>&1 &

You can also use batch scripts to loop from database to database, to perform system-wide functions, like this one that deletes from current_logons table:


MYDATE=`date +"%Y%m%d"`
SERVER=`uname -a|awk '{print $2}'`
if [ -f /usr/local/src/rsp_server-0.01/inetd_copy/core ]
   # Remove all connections for this WebServer from the database
   su - oracle -c "/usr/app/oracle/admin/product/bin/sqlplus reader/reader@testb1<<!
   select count(*) from current_logons;
   delete from current_logons where webserver_name = '$SERVER';
   select count(*) from current_logons;
If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & 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 at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.