Chapter 4. Building Oracle Applications with Perl/Tk and Tcl/Tk

The previous chapter introduced the use of the most popular open source scripting languages—Tcl, Perl, and Python, along with their GUI toolkits—and the Oracle interfaces built upon them. In this chapter, we’ll take a detailed look at two particular Oracle applications, one from the Perl camp and the other from the Tcl camp:

Orac

A Perl/Tk GUI tool designed mainly for database and system administrators; it performs database management and performance tuning and also makes use of the Perlplus Netscape plug-in.

Oddis

A Tcl/Tk GUI tool conceptually similar to Orac and designed for both DBAs and developers; it performs database management and performance tuning, with a special focus on SQL tuning.

As yet, no major Python Tkinter Oracle application making use of DCOracle is generally available. We’re hoping one of our faithful readers will remedy this situation in the near future.

In addition to describing what Orac and Oddis do (and briefly mentioning a few related tools, such as dbMan), we’ll also spend some time looking at the implementations of these two Oracle applications. We’re hoping that looking at these implementations might give you some good ideas for how to approach building your own applications. Although these relatively large applications were developed entirely independently from each other, it’s interesting to see how their functionality overlaps.

In the second part of this chapter, we’ve also provided a small but fully worked-out example designed to show you the way that existing tools can be extended. We’ll take two existing base tools and then blend them together in a hundred or so lines of code to provide a new example application called TableSpacer. The purpose of TableSpacer is to graphically display Oracle table space usage. It is based on the following tools:

Oratcl

Tcl’s Oracle module, introduced in Chapter 3, which allows Tcl applications to interact directly with the Oracle database via OCI.

BLT

An extension to Tcl/Tk that adds plotting graphs and barcharts to Tk canvases. When combined with Oratcl, BLT has a lot of potential. Many engineers, NASA scientists, and astronomers have been using BLT as their bacon, lettuce, and tomato Tcl package for quite some time. Check it out—you could find yourself in stellar company.

Orac

Orac is a tool developed by one of your authors, Andy Duncan. It is built upon the base of Perl/Tk and its many widgets, and it employs Perl DBI to connect to the Oracle database. In this section, we’ll describe how the Orac program makes use of many of the Perl/Tk widgets.

The Orac program was originally developed to provide a way of keeping a useful collection of Oracle DBA scripts wrapped up together in one central place. It is basically a GUI wrapper containing a large repository of prepared, configurable SQL scripts for interrogating and managing 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 first came about because Andy was working on a two-man team looking after 25 or so revenue-critical production databases and about 15 development databases at a large corporate data center. Although not particularly massive, the databases were constantly being upgraded by large teams of developers and being hit by as many as 500 sales representatives at a time. Something was required to ease the constant workflow generated by this activity. Orac became that something, starting out as a Perl/Tk GUI-wrapped script to kill spinning processes and gradually growing into a complete DBA toolbox with some system administration aids thrown in for good measure. Although Orac was originally aimed directly at DBAs (and partially at system administrators), as the program developed, it also acquired a number of Oracle development aids.

Tip

Orac’s central SQL driving scripts were based largely upon those developed by others. Many of these ubiquitous scripts have floated around for years, and we can’t easily attribute them. Some, though, are based on Brian Lomasky’s superb collection (packaged up in his book, Oracle Scripts); 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, also allowed his very fine tuning scripts to be adapted for use within Orac. (See Appendix C, for references to both books.)

Since Orac was first released on CPAN, many users have sent in additional scripts which have been used to enhance the program. Over time, the Orac program has evolved into a wide-ranging tool. It gives typical Oracle DBAs most of the basic answers they need when they interrogate a database for the background information necessary to perform database administration, tuning, or problem resolution. As most Oracle DBAs’ lives are not typical, however, Orac also has enough flexibility deliberately built in to it to allow it to quickly adapt to differing situations. The program can easily be modified to ask many new questions as new needs arise.

Orac is so flexible that it has been ported for use under both Informix and Sybase. These ports employ the same GUI interface combined with a different set of menu configuration and SQL files. Indeed, if you should wish to, you can even switch databases mid-stream while using the program. But since this is an Oracle book, let’s get down to Oracle business.

Installing Orac

You can download the Orac program from several locations on CPAN (the Comprehensive Perl Archive Network):

http://www.perl.com/CPAN-local/authors/id/A/AN/ANDYDUNC/

This is linked to the following Perl module sites:

http://www.perl.com/CPAN-local/modules/by-module/Tk/
http://www.perl.com/CPAN-local/modules/by-module/DBI/
http://www.perl.com/CPAN-local/modules/by-module/Shell/

Look for the latest tarball when you get to the FTP download sites—for example, Orac-1.2.0.tgz or later. Because the Orac program is a collection of relatively short Perl scripts, modules, and text and SQL files, rather than large C libraries, the download should be relatively quick.

Before you install Orac, you will need to preinstall the following on your system (the first three are covered in Chapter 2, the fourth in Chapter 3):

  • Perl 5

  • Perl DBI

  • DBD::Oracle (which requires access to Oracle’s OCI libraries)

  • Perl/Tk

Perl’s operating system independence transfers itself automatically to the Orac program, and the program is easily portable for Oracle DBAs and developers across many different platforms. Developed originally for Oracle 8.0.4 under Solaris, the program works just as well under Linux, Windows, and virtually all other environments where Perl can operate successfully. (Figure 4-1 shows the main Orac login screen under three different operating systems.)

Tip

The single major exception to this OS-independence rule is the Macintosh, as Perl/Tk has not yet been ported to the MacPerl system, although rumors are often floating about that this is being attempted. If the port occurs, it won’t be to the “classic” Macintosh OS, but instead to the BSD Unix-based Mac OS X.For current MacPerl usage, see:

http://www.iis.ee.ethz.ch/~neeri/macintosh/perl.html
http://www.macperl.com
http://www.macinstruct.com/tutorials/macperl/index.shtml

We’ve included instructions in the following sections for installing Orac under the main three operating systems. (We’re hoping that once Linux or any other Perl-friendly OS dominates the palm-held market, all of Perl will swiftly follow and enable Perl/Tk usage on third generation mobile phones et al via web plug-ins.)

Installing Orac on Unix

Once you have the correct Perl installation set up with the required Perl/Tk and Perl DBI modules, the Orac installation should be fairly straightforward under most flavors of Unix. Here we’re using Solaris. The Perl environment was previously set up via a Perl package available from http://www.sunfreeware.com, which is an excellent site for all manner of GNU-related tools. Follow these steps:

  1. Download Orac from one of the CPAN sites listed earlier. Then unpack it:

    $ gzip -d Orac-1.2.0.tgz
    $ tar xvf Orac-1.2.0.tar
    $ cd Orac-1.2.0
    $ ORACLE_HOME=/u01/oracle/8.1.5
    $ export ORACLE_HOME
  2. Run orac_dba.pl, first making sure that the top line of the file has the right Perl string address (e.g., #!/usr/local/bin/perl ):

    $ ./orac_dba.pl
  3. Alternatively, run it with the Perl program directly:

    $ perl orac_dba.pl

That’s about it. Full instructions are also included in the README file.

Orac connecting under Solaris, Linux, and Windows

Figure 4-1. Orac connecting under Solaris, Linux, and Windows

Example 4-1 shows a typical helper script for running Orac. The example assumes that you’ve installed it into a /usr/local/orac directory and that you’re running Oracle 8.1.5.

Example 4-1. orac.sh (Script Itself Is Installed in /usr/local/bin)

#!/bin/sh
ORACLE_HOME=/u01/oracle/8.1.5 ; export ORACLE_HOME # For Perl DBI
ORAC_HOME=$HOME/.orac # Provides customisation for all users
export ORAC_HOME
cd /usr/local/orac/Orac-1.2.0 # Localises logging
perl orac_dba.pl & # Run in background

This is run with:

$ PATH=/usr/local/bin:$PATH ; export PATH
$ orac.sh

In general, all other Unix installations should follow a similar pattern. However, we’ve provided a few additional notes specifically for Linux users. These might be required when an older Perl version has already been prebuilt for you on your Linux distribution (it’s not something you tend to find on Solaris boxes). Note also the setting of the ORAC_HOME environment variable in Example 4-1. We’ll discuss this shortly, following the installation.

Installing Orac on Linux

These notes are adapted from those originally provided by Kevin Kitts, an Oracle DBA from Washington, DC, for Orac users using Red Hat 5.2. They supplement those already provided above for the standard Unix installation:

  1. Get the latest source RPM for Perl and rebuild it beforehand. The Perl version supplied with an older distribution of Linux may not be adequate. The man pages for rpm on your flavor of Linux should explain how to do this, and the rebuild should be straightforward.

  2. Get the latest Perl/Tk and Perl DBI/DBD modules from CPAN and compile them:

    http://www.perl.com/CPAN-local/modules/by-module/Tk
    http://www.perl.com/CPAN-local/modules/by-module/DBI
    http://www.perl.com/CPAN-local/modules/by-module/DBD
  3. Make sure you can get SQL*Plus to work first. If that works, make sure the DBI/DBD make test step passes.

If you complete these steps successfully, you should have no problem running Orac under Linux.

Installing Orac on Windows

Back in the dark ages of ActivePerl 519, another prominent Washington, DC, Oracle DBA, Charles Wolfe, worked out the first installation requirements for Orac on Windows NT. It seemed almost magical to run a Solaris application on Windows NT for the first time. However the latest builds of ActivePerl for Perl 5.6, with the installed packages of Perl DBI and DBD::Oracle (as described in Chapter 2), should make the running of Orac pretty straightforward. Once the required Perl/Tk DBI system is ready, do the following:

  1. Download Orac from one of the CPAN sites listed earlier.

  2. Unpack and install Orac into its own directory via an unzip program such as WinZip (see http://www.winzip.com/ ).

  3. Set up the environment as required for the ORAC_HOME and ORACLE_HOME environment variables. (See the next section for details.) Once you’ve started your target Oracle database, the Orac program should now be ready to run out of the box.

  4. Double-click on the orac_dba.pl icon, and the program should fire right up, as shown in Figure 4-2 (this screen also demonstrates the “one-time-only” initial database configuration).

Double-clicking on the orac_dba.pl icon

Figure 4-2. Double-clicking on the orac_dba.pl icon

Alternatively, you might like to employ the Win32 command file (written by Thomas Lowery) that comes with the Orac distribution:

@echo off
rem
rem Execute orac_dba
rem
start perl -w orac_dba.pl

Customizing ORAC_HOME for multiple users on Unix and Windows

The setting of the $ORAC_HOME (or for Windows users, %ORAC_HOME%) environment variable allows Orac to be used on one machine by many different users, with their own personal customizations stored in their own personalized locations. This capability was originally proposed and then coded by Bruce Albrecht. The crucial piece of Perl code is as follows:

if ($ENV{ORAC_HOME}) # Generally Non-NT Win32
{
   $main::orac_home = $ENV{ORAC_HOME};
}
elsif ($^O =~ /MSWin/ && $ENV{USERPROFILE}) # Generally NT
{
   $main::orac_home = $ENV{USERPROFILE} . "/orac";
}
elsif ($ENV{HOME}) # Generally Unix
{
   $main::orac_home = $ENV{HOME} . "/.orac";
}

Note that Orac checks the handy built-in Perl $^0 operating system variable (which is also known as $OSNAME in other Perl programs) for the value “MSWin”, to check whether or not it’s running on Win32. Customizations (e.g., personalized menus, etc.) can then go to the following:

$ORAC_HOME

If already set

%USERPROFILE%/orac

If on Win32

$HOME/.orac

As the default on a typical Unix system

Some Win32 systems (e.g., Windows NT systems) automatically set up %USERPROFILE% so the default always works, creating the required subdirectories and files even if %ORAC_HOME% is not set up on first use. However, other older Win32 systems do not necessarily employ %USERPROFILE%. You must therefore set up %ORAC_HOME% beforehand in something like the AUTOEXEC.BAT boot file to make sure the Orac program knows where to store personalized profiles. For example:

set ORAC_HOME=C:\Temp\orac

On any system, once this is set up (or the default is taken on Unix or Windows NT), any number of users can use the same Orac Perl script, with their personalized preferences (such as screen color) and unique SQL scripts stored under their own allocated home directories. As noted elsewhere, $ORACLE_HOME must always be set up correctly with any Perl DBI program to make sure the DBD::Oracle driver module gets access to the necessary OCI libraries and/or related DLL files (such as ociw32.dll ).

Using Orac

Now that you’re up and running, let’s take a look at what Orac can do by examining each of its basic menus:

File menu

Provides user customizations and general program information.

Structure menu

Used for current logical and physical setup and to view tables.

Object menu

Provides hierarchical drill-downs needed to generate the specific Data Definition Language (DDL) needed to re-create every object within the database. You can also use this menu to detect invalid PL/SQL database objects and PL/SQL compilation error messages and perform a variety of other tasks.

User menu

Provides reporting scripts that let you find out what users are doing in the database, plus drill-downs on specific process addresses and SIDs.

Lock menu

Generally used in an emergency to find out what’s locking the database—also where, when, how, why, and who’s to blame.[27]

Tune menu

Provides an assortment of scripts used to monitor all aspects of current tuning states within the database (though no time-series tuning is yet available).

SQL menu

Offers direct access to Thomas Lowery’s Orac Shell, which lets you run database transactions directly.

My Tools menu

Provides a way for users to store personalized and favorite Orac SQL scripts within the standard menu structure.

Tip

The Orac program was designed from the outset to cautiously observe the database rather than to change it. Therefore, except for a very small section where updates are performed to take advantage of Oracle’s EXPLAIN PLAN facilities and the Orac Shell module (described later), the main Orac program does not carry out any other database transactions. This basic safety-first philosophy is reflected throughout the entire program’s structure wherever possible.

File Menu

Selecting the File menu gives you several options. The leftmost menu available after you select File (shown on the far left of the Orac Control Panel screen in Figure 4-3) gives you a series of customizable user options. These let you modify the visual environment and provide easy viewing access to the various program and configuration files that make up the system.

File options

Figure 4-3 shows a number of options and also demonstrates the menu tear-off ability, a feature standard across most Tk menu widgets. This enables menu clustering around the main screen for quicker information gathering. The File menu also allows connection to other databases and a straightforward way of exiting the program.

Plain Old Documentation

The Main File Viewer, shown in the lower part of Figure 4-3, can drill up and down the directory structure and allows the user to view source code using either Perl’s POD (Plain Old Documentation) capability or straight flat-file text viewing. Most of the Orac source code makes use of Perl’s self-documenting POD technique for embedding English language comments within the body of the code. Later on, these comments can be extracted by various programs, formatted, and turned into straightforward documentation without the code getting in the way. The Main File Viewer can do this too, accessing the source code and turning it into (we hope) more readable code descriptions.

If you’d prefer to actually read the source code itself, the POD reader can be turned off, and the straight text and source code, whatever it happens to be, becomes visible instead.

Orac’s primary File menu

Figure 4-3. Orac’s primary File menu

Structure Menu

The Structure menu provides a series of options that allow you to view the current logical and physical structure of the database. This includes two basic graphical reports and several textual reports providing information on tablespaces, datafiles, extent sizing, and free space availability within the extents. Several of the reports are shown in Figure 4-4.

Press me

Figure 4-4 also demonstrates another general feature of Orac. Most of the reports available from this and other Orac menus contain an Alice-in-Wonderland-style “press me” button, generally labeled “SQL.” (Notice the icon near the bottom left of the main screen.) If this is clicked, a screen (titled “See SQL” in this figure) usually pops up detailing the exact underlying SQL used to generate the report.

Some of the structural reports available in Orac

Figure 4-4. Some of the structural reports available in Orac

DBA Tables Viewer

You’ll also find the DBA Tables Viewer option available via the Structure Menu. This viewer gives you easy access to all of the information within the general DBA tables. You can navigate as follows:

  1. Select the DBA Tables Viewer option, which brings up a scrollable pick-list of DBA tables.

  2. Double-click on the selected table to bring up a form where you can enter SQL query lines and the row order in which you wish to bring the results back. For example, you may want to see all the rows in DBA_TABLES where the owner is the SYSTEM user, and order this by TABLE_NAME.

  3. Press the “go” button (the drill-down arrow), to display an ordered slider screen, where you can scroll through every row in the table that matches your original query. This screen can be useful for copying information into a tandem SQL*Plus session.

You can view a cut-down version of this process in Figure 4-5.

Stepping through the DBA Tables Viewer screens

Figure 4-5. Stepping through the DBA Tables Viewer screens

Object Menu

The third menu option, the Object menu, contains drill-downs for accessing the SQL required to re-create all of the objects currently existing within the database.

Tables option drill-downs

The Tables Menu, available via the Object Menu, provides a number of options. Double-click down to the main Tables screen, then drill down through the hierarchical lists of schema owners and tables until you reach your target table. Once you’ve selected it, a screen pops up, displaying the DDL text that could be used to re-create the table (since this text is provided within a Perl/Tk text widget, full cut-and-paste text facilities are available). The lower menu bar on the table’s DDL screen also gives you the following options, from left to right:

SQL

Pops up the SQL used to generate the DDL.

Line Writer

Re-creates the DDL, prefixing each line with its respective line number.

Interactive Form

Similar to the DBA Tables Viewer described earlier, this option allows you to query the table and then scroll through the rows of results. Be careful when using this option; it’s not designed for several-thousand-row queries. Although it’s possible to perform such queries, doing so may require a huge amount of memory to produce results, especially if the table is a large one and the query SQL is not specifically tailored for a relatively small result set.

Index Builder

Works out the required sizing for any index required on the table.

Index DDL

Generates the DDL necessary to create the table’s associated indexes.

Free Space Finder

Works out the free space within the data object.

Constraint

Generates the DDL required for all the table’s constraints.

Trigger Finder

Finds any related table triggers.

Comments

If any comments are defined on the table (in DBA_TAB_COMMENTS and DBA_COL_COMMENTS), this button displays the DDL necessary to re-create them.

Figure 4-6 shows a typical subset of the options available in the Tables submenu.

In addition to tables, you can drill down to most of the other Oracle database objects within the various schemas using the options summarized in Table 4-1.

Facilities available through the Tables submenu

Figure 4-6. Facilities available through the Tables submenu

Table 4-1. The Main DDL Drill-Downs Within Orac’s Object Menu

Top-Level Menu

Secondary Menu Options

Description

Data Objects

Tables, Indexes, Views, Sequences, Links, Synonyms, Constraints

Access to DDL generating Oracle objects generally controlled by a particular schema

User Objects

Users, UserGrants, Roles, RoleGrants, Profiles

Access to DDL used to re-create schema owners and associated role and profile permissions

Logical Structures

Tablespace, Rollback

Access to DDL used to re-create logical database structures outside of the general schema owner pattern

PL/SQL

Procedures, PackageHeads, PackageBods, Functions, Triggers, Comments

Grouping of the compiled database objects generally stored within the DBA_SOURCE table

Snapshots

Snapshots, Snapshot Logs

Objects related to replication

The PL/SQL drill-down may be the hierarchical submenu most often used. Its use is demonstrated in Figure 4-7, which also shows an example of a “See SQL” pop-up generated from the menu that accompanies every DDL screen, showing the start of the PL/SQL used to generate the DDL. Note that Perl’s DBD::Oracle module can make use of Oracle-specific anonymous PL/SQL functionality,[28] as in the “See SQL” pop-up in Figure 4-7.

Hierarchical drill-down displaying procedural DDL

Figure 4-7. Hierarchical drill-down displaying procedural DDL

The other facilities available through the Object Menu include tools to help generate scripts to re-create the database in its entirety, including a Server Manager (svrmgrl ) -type script[29] (a typical example of which is displayed in Example 4-2), as well as other tools to debug PL/SQL objects that fail to compile properly.

You will have to do a small amount of customization of the following script, to which we’ve also added some step numbers. (Note the highlighted lines. At the end of Example 4-2, we’ll explain what’s happening with them.)

Example 4-2. Typical Orac Script for Regenerating an Entire Database Structure

rem  ************************************************
rem  crdborcl.sql
rem  ************************************************
rem  Database name        :orcl
rem  Database created     :29-JUL-00
rem  Database log_mode    :NOARCHIVELOG
rem  Database blocksize   :2048 bytes
rem  Database buffers     :100 blocks
rem  Database log_buffers :8192 blocks
rem  Database ifile       :
rem
rem  Note:  Use ALTER SYSTEM BACKUP CONTROLFILE TO TRACE;
rem  to generate a script to create controlfile
rem  and compare it with the output of this script.
rem  Add MAXLOGFILES, MAXDATAFILES, etc. if reqd.
rem  ************************************************

spool crdborcl.lst
connect internal
startup nomount

rem -- please verify/change the following parameters as needed

rem Step 1

CREATE DATABASE "orcl"
  NOARCHIVELOG

  REMOVE=>NB: Make sure NOARCHIVELOG/ARCHIVELOG sorted out    

  /* You may wish to change the following  values,          */
  /* and use values found from a control file backed up     */
  /* to trace.  Alternatively, uncomment these defaults.    */
  /* (MAXLOGFILES and MAXLOGMEMBERS have been selected from   */
  /* v$log, character set from NLS_DATABASE_PARAMETERS.*/

  /* option start:use control file*/

  CHARACTER SET  US7ASCII
  MAXLOGFILES    8
  MAXLOGMEMBERS  2
rem Step 2

  /* MAXDATAFILES   255 */
  /* MAXINSTANCES   1 */
  /* MAXLOGHISTORY  100 */
  /* option end  :use control file*/

  DATAFILE 
    '/u02/sys1orcl.ora' SIZE 40M

  LOGFILE
    GROUP  1 (
    '/u03/log2orcl.ora' 
    ) SIZE 100K
    ,
    GROUP  2 (
    '/u04/log1orcl.ora' 
    ) SIZE 100K
;

rem ----------------------------------------
rem  Need a basic rollback segment before proceeding
rem ----------------------------------------

CREATE ROLLBACK SEGMENT dummy TABLESPACE SYSTEM 
    storage (initial 500K next 500K minextents 2);
ALTER ROLLBACK SEGMENT dummy ONLINE;
commit;
rem ----------------------------------------

rem Create DBA views

@?/rdbms/admin/catalog.sql
commit;

rem ----------------------------------------
rem  Additional Tablespaces
rem ----------------------------------------

CREATE TABLESPACE ROLLBACK_DATA DATAFILE
    '/u02/rbs1orcl.ora' SIZE 2M
 
default storage
 (initial 10K
  next 10K
  pctincrease 0
  minextents 1
  maxextents 121
 ) ;
rem ----------------------------------------

CREATE TABLESPACE TEMPORARY_DATA DATAFILE
    '/u03/tmp1orcl.ora' SIZE 2M
 
default storage
 (initial 10K
  next 10K
  pctincrease 0
  minextents 1
  maxextents 121
 ) ;
rem ----------------------------------------

CREATE TABLESPACE USER_DATA DATAFILE
    '/u04/usr1orcl.ora' SIZE 5M
 
default storage
 (initial 10K
  next 10K
  pctincrease 0
  minextents 1
  maxextents 121
 ) ;

rem ----------------------------------------
rem  Create additional rollback segments in the rollback tablespace
rem ----------------------------------------

CREATE ROLLBACK SEGMENT DUMMY
 TABLESPACE SYSTEM STORAGE
    (initial 100K
 next 100K
 minextents 2
 maxextents 121
);
CREATE PUBLIC ROLLBACK SEGMENT RB1
 TABLESPACE ROLLBACK_DATA STORAGE
    (initial 50K
 next 50K
 minextents 2
 maxextents 121
 optimal 100K
);
CREATE PUBLIC ROLLBACK SEGMENT RB2
 TABLESPACE ROLLBACK_DATA STORAGE
    (initial 50K
 next 50K
 minextents 2
 maxextents 121
 optimal 100K
);
CREATE PUBLIC ROLLBACK SEGMENT RB3
 TABLESPACE ROLLBACK_DATA STORAGE
    (initial 50K
 next 50K
 minextents 2
 maxextents 121
 optimal 100K
);
CREATE PUBLIC ROLLBACK SEGMENT RB4
 TABLESPACE ROLLBACK_DATA STORAGE
    (initial 50K
 next 50K
 minextents 2
 maxextents 121
 optimal 100K
);
ALTER ROLLBACK SEGMENT RB1 ONLINE;
ALTER ROLLBACK SEGMENT RB2 ONLINE;
ALTER ROLLBACK SEGMENT RB3 ONLINE;
ALTER ROLLBACK SEGMENT RB4 ONLINE;

rem  Take the initial rollback segment (dummy) offline

ALTER ROLLBACK SEGMENT dummy OFFLINE;

rem ----------------------------------------

ALTER USER SYS TEMPORARY TABLESPACE SYSTEM;
ALTER USER SYSTEM TEMPORARY TABLESPACE TEMPORARY_DATA DEFAULT TABLESPACE USER_DATA;

rem ----------------------------------------

rem  Run other @?/rdbms/admin required scripts

commit;

@?/rdbms/admin/catproc.sql

rem You may wish to uncomment the following scripts?

rem Step 3

rem @?/rdbms/admin/catparr.sql
rem @?/rdbms/admin/catexp.sql
rem @?/rdbms/admin/catrep.sql
rem @?/rdbms/admin/dbmspool.sql
rem @?/rdbms/admin/utlmontr.sql

commit;

connect system/manager
@?/sqlplus/admin/pupbld.sql
@?/rdbms/admin/catdbsyn.sql

commit;

spool off
exit

rem EOF

Note that you’ll have to make the following modifications to this script:

  1. You must make sure that the ARCHIVELOG situation is fully resolved before running the script.

  2. The MAXDATAFILES, MAXINSTANCES, and MAXLOGHISTORY directions necessary for database creation are held directly within control files. Therefore, the PL/SQL program outputting the Server Manager script (which relies entirely upon the data dictionary) uses defaults that you must uncomment or change, if necessary. To avoid future trace control file trickery adjusting the figure upwards, you might want to make sure that MAXDATAFILES is always set high enough that it won’t ever need to be reset, if this is within your database construction guidelines. (We learned this from the hard knocks school of “mistakes you make only once.”)

  3. After catproc.sql is run near the end of the script, you’ll have to uncomment or add in any other @?/rdbms/admin scripts you may want to run after the initial database creation.

User Menu

The User menu is divided into two main sections. The first offers you ten different reports containing various information on what your users are currently doing with the database. Available submenus include:

User Records
Current Logged on Users
Registered Users on Database
User Activity Summary
User Processes
What SQL Statements are Users Processing?
Any Users Updating on Database?
Any User Processes Performing I/O?
Current Processes
User Access
Roles on Database
Profiles on Database
Quotas

Typical of the SQL statements driving these reports is the code shown in Example 4-3.

Example 4-3. SQL Behind the Current Logged on Users Report

/* Thanks to Andre Seesink for Sid,Serial change to ease */
/* session control */
select s.username "User", s.osuser "OS User",
       s.sid||','||s.serial# "Sid,Serial",           
       decode(s.type, 'USER', 'User', 'BACKGROUND', 
              'Backgd', s.type) "Type",
       decode(s.status,'INACTIVE','Inact ' || 
                                  round((s.last_call_et/60),0) || 
                                  ' min', 
                       'ACTIVE', 'Active',
              s.status) "Status",     
       to_char(s.logon_time,'dd/mm hh24:mi') "Logged On",
       p.spid "Spid",
       s.program "Program", s.module "Module",
       s.server "Server", s.machine "Machine",   s.terminal "Terminal",
       decode(s.command, 0,'',                 1,'Create Table',
                         2,'Insert',           3,'Select',
                         4,'Create Cluster',   5,'Alter Cluster',
                         6,'Update',           7,'Delete',
                         8,'Drop',             9,'Create Index',
                         10,'Drop Index',      11,'Alter Index',
                         12,'Drop Table',      15,'Alter Table',
                         17,'Grant',           18,'Revoke',
                         19,'Create Synonym',  20,'Drop Synonym',
                         21,'Create View',     22,'Drop View',
                         26,'Lock Table',
                         28,'Rename',          29,'Comment',
                         30,'Audit',           31,'Noaudit',
                         32,'Cre Ext Data',    33,'Drop Ext Dat',
                         34,'Create Data',     35,'Alter Data',
                         36,'Create Rollback Segment',
                         37,'Alter Rollback Segment',
                         38,'Drop Rollback Segment',
                         39,'Create Tablespace',
                         40,'Alter Tablespace',
                         41,'Drop Tablespace',
                         42,'Alter Session',   43,'Alter User',
                         44,'Commit',          45,'Rollback',
                         46,'Save Point',      47,'PL/SQL',
                         to_char(command))     "Command Type",
       decode(s.lockwait,'','','Yes') "Lock Wait?"
from   v$session s, v$process p
where  s.paddr = p.addr
order by 1, 2, 3, 4, 5

As the previous example clearly demonstrates, Orac is not driven by polymorphic 23rd-century science. However, it is nice not to have to type this sort of thing in too often or to need to remember which afiedt.buf file you last saw it in (and on which host).

Sids and addresses

The second half of the User Menu gives you two pop-ups with which you can interrogate various Sids and addresses, particularly those of spinning processes or any other processes taking up more than their fair share of CPU time. You may decide that such processes are due for a merciful license-to-kill 007 command. (This part of the User Menu was originally written after a request from a senior sysadmin/DBA within Oracle Corporation, who we hope is still using it.) You can see these two covert partners in action in Figure 4-8.

Options to isolate and then kill spinning processes with Orac

Figure 4-8. Options to isolate and then kill spinning processes with Orac

Lock Menu

A number of helpful reporting scripts gathered under the Lock menu allow you to check various locking scenarios, particularly in emergency situations. These scripts include the following:

Locks Currently Held
Who’s holding back Whom
Who’s accessing which objects?
Rollback locks?
Top ORACLE Wait events
Summary of Session Waits & CPU

The use of a specialized Lock Menu was originally suggested by Tim Bunce. The most critical of the submenus available through the Lock Menu is perhaps “Who’s holding back Whom?” The driving script for this report is shown below:

select substr(s1.username,1,12) "Wait User",
substr(s1.osuser,1,8) "OS User",
s1.serial# "Ser#",
substr(to_char(w.sid),1,5) "Sid",
P1.spid "Pid",
'=>' "=>",
substr(s2.username,1,12) "Hold User",
substr(s2.osuser,1,8) "OS User",
s2.serial# "Ser#",
substr(to_char(h.sid),1,5) "Sid",
P2.spid "Pid"
from v$process P1,v$process P2,
v$session S1,v$session S2,
v$lock w,v$lock h
where h.lmode is not null
and w.request is not null
and h.lmode != 0
and w.request != 0
and w.type (+) = h.type
and w.id1 (+) = h.id1
and w.id2 (+) = h.id2
and w.sid = S1.sid (+)
and h.sid = S2.sid (+)
and S1.paddr = P1.addr (+)
and S2.paddr = P2.addr (+)

Every once in a while, with some applications (OLTP applications, in particular), a single user might lock one row on a table, which then blocks every other client. If this user then leaves his client up while he goes for lunch or home for the day, this situation can quickly become a serious problem, especially on a production database.

In normal situations, the driving script shown in the previous section reports no rows. However, if you suspect that a lock situation has occurred, running the report above should reveal it. To demonstrate that case, we’ll open a SQL*Plus session and update one row with the following SQL (pressing the Return key, but without committing the update):

SQL> select * from dept;

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       40 OPERATIONS     BOSTON

SQL> update dept set dname = 'ACCOUNTING' where deptno = 10;

1 row updated.

SQL>

We then open another SQL*Plus session and attempt the same update:

SQL> update dept set dname = 'ACCOUNTING' where deptno = 10;

When you press the Return keyin this case, however, you will not get the “1 row updated” message; the program will sit there waiting for the first session to commit before it too can carry on (i.e., the row has been locked by the first session). When this occurs, the Orac program will report the situation as in Figure 4-9. As well as providing a basic report, the program also creates a drill-down pick-list within a Perl/Tk text widget that can be used to gain further kill information to help unlock the situation (no prisoners taken in this data shop, we think you’ll agree!).

Clients blocking each other with locked rows

Figure 4-9. Clients blocking each other with locked rows

Tune Menu

There are many different tuning scripts embedded within the Orac program and available from the Tune menu. Most of these scripts were added to the program as a result of user requests for extended functionality with SQL. Many scripts were, in fact, supplied by the users themselves!

Reporting options

Table 4-2 summarizes the options available through the Tune Menu.

A pair of graphical tuning reports

Figure 4-10. A pair of graphical tuning reports

Table 4-2. Reporting Options Within Orac’s Tune Menu

Top-Level Options

Secondary Options

Hit Ratios

Version 1, Version 2 (various reports on hit ratios et al, displayed in Figure 4-10)

SQL Browser and Explain Plan

The ability to browse through the SQL cache and use the EXPLAIN PLAN tuning facilities (covered in more depth in the following section)

Shared Pool and SQL

Pool Statements, High Disk Reads, All Pool Statements, Pool Fragmentation?, Main SGA Stats, Fuller SGA Stats Info

Rollback

Rollback Statistics, Rollback Sizings, Current States

Parameters

NLS Parameters, Database Info, Version Info, Show Parameters

Mts

Mts User Session Current and Max Memory (Figure 4-11), Mts Busy Time of the Dispatchers, Mts Wait Time for the Dispatcher Queues, Mts Wait Time for a Server, Total Session UGA, Total Session UGA Max

Database Writer

File I/O (also seen in Figure 4-10), ORACLE Session IO (this option also has a rough-and-ready facility to rapidly repeat the report in a 1-second loop, to enable quick visual monitoring), DBWR Monitor, DBWR LRU Latches

Log Writer

LGWR Monitor, LGWR Redo Buffer Latches

DBWR & LGWR

DBWR & LGWR Waits Monitor

Sorts

Sort Monitor, Identifying Sort Users

Latches

Current Latch Wait Ratios, Latch Waiters

Tablespaces

Tablespace Fragmentation, Tablespace Space Shortages

Data Objects

Tables, Indexes, Views, Sequences, Links, Synonyms, Constraints

Multi-threaded server (MTS) reporting

Figure 4-11. Multi-threaded server (MTS) reporting

SQL Browser/Explain Plan menu option

Following a suggestion from Guy Harrison’s tuning book about SQL browsing tools, this option browses through all of the SQL currently parsed within the database library cache via the V$SQLAREA data dictionary view.

Tip

To get the full Explain Plan capability available via this menu option, the $ORACLE_HOME/rdbms/admin/utlxplan.sql script must already have been run by your Orac login user in order to create the necessary PLAN_TABLE. (You may still use the SQL Browser functionality even if PLAN_TABLE has not yet been created, however.)

Merged in with the SQL Browser, the Explain Plan option is only available when looking at cached SQL created originally by the database user logged in via Orac (that is, if you’re the SYSTEM user, you can only “Explain” the SYSTEM user’s SQL). However, if you clear the screen with the eraser button, you can enter any new SQL directly and explain it then and there. To demonstrate the combined Browser/Explain functionality, we’ll run a simple SQL statement as our SYSTEM user:

SQL> select count(*) from dba_tables;

 COUNT(*)
---------
      129

SQL>

We can now scroll through the library cache with the Browser slider and find the appropriate SQL. Once we’ve found it, we press the spot marked “X” to generate the execution plan for this particular SQL statement (Figure 4-12).

The Browser facility to track down and explain SQL

Figure 4-12. The Browser facility to track down and explain SQL

Alternatively, you can clear the SQL text with the eraser button and then enter your own SQL directly before interrogating the execution plan, as shown in Figure 4-13.

Explaining SQL directly via the “X” button

Figure 4-13. Explaining SQL directly via the “X” button

SQL Menu

The SQL menu allows you to invoke the Orac Shell program and perform a number of other SQL-related functions. Orac Shell was developed by Thomas Lowery, who is also a major contributor to the Perl DBI database driver scene (DBD::Ado, etc.). He originally slotted this module directly into the main Orac structure as a GUI form of Perl’s dbish program. Orac Shell immediately became one of the best features of the program, and its second major functional area (the first, of course, is central DBA interrogation). The third functional area is the program’s web interface, which we’ll describe at the end of the Orac section later in the chapter.

The Orac Shell interface

As its name suggests, Orac Shell is an interactive shell-like program for interfacing directly with the database and running transactions in a manner similar to SQL*Plus. Although it runs in a window that’s separate from the main Orac application, it is often run in tandem with the main window.

Part of Orac Shell’s widely interactive use is demonstrated within Figure 4-14. Here we’ve employed the file selection facility within Orac Shell to pick up the second SQL plug-in file used to fill the SQL Browser feature described earlier. We’ve then executed it to generate the raw results that are employed by Orac to fill the Browser slider in Figure 4-13. Much more can be achieved through the use of Orac Shell. It’s such an interactive tool that we recommend you download it and try it out for yourself, rather than have us try to explain it step by step.

Orac Shell in action

Figure 4-14. Orac Shell in action

Transaction control

Orac Shell is the main place within the Orac program where transactions can take place. You can set whether Perl DBI’s AutoCommit facility is On or Off by clicking on a button at the right-hand bottom edge of the main Orac Shell screen. A red button indicates that AutoCommit is Off, a green button that it is On.

My Tools Menu

Suggested originally by Jared Still, Orac’s last main menu option adds a personalized feature. The My Tools menu lets you add your own buttons to the menus to allow you to run your own customized SQL statements. To set up this option, follow these steps, which are also fully described within the Help section of the My Tools menu option:

  1. Add a cascade menu to the My Tools menu.

  2. Add a button to the cascade menu.

  3. Add the SQL to the button you wish to run.

Once the SQL has been added, you can then run it immediately, just like any other Orac report. This is demonstrated in Figure 4-15, where we’ve attached a piece of SQL to a new button and cascade (you can give these cascades and buttons any names you like (we’ve named ours “tom” and “jerry”).

As soon as it’s saved, the SQL is available immediately on the fly using Perl’s circle-squaring eval( ) capability. The report appears via the main program menu interface as a standard option under the My Tools menu, with the “See SQL” functionality also built in. The cascade-button-SQL combination is also stored directly under the $ORAC_HOME directory destination, as described earlier. It remains invisible to other users running this installation of Orac, but it will persistently re-appear each time you run the program (thanks to Bruce Albrecht’s creation of multiuser functionality within the program). The cascades, buttons, and accompanying SQL are also fully editable. Incidentally, you may notice in Figure 4-15 that the generated report is automatically formatted into neat columns with no manual intervention. This is the result of some pretty clever DBI coding by Kevin Brannen, who also created the Informix port of Orac.

Adding a SQL button via the My Tools menu option

Figure 4-15. Adding a SQL button via the My Tools menu option

Icon Bar

The icon bar stretching across the top of the main Orac control panel accesses programs unavailable from the menus and also provides shortcut access to regularly used menu options. (See the bar just beneath the list of menus in Figure 4-3.)

Reconnect

Brings up the main database login screen.

Database Monitor

Provides access to the database monitor.

Font Selector

Allows the display fonts to be configured to any available on the machine.

Print Selection

Offers a limited printing service.

Clear Screen

Clears the main control panel screen. Not normally necessary unless manual screen clearance is set, as described below, to enable many reports to be viewed simultaneously.

Auto Screen Clearance

Reports usually clear the screen automatically before they print out. This feature switches between automatic clearance and manual clearance, which requires the eraser button.

Main File Viewer

Allows drill-down access to every file making up the Orac program, including source code files, SQL plug-ins, and everything else (including the image files in the .. /img directory).

Orac Home Viewer

Allows access to files stored separately in your personal $ORAC_HOME directory, which may be located on a different directory path from the Orac program directory.

Documentation

Provides access to the development documentation accompanying the program.

Help

Provides help files containing configuration information necessary to set up menus, SQL files, and other parts of the system.

The Print Selection and Database Monitor buttons, described in the following sections, are perhaps the two most interesting options for future development.

Print Selection button

Once the report has appeared on the main control panel report screen, you can press the print icon. It’s then transformed into a formatted Perl/Tk canvas widget, which pops up separately on the workstation screen. This window requires you to complete the following tasks:

  1. Set the paper size and preferred portrait/landscape orientation.

  2. Press the PS button to create a PostScript file in the $ORAC_HOME directory or supply an operating system command to send the Perl/Tk canvas directly to a networked PostScript printer. The Help information available via the Print Selection screen should give you some basic details on how to do this on various operating systems; however, printing is not the smoothest facility within the Orac program and could do with some improvement (perhaps linking it more easily to Russell Lang’s GSview program?).

  3. If the PostScript file is created, you should be able to use any standard PostScript application to render the Perl/Tk canvas containing the report, as in Figure 4-16.

Converting an Orac report into a PostScript output file

Figure 4-16. Converting an Orac report into a PostScript output file

To learn more about open PostScript applications, check out the following excellent web site for Ghostscript, Ghostview, and GSview (written by Russell Lang):

http://www.cs.wisc.edu/~ghost/

Database Monitor button

The Database Monitor facility is an experimental part of the Orac program; however, you may wish to make some use of it or possibly expand it to meet your requirements. The ideas behind it are heavily “borrowed” from the Karma program (coincidentally originated by another of your authors, Sean Hull, and described in Chapter 6). The Karma program is a far more advanced web database monitoring tool, and its web basis is perhaps a more natural home for such a background monitor. However, if all you require is fairly simple monitoring, Orac’s Database Monitor might do the trick.

Warning

The Database Monitor was developed under Solaris 2.6 and has been reported as also working effectively under Win32. However, there was an Oracle OCI bug under Linux that caused disconnection problems for the program. This bug was reported and we hope it will be fixed by Oracle in subsequent versions of Oracle for Linux.

In Figure 4-17, we have set up three databases to be checked by the monitoring program. The program can then be left to run in the background, rechecking the database in a configurable period of 15 seconds to 24 hours. Various red and yellow warning flags are used to indicate particular problems with a target database. You can press these drillable flags for further information. In the following example, we check for the percentage of sorts in memory as opposed to those on disk:

Y2KDEV memsorts flag

Red flag given by less than   : 90
Yellow flag given by less than: 95
Last value found              : 95.77

/* This finds out the percentage of sorts occurring in memory */
/* Thanks to Duncan Lawie */
select round((sum( decode( name, 'sorts (memory)', value, 0 ) )
                           / (
              sum( decode( name, 'sorts (memory)', value, 0 ) )  +
              sum( decode( name, 'sorts (disk)', value, 0 ) )
                              ) * 100),2)
from v$sysstat

There are currently nine monitoring reports (detailed in Table 4-3) that come pre-installed with the Orac program. There are full configuration instructions for adding many further checks, depending on what you require, with the basic rule being that SQL monitoring plug-ins must report a figure that can be broken down into thresholds. The higher the figure, the better the situation:

  • If a figure stays above a particular good threshold, the flag stays green.

  • If it drops below this threshold, but stays above danger, it goes to yellow alert.

  • If it drops below the danger threshold, the flag goes to red alert.

Here is another example of SQL used to monitor the rollback situation as follows:

select 100.00 - 
(round((sum(waits) / (sum(gets) + .00000001)) * 100,2))
from v$rollstat

You can make up any number of these kinds of checks, which can be easily configured and added into the monitoring loop.

Orac’s Database Monitor in action

Figure 4-17. Orac’s Database Monitor in action

Table 4-3. Pre-Installed Monitoring Scripts Under Database Monitor

Flag

Monitoring Report

redo

Checks the Redo Logs via the V$LOG database view

roll

Checks Rollback Stats in V$ROLLSTAT

w2w

Checks the Willing-to-Wait Stats via Latches

tbsp

Monitors Tablespaces filling up through DBA_DATA_FILES and DBA_SEGMENTS

slow

A rough indicator of slow SQL detected through V$SQLAREA

dchr

Dictionary Cache Hit Ratio (Row Cache)

lchr

Library Cache Hit Ratio

bchr

Buffer Cache Hit Ratio

sort

Percentage of sorts occurring in memory

Running Orac over the Web Using Perlplus

By using Frank Holtry’s Perlplus plug-in, which we described in Chapter 3, you can set up Orac to run over the Web. Once you’ve got Perlplus set up (as described in Chapter 3), follow the instructions provided with the Orac program in the .. /help/WebPlugin.txt file for running it over the Web. An outline of these instructions is provided below (note that here we assume the use of Apache and Orac-1.2.0):

  1. Unpack your Orac tarball distribution file in Apache’s .. /cgi-bin directory.

  2. Rename Orac-1.2.0/orac_dba.pl to Orac-1.2.0/orac_dba.ppl.

  3. Go to the .. /pplug directory.

  4. Edit the orac.html file by renaming the Orac-x.y.z string as follows:

    <embed src="/cgi-bin/Orac-1.2.0/orac_dba.ppl"
    enctype="application/x-perlplus">
  5. Move orac.html to your Apache .. /htdocs directory.

  6. Edit the perlplus-secure.cgi file by changing the URL_ROOT string to your appropriate host—the one you compiled the Netscape plug-in with, using the Makefile SECURE_CGI directive. For our example, we changed this to the following:

    $URL_ROOT="http://127.0.0.1/cgi-bin";
  7. Move perlplus-secure.cgi directly into Apache’s .. /cgi-bin (making sure it’s executable).

  8. Copy Orac-1.2.0/img/splash.gif to your Apache .. /icons directory (it will be accessed here by orac.html later on).

  9. Under your Apache configuration file, httpd.conf, add the following section (and adapt the Oracle DBA user and ORACLE_HOME value, as highlighted):

    # OracWeb General Environment Variables
    SetEnv ORACWEB_SWITCH 1
    SetEnv ORACWEB_DB_TYPE Oracle
    SetEnv ORACWEB_DBA_USER SYSTEM
    SetEnv ORACWEB_BACKGROUND_COL steelblue2
    SetEnv ORACWEB_STANDARD_DB pppep
    SetEnv ORACWEB_FOREGROUND_COL black
    SetEnv ORACWEB_ENTRY_COL white
    SetEnv ORACWEB_FONT_FAMILY courier
    SetEnv ORACWEB_FONT_SIZE 10
    SetEnv ORACWEB_FONT_WEIGHT normal
    SetEnv ORACWEB_FONT_SLANT roman
    
    # OracWeb Specific Oracle Required Variable
    SetEnv ORACLE_HOME / u01/app/oracle/product/8.1.5
  10. Shut down your Apache httpd program and restart with the new configurations. Then point your browser at the installed Orac program to receive the login dialog over the Web (see Figure 4-18).

The Perlplus plug-in at work with Orac

Figure 4-18. The Perlplus plug-in at work with Orac

This technique can easily be adapted to any Perl/Tk Oracle database programs you write yourself, providing you with a moveable feast of thin Perl GUI applications you won’t have to carry around anymore.

Extending Orac

There are plenty of expansion opportunities for the Orac program, particularly in terms of adding new Oracle capabilities. Orac has stagnated for a while because it relied on the efforts of two or three key programmers who took time out to work on other projects (you’re holding the results of one of those efforts in your hands). To help break this logjam, we’re going to try to exploit the fluid development model of CVS. If you’d be interested in helping out with this, check out the following SourceForge PerlDBAdmin “parent” project. Further extensions to the Orac program will be developed here before being released on CPAN:

http://sourceforge.net/projects/perldbadmin/

These extensions will include the use of Richard Sutherland’s DDL-Oracle Perl module for Oracle developers and DBAs, and Dean Arnold’s DBD-Chart graphical facilities (similar to Tcl’s BLT, discussed later in this chapter). These two modules are available at the following web sites:

http://www.perl.com/CPAN-local/modules/by-authors/id/R/RV/RVSUTHERL
http://home.earthlink.net/~darnold/dbdchart/


[27] Sorry, this should read, " . . . which part of the overall system to attach responsibility to for providing us with a proactive challenge in improving our customer service level interface.” (We generally find it easier, though, just to blame someone, as long as it’s not us!)

[28] Remember from Chapter 2 that the Perl DBI architecture is not universally restrictive. If different database types can provide specific extensions beyond standard SQL/92 usage, the individual drivers (such as DBD::Oracle) can provide access to these extensions via the DBI func( ) method.

[29] Yes, we know that Server Manager is a cipher in the bit bucket of history, but we’ll always love it.

Get Oracle and Open Source now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.