 |
|
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 |
MS-DOS batch language |
|
OS/2 |
REXX |
|
VMS and OpenVMS |
DCL |
|
Windows 95 |
Visual Basic, Perl, MS-DOS batch language
|
|
Windows NT |
Visual Basic, Perl, MS-DOS batch language
|
|
Unix |
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.
#
svrmgrl
connect internal
shutdown immediate
#
# Backup the database control files, redo logs, and dbf files.
#
tar -cvf /dev/backup/tdr $ORACLE_HOME
#
# Restart the database.
#
svrmgrl
connect internal
startup
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
|
