Oracle Scripts

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.

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).

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):

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):

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

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.