Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




SQL and PL/SQL Scripting
Oracle Tips by Burleson

The use of scripts and batch processes is the oldest aspect of computing and still plays a very active role in most systems. Most major operating systems support at least one scripting language. Table 3.1 highlights the predominant scripting language for each major operating system.

Table 3.1 The scripting languages supported by major operating systems.

Operating System

Scripting Language


MS-DOS batch language



VMS and OpenVMS


Windows 95

Visual Basic, Perl, MS-DOS batch language

Windows NT

Visual Basic, Perl, MS-DOS batch language


C shell, Bourne shell, Korn shell, Perl, awk, sed

As you can see, Unix is particularly rich in scripting tools. Unix is also the predominant platform for Oracle databases (Windows NT is gaining strength, while VMS and OpenVMS are gradually disappearing). Consequently, the scripts included in this chapter (where appropriate) contain examples of scripts on a Unix system, but keep in mind that SQL*Plus and PL/SQL can be used with the scripting language of any operating system.

This chapter will introduce you to the basic uses of scripts in an Oracle database and discuss the tools you’ll need to script using SQL*Plus and PL/SQL.

The Many Faces Of Scripting

Scripts are used to perform many types of tasks in an Oracle database. The database administrator’s duties often involve complex tasks that are automated with scripts, but this isn’t the entire world of scripting. Scripts perform many different tasks within a system, including:

  • Performing system backups and helping with restoration.

  • Administering system security and user creation.

  • Dynamically generating code.

  • Testing stored PL/SQL objects.

  • Any other task that has to be performed regularly.

Backup And Restore

The most well-known scripts in an Oracle database are those that the DBA uses to back up the database. Listing 3.1 is a simple generic script that a DBA could use to perform a cold backup of an Oracle database (you may remember this script from Chapter 1).

Listing 3.1 A generic cold backup script for an Oracle database.

# Set up the environment variables.
ORACLE_SID=registrar_db; export ORACLE_SID
ORACLE_HOME=/dbhost/database/oracle/v722; export ORACLE_HOME
# Shut down the database.
connect internal
shutdown immediate
# Backup the database control files, redo logs, and dbf files.
tar -cvf /dev/backup/tdr $ORACLE_HOME
# Restart the database.
connect internal

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter