Oracle Scripts Powerful Tools for DBAs and Developers By Brian Lomasky & David C. Kreines 1st Edition May 1998 1-56592-438-X, Order Number: 438X 204 pages, $29.95, Includes CD-ROM

Chapter 1.
Introduction

Over the years, the authors have created and assembled a vast collection of Oracle utilities and UNIX shell scripts. We developed these scripts in order to solve day-to-day database reliability and performance analysis problems, as well as to assist in the development and maintenance of Oracle applications. In addition, we created a variety of highly specialized but infrequently-run scripts to detect database and/or operating system problems or to identify other exceptions. We've extensively tested all of these routines, and they've been in production use on a daily basis.

We believe that these scripts and other tools will allow our fellow Oracle database administrators and developers to create and use a comprehensive environment of interoperative tools. We've carefully planned the script architecture so that common scripts are called from higher-level scripts. This isolation of functionality allows DBAs to easily customize the scripts in order to match their companies' specific business rules and/or system configurations.

When you are implementing or modifying any of the scripts, be sure to check for any restrictions or dependencies (e.g., naming conventions, files that must be available). These are listed in the descriptions of the scripts in the following chapters. For your convenience, they are also included as comments at the beginning of the script files themselves.

WARNING: Since no two sites have the identical vendor implementation of Oracle and the operating system, you must test each script you plan to use in order to ensure that it runs correctly on your specific hardware/software platform. While we have invested a considerable amount of time and effort in the development of these scripts, it is impossible to guarantee that each script will always properly operate on every possible platform. All of these scripts have been tested and will work on most platforms without any modifications, but it is up to you to test them to be sure that they will properly work on a particular platform.

NOTE: Most of the scripts have been tested on Oracle versions as far back as 7.0.16, although some of them require more recent versions of Oracle (noted in the detailed descriptions of the scripts). Similarly, most of the shell scripts have been tested on HP, IBM, Pyramid, and Sun SOLARIS systems.

Converting to Other Operating Systems

The scripts in this book were developed, and are designed to run, in a UNIX operating system environment. However, please do not let that fact deter you from using the scripts if you are running in another (non-UNIX) environment. One of the strengths of Oracle has always been its portability, and since the bulk of the scripts are written in standard Oracle Structured Query Language (SQL), these scripts are portable as well.

There are two basic types of scripts found in this book (and on the accompanying CD-ROM):

• SQL scripts are well-known to most Oracle DBAs and developers; they are written in SQL to run under Oracle's SQL*Plus product, and are normally portable to any operating system in which Oracle runs. These scripts are easily identified by the extension ".sql " in the script name; in this book, they are also identified as SQL scripts by the "File Type" entry at the top of each script description.
• UNIX shell scripts are UNIX-specific programs fthat perform a series of commands and functions. The scripts in this book are written to run under the Korn or Bourne shells; they are identified as shell scripts by the "File Type" entry at the top of each script description.

In this book, many of the shell scripts are used to execute a SQL script in an orderly manner. In other operating systems, such a script might be called a "batch" script or file. For example, a shell script might set some environment variables, execute a SQL script, and display the corresponding output on the screen. In this case, you could execute the SQL script independently, or use the native scripting language of your operating system to write a batch script with similar functionality.

Consider the following shell script, which is used to list all roles in the database and the usernames who have been granted each role (this simplified example has been adapted from the rolelist script found in this book).

echo "Enter the ORACLE SID of the database to be accessed: \c"
read sid
SID=$mysid
. define
sqlplus -s / @$DBA/rolelist
lp -c rolelist.lis
rm rolelist.lis

This script gets the SID of the database from the user (via the keyboard), sets the correct environment (via the define script), calls SQL*Plus to execute the rolelist.sql script (located in the DBA directory), and finally prints the resulting report and deletes the report file.

You can readily convert this script to another operating system. For example, the following DCL file will perform the same function in a DEC VMS environment (note that in this example SID is a symbol and $DBA is a logical name):

$ INQUIRE SID /NOPUNC "Enter the ORACLE SID of the database to be accessed: "
$ @define
$ sqlplus -s /@$DBA:rolelist
$ print rolelist.lis;
$ delete rolelist.lis;

Similarly, the following would work in a DOS/Windows environment (note that accept is a utility that reads a value from the keyboard and stores it in the indicated environment variable):

echo Enter the Oracle SID of the database to be accessed
accept SID
ORA_SID=$SID
sqlplus -s / /DBA/rolelist.sql
copy rolelist.lis lpt1:
del rolelist.lis

Or you could simply execute SQL*Plus directly from the keyboard and then print the resulting file:

sqlplus -s $DBA/rolelist
print rolelist.lis
del rolelist.lis

Of course, some of the shell scripts are more complex, or perform UNIX-specific functions; our point here is that most of the scripts provided are readily transportable to other operating environments.

TIP: For more information about UNIX shell scripts, refer to a good UNIX book; one we recommend is UNIX in a Nutshell (O'Reilly & Associates, Second Edition, 1992).

Installing the Scripts

All of the scripts covered in this book are located on the accompanying CD-ROM the book. (See "About the CD-ROM" in the Preface for information about the CD-ROM contents and organization.) Before you can use the scripts, you must copy them from the CD-ROM to your system. As written, the scripts are expected to be installed in two different directories:

$DBA

This directory contains scripts that are generally useful to the DBA. You should create a directory with the appropriate permissions to hold these scripts. You might want to have this directory owned by the Oracle owner and belong to group DBA. In such a case, a permission of 750 (read, write, execute to the owner, read and execute to the DBA group) might be appropriate. If you do not want to define a DBA group, set the protection to 700, which allows read/write/execute access only to the DBA.

$TOOLS

This directory contains the rest of the scripts, which are generally useful to both DBAs and developers. Again, create a separate directory. This directory may also be owned by the "oracle" owner, but might belong to group DEV. Permission can be set to 755, which gives all permissions (read/write/execute) to the owner and gives read and execute privilege to both the group and any other users.

Defining Directories

You may select any directory names you choose; just be certain to define the environment variables DBA and TOOLS to point to the desired directory. For example, if you use /usr/scripts/dba as your DBA directory, and /usr/scripts/tools as your TOOLS directory, then the following UNIX code will define the correct environment;

DBA=/usr/scripts/dba
export DBA
TOOLS=/usr/scripts/tools
export TOOLS

The variables $DBA and $TOOLS can be used to refer to these directories.

The scripts on the CD-ROM have been organized according to this basic directory structure. The DBA subdirectory contains all files that should be copied to your $DBA direcotry, and the TOOLS subdirectory contains all files for the $TOOLS directory.

For your convenience, UNIX tar files and zip files have also been included. See "About the CD-ROM" in the Preface for details.

If you will be running the scripts on a UNIX system, the following steps will help you get your scripts installed:

1. Create a directory for DBA scripts.
2. Create a directory for TOOLS scripts.
3. Define the environment variables $DBA and $TOOLS.
4. Copy the file dba.tar to $DBA (you can mount the CD-ROM on your system, or NFS mount it on another system on your network, or use FTP to transfer the file from a PC workstation).
5. Copy the file tools.tar to $TOOLS.
6. Expand the DBA scripts as follows:

cd $DBA tar -xvf dba.tar

7. Expand the TOOLS scripts as follows:

cd $TOOLS tar -xvf tools.tar

8. Modify the user profile script (.profile for Korn and Bourne shells) to include the definitions of DBA and TOOLS environment variables.

Creating the Environment

To create the correct environment for the scripts in this book, you must modify the $TOOLS/database script. This script contains a list of all Oracle databases and their corresponding parameters. The file is used by many scripts to verify a user-supplied Oracle SID or to acquire an associated parameter. It is a simple text file that you can edit using any ASCII editor. A sample file is provided, but the file must be created with correct values before the scripts in this book will function.

Creating the Username

One restriction common to many of the scripts provided here is the requirement that an OPS$username account must exist. If you do not already have an existing OPS$username account, create one by performing the following steps:

1. Log into the Oracle database as the SYSTEM username.
2. Type the following,

CREATE USER OPS$username IDENTIFIED EXTERNALLY;

where username matches your current UNIX login name.

3. Type the following:

GRANT CONNECT, RESOURCE, DBA TO OPS$username;

4. Type the following:

EXIT

This will allow the scripts (and yourself) to access Oracle by simply typing,

sqlplus /

at the UNIX system prompt. No username or password needs to be specified. This avoids the security problem of enclosing usernames and passwords in all of your script files.

Now make the required modifications to the database script, and you are ready to unleash the power of the script collection presented in this book.

Installation Summary

The following is a summary of the steps you must complete before attempting to run the scripts in this book:

1. Create directories for DBA and TOOLS, and assign the DBA and TOOLS environment variables to them.
2. Copy the scripts from the CD-ROM into the appropriate directories.
3. Edit the database file to include the appropriate parameters for your Oracle databases.
4. Edit the fixcase script if necessary to match your site's standards.
5. Edit the mailto script to enable automatic email notifications.
6. Create an OPS$ account with DBA privileges.
7. If you are using the backup scripts, follow the installation and configuration instructions in Chapter 6.

How the Scripts Work

In many cases the scripts contained in this book are actually pairs of scripts. The typical operation of these "paired" scripts follows:

1. A UNIX shell script is executed by entering the script name from the UNIX command line (sometimes with parameters as indicated in the script documentation).
2. The UNIX shell script makes calls to other scripts to initialize the UNIX environment, validate arguments, etc.
3. A SQL script (often with the same name as the shell script, but with an extension of .sql ) is called. Arguments may be passed to the SQL script, or the SQL script may prompt the user for input (like the name of a table).
4. The SQL script may create an output file, and sometimes one or more temporary Oracle tables.
5. Control will return to the shell script, which will display the output file on the terminal screen.

Since many of the .sql scripts create output files (see Appendixes C, SQL Scripts That Create Output Files, and D, Output Files Created by SQL Scripts, Output Files for cross-references to these files), these files are usually available for further processing. In addition, most scripts that display the file on the terminal screen contain a commented lp command that may be uncommented if you desire printed output.

Other scripts create SQL files as their output. These files are designed to be executed by Oracle's SQL*Plus, and of course may be editted just like any other SQL script. For example, the script crindexs.sql creates a file called cr_index.sql, which contains all of the SQL statements necessary to recreate all indexes in your database. This file can be run at any time using SQL*Plus.

What Can I Do with the Scripts?

Table 1-1 provides a list of tasks that a DBA or developer might want to perform, and the corresponding scripts from this book that will perform those tasks.

NOTE: Only those scripts that perform a particular task are listed here; there are many other scripts included that are considered "support" or "internal" scripts--that is, they are called by one of the "main" scripts to perform a specialized function, but may not be useful by themselves. These scripts have not been included, although they are documented in the book.

Table 1-1: Oracle Script Summary

If You Want To...	Use This Script...	Chapter
Analyze dump and trace files every five minutes	every5	4
Analyze tables and indexes using ANALYZE COMPUTE STATISTICS	analyze.sql	8
Analyze the alert file for errors	alertlog	4
Analyze the appropriateness of indexes without creating statistics	analinds.sql	8
Back up archived log files	backarch	6
Calculate the age of a file in days	days_old	10
Calculate the average row size for a table	avgrow.sql	8
Check a table's columns for special characters (more than ten columns)	chktabl2.sql	8
Check a table's columns for special characters (up to 10 columns)	chktable.sql	8
Check create database log for errors	chkcrdb	8
Confirm that a table name is valid and exists	istable	8
Create a file containg statistics and SQL for a single session	sesstats.sql	3
Create a file containing a detailed tablespace analysis	tabanal.sql	8
Create a file containing a list of all database files ordered by directory	locate2.sql	8
Create a file containing a list of all dependencies in the database	depend.sql	8
Create a file containing a list of all indexed columns	indcols.sql	8
Create a file containing a list of all invalid objects	invalid.sql	8
Create a file containing a list of existing locks	locks.sql	4
Create a file containing a list of freespace in the database	free.sql	8
Create a file containing a report of session waits	waitstat.sql	3
Create a file containing alert file errors	alertlog.sql	4
Create a file containing all grants against columns	colgrant.sql	5
Create a file containing all grants to specific user or role	grant_to.sql	5
Create a file containing all profiles in the database	profiles.sql	5
Create a file containing all security privileges for a user	privlist.sql	5
Create a file containing all security privileges for a user	privs.sql	5
Create a file containing all table privileges	alltabp.sql	5
Create a file containing all user account quotas	quotas.sql	5
Create a file containing all user accounts	usrs.sql	5
Create a file containing ANALYZE VALIDATE STRUCTURE commands	validate.sql	4
Create a file containing configuration and sizing information	tblsize.sql	8
Create a file containing database structures and associated files	file_use.sql	8
Create a file containing grants against an object segregated by type of grant recipient	tabgrant.sql	5
Create a file containing grants on objects	allgrant.sql	5
Create a file containing grants on users and roles	allprivs.sql	5
Create a file containing list of current users	dbousers.sql	2
Create a file containing roles and grantees of those roles	rolelist.sql	5
Create a file containing rollback segment information	rollback.sql	3
Create a file containing statistics for an individual table	tablstat.sql	8
Create a file containing tablespace utilization statistics	tablesp.sql	8
Create a file describing all V$ and DBA_ views	descview.sql	8
Create a file of SQL statements being executed	dbo_sql.sql	8
Create a list of all installed Oracle products	products	8
Create a new index on a table	crindex	8
Create a new OPS$ account	makeops.sql	5
Create a new user with the same properties as an existing user	copyuser	5
Create a report of all object auditing options in effect	auditobj.sql	5
Create a report of all statement and object auditing options in effect	audobj.sql	5
Create a report of all statement auditing options in effect	auditst.sql	5
Create a report of the current month's usage by username	audses.sql	5
Create a report of the most frequently used tables and views	audhot.sql	5
Create a script containing nonunique index drop and create statements	cdindex.sql	8
Create a script to create an OPS$ user account for an existing username	makeops.sql	5
Create a script to grant a user all privileges on non-system objects	grantall.sql	5
Create a script to recreate grants for tables and columns	getgrant.sql	5
Create a SQL script containing INSERT statements for use in tablsize	tblsizec.sql	8
Create a SQL script to create a new index on a table	cindex	8
Create a SQL script to fix ownership of primary keys	fixowner.sql	8
Create a SQL script to list the contents of X$ tables	fixtable.sql	8
Create a SQL script to recreate a control file	ccontrol	6
Create a SQL script to recreate all clusters	crclusts.sql	8
Create a SQL script to recreate all comments	crcomms.sql	8
Create a SQL script to recreate all database links	crlinks.sql	8
Create a SQL script to recreate all functions	crfuncs.sql	8
Create a SQL script to recreate all grants	crgrants.sql	8
Create a SQL script to recreate all indexes	crindexs.sql	8
Create a SQL script to recreate all package bodies	crbodys.sql	8
Create a SQL script to recreate all packages	crpacks.sql	8
Create a SQL script to recreate all procedures	crprocs.sql	8
Create a SQL script to recreate all profiles	crprofs	8
Create a SQL script to recreate all roles	crroles.sql	8
Create a SQL script to recreate all sequences	crseqs.sql	8
Create a SQL script to recreate all snapshot logs	crsnlogs.sql	8
Create a SQL script to recreate all snapshots	crsnaps.sql	8
Create a SQL script to recreate all synonyms	crsyns.sql	8
Create a SQL script to recreate all triggers	crtrigs.sql	8
Create a SQL script to recreate all user accounts	crusers.sql	8
Create a SQL script to recreate all views	crviews.sql	8
Create a SQL script to recreate sized tables and indexes	tblsize.sql	8
Create a SQL script to recreate sized tablespaces	tblsize.sql	8
Create a SQL script to recreate the database structure	crdb	8
Create a SQL script to resize an existing table	resize	8
Create a unique temporary file name	mktemp	11
Create a UNIX script to backup database files	creback	6
Create a UNIX script to restore database files	creback	6
Create all nonunique indexes for a table	cdindex	8
Create all SQL scripts required to drop tables	ddl	8
Create all SQL scripts required to recreate tables	ddl	8
Create an opcard for Alexandria Backup	cr_alex	6
Create an OPS$ account	creops	5
Crfeate a SQL script to recreate all constraints on tables	crconstr.sql	8
Define an RCS environment variable	lib	9
Define parameters for DBA-related scripts	dbabatch	10
Define the characteristics of your database instances	database	2
Define the UNIX environment	define	2
Delete obsolete archived log files	delarch	6
Determine if database is started	isdbo	2
Determine start/stop status of concurrent manager for Applications	is_mgr	7
Determine the status of the database (up or down)	dbo_stat	2
Disable all primary key, unique, and foreign key constraints	discon.sql	8
Dispaly a list of all invalid objects	invalid	8
Display a detailed analysis of a tablespace	tabanal	8
Display a list of all database files ordered by directory	location	8
Display a list of all indexed columns	indcols	8
Display a list of all nondata objects	nondata.sql	8
Display a list of columns in a table	cindexd.sql	8
Display a list of files that were backed up	backdisp	6
Display a list of locked RCS files	reservs	9
Display a report of freespace in the database	free	8
Display all database structures and associated files	file_use	8
Display all dependencies in the database	depend	8
Display all grants against columns	colgrant	5
Display constraints with owner different from table owner	bad_cons.sql	8
Display grants on objects	allgrant	5
Display grants on users and roles	allprivs.sql	5
Display HP/UX memory statistics	allmemry	12
Display processes connected to the database	connect.sql	3
Display rollback segment information	rollback	3
Display statistics for an individual table	tablstat	8
Display the amount of free memory	freemem	12
Display the current status of the concurrent manager	chkcm	7
Display the date and time the instance was started	instance.sql	8
Display the history of a program in RCS	history	9
Display the owner and tablespace for a table	tableown	8
Display the owner and tablespace for a table	tableown.sql	8
Display the plan output from EXPLAIN PLAN	autoplan.sql	9
Drop a specified object	drop_obj	8
Drop all nonunique indexes for a table	cdindex	8
Enable all primary key, unique, and foreign key constraints	enacon.sql	8
Enforce a 10-minute timeout while waiting for database shutdown	dbostopt	2
Export an entire database	xport	8
Get the name of the operating system	getsyi	11
Grant select on objects to the READONLY role	readonly.sql	5
Grant SELECT on specific tables to users to run scripts	grantool	5
Import an entire database	mport	8
Kill a UNIX process	killunix	10
Kill an Oracle session	dbakill	10
Lock a file in RCS	reserve	9
Make the database available to menus	online	2
Modify the case of a parameter according to local standards	fixcase	2
Perform a backup on a specific database	backPROD	6
Perform a complete backup	backup	6
Perform ANALYZE VALIDATE STRUCTURE on all tables and indexes	validate.sql	4
Perform GRANTS on tables required to run scripts	grantool.sql	5
Provide menu access to databases	menu	2
Remove a database and all associated files	dbdelete	8
Remove old backup files	backcln	6
Remove the database from menus	offline	2
Remove trailing white space from records in a file	trunc	11
Rename a column	renamcol.sql	8
Replace a fine in RCS	replace	9
Replace a portion of the UNIX path	replpath	11
Report all existing locks in the database	locks.sql	4
Report all grants to a specific user or role	grant_to	5
Report all profiles in the database	profiles	5
Report all roles and to whom they were granted	rolelist	5
Report all security privileges for a user	privlist	5
Report all security privileges for a user	privs	5
Report all user account quotas	quotas	5
Report all user accounts	usrs	5
Report grants against an object segregated by type of grant recipient	tabgrant	5
Report Oracle and UNIX performance statistics	monitor	3
Show current users of the database	dbousers	2
Shut down the database	dbostop	2
Specify database access by user	menu.dat	2
Start SQL*Net listeners	startnet	2
Start the concurrent manager	startm	7
Start the database	dbostart	2
Stop SQL*Net listeners	stopnet	2
Stop the concurrent manager	stopm	7
Unlock a previously locked RCS file	unreserv	9
Verify that an SID is valid	valid_db	2
Wait for online redo logs to be archived	archwait	6

 

Using the Script Descriptions

The following chapters contain detailed descriptions of the scripts which are contained on the CD-ROM. This is the basic documentation for each of the scripts, and is provided to help you understand the functions and operation of the scripts.

We have adopted a standard style for the documentation of each script. Here are samples of two script documentation descriptions:

profiles

---

Directory: $DBA
File Type: Shell Script
Edit Required: No
Output File: Screen
Syntax: profiles [sid]

sid

The optional Oracle SID for the database to be reported. If omitted, all databases will be reported.

This script creates a report of all profiles that are defined in the specified database (or all databases if no database is specified.

profiles.sql

---

Directory: $DBA
File Type: SQL Script
Edit Required: No
Output File: profiles.lst

This script is called by the profiles script to create the report file.

Sample report

PROFILES - Profiles                              
                                                                                
Profile         Resource                                                        
Name            Name                      Limit                                 
--------------- ------------------------- ---------------                       
DEFAULT         COMPOSITE_LIMIT           UNLIMITED                             
DEFAULT         SESSIONS_PER_USER         UNLIMITED                             
DEFAULT         CPU_PER_SESSION           UNLIMITED                             
DEFAULT         CPU_PER_CALL              UNLIMITED                             
DEFAULT         LOGICAL_READS_PER_SESSION UNLIMITED                             
DEFAULT         LOGICAL_READS_PER_CALL    UNLIMITED                             
DEFAULT         IDLE_TIME                 UNLIMITED                             
DEFAULT         CONNECT_TIME              UNLIMITED                             
DEFAULT         PRIVATE_SGA               UNLIMITED                             
 
9 rows selected.

The following elements are included in the documentation for each script:

Name

The name of the script.

Directory

The directory where the script can be found: either $DBA for the directory specified for DBA scripts at installation, or $TOOLS for the directory specified for other (non-DBA ) files at installation.

File Type

The type of file. Most scripts are either UNIX shell scripts (denoted by no extension; for example, profiles above), or SQL scripts (denoted by an extension of .sql; for example, profiles.sql above).

Edit Required

This indicates whether you are required to make changes to this file before running it. Some files may indicate that editing is optional, which means that some changes may be required to conform to standards at your particular installation.

Output File

The name of the output file (if any) produced by this script. "Screen" indicates that output is directed to the terminal screen. Note that screen output may be redirected to a file or other device if desired.

Script Description

A description of what the script does, along with any other information that might be necessary to successfully run the script. For example, if a script prompts for input, we'll note this in the body of the description.

In addition to the elements listed above, the following may be included in the documentation for a particular script, depending on the characteristics of that script:

Syntax

For scripts that require command line parameters, a syntax section is included. The documentation for the profiles script above shows that there is an optional (because it is enclosed in brackets) argument sid which can be included on the command line. The parameter explanation indicates that this is the Oracle System Identifier (SID) for the database and that if it is omitted, all databases will be reported.

Sample Report/Output

In cases where sample output helps your understanding of the script, it has been included. Not all script documentation pages include sample output, since much of the output produced is obvious and self-explanatory.

Back to: Oracle Scripts: Powerful Tools for DBAs and Developers