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


 

HTML Text

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

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.

  
 

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