3.4 Orac

Orac is a tool developed by one of your authors, Andy Duncan. It is based on Perl/Tk and its many widgets, and it employs Perl DBI to connect to the Oracle database. It is basically a GUI wrapper containing a large repository of prepared, configurable SQL scripts that allow Oracle DBAs to interrogate and manage their databases. Using Orac, users can rapidly apply these scripts to any target databases without having to copy them from one machine to another via complicated directory structure installs and environment variable setups. If these scripts are no longer up-to-date because of changes to the Oracle data dictionary, they can be modified or changed directly within the repository. Orac makes it easy for you to make changes to the scripts.

Orac owes a great debt to many people besides its main author. Andy received early input from Dave Ensor, coauthor of Oracle Design (O'Reilly) and from Tim Bunce, chief creator of Perl DBI and coauthor of Programming the Perl DBI (O'Reilly). Many of Orac's central scripts were based largely upon those packaged up by Brian Lomasky in his book, Oracle Scripts, for which he graciously gave permission for adaptation and use within the Orac program. As the program grew, Guy Harrison, author of the excellent Oracle SQL: High Performance Tuning (Prentice Hall), also allowed his very fine tuning scripts to be adapted for use within Orac. Since Orac was first released on CPAN, many other Oracle DBAs have contributed additional useful scripts. Orac has become a real community effort.

3.4.1 Installing Orac

You can download the Orac tarball from here:

http://www.cpan.org/authors/id/A/AN/ANDYDUNC

3.4.1.1 Installing Orac on Unix

Once Orac has been downloaded, unpack it, and set the environment. Once this is completed, we can then simply run it with a single command:

$ gzip -d Orac-alpha-1.2.6.tar.gz
$ tar xvf Orac-alpha-1.2.6.tar
$ cd Orac-alpha-1.2.6

Before you actually run the program, make sure your environment can run both ordinary Perl DBI and Perl/Tk scripts. Ensure that ORACLE_HOME is set to make sure DBD::Oracle works correctly, underneath Perl DBI.

$ export ORACLE_HOME=/u01/oracle/8.1.5

3.4.1.2 Installing Orac on Win32

For Win32, simply unpack the tarball into its own directory with your favorite unzip program. If you have Perl DBI and Perl/Tk both working on your machine, Orac should be ready to fire up straight out of the box, once you've personalized it as described in the next section. (You may have to set ORACLE_HOME via the Registry, the system environment, or AUTOEXEC.BAT in order to get Perl DBI connecting to Oracle properly.)

3.4.2 Personalizing Orac

On a server system, Orac allows each of its users to save his or her own personalized options, such as font, background color, and so on. It then stores these in various locations depending upon the operating system:

Unix

Your personal options are hidden within the $HOME/.orac directory.

Win32 (business systems with the USERPROFILE environment variable)

Personal options are stored within the %USERPROFILE%/orac directory.

Win32 (home systems, without the USERPROFILE environment variable)

Options are stored within the directory from which you launch Orac.

Alternatively, personal options can be stored in a named directory. To do this, set the following environment variable before running the program on Unix:

$ export ORAC_HOME=/my_personal_options_directory/orac_profile

To carry out a similar operation on Win32 (especially if you're on a non-USERPROFILE system like Win98), edit your AUTOEXEC.BAT file to preset your environment when booting up:

DOS> set ORAC_HOME=C:\Temp\Orac

3.4.3 Running Orac

Within the Win32 environment, double-click on the orac_dba.pl program icon, or on either Unix or Win32 command lines, run the following command:

$ perl orac_dbal.pl

Because Orac also works with other databases, the first time you log in to the program, choose Oracle as your default database. Following the subsequent user login dialog, you'll be given access to a wide range of menu-driven options designed for an Oracle DBA (see Figure 3-9) summarized in Table 3-1.

Figure 3-9. Some of the options under Orac

Table 3-1. Orac's main user options for Oracle DBAs

Main menu	Description
File	Provides individual user customizations and general program help.
Devel	Collection of GUI screens based on the DDL::Oracle API.
Structure	Options to access the current physical structure of the database.
Object	Daily DBA tasks and problem solving, such as DDL generation to create the entire database in a single script and PL/SQL debug options.
Lock	Investigates the various types of locking going on in a target database. Especially useful in panic stations.
Tune	Tuning options, including a SQL Cache Browser and physical IO graphs.
SQL	Gateway to Thomas Lowery's GUI shell program for direct database work.
My Tools	Facility for storing your own favorite DBA scripts and rerunning them as GUI reports driven by automatically generated buttons and menus.