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:
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 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.
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.)
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:
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
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
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.
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 DBIORAC_HOME=$HOME/.orac# Provides customisation for all users export ORAC_HOME cd /usr/local/orac/Orac-1.2.0 # Localises logging perlorac_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.
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:
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
rpmon your flavor of Linux should explain how to do this, and the rebuild should be straightforward.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 Make sure you can get SQL*Plus to work first. If that works, make sure the DBI/DBD
make teststep passes.
If you complete these steps successfully, you should have no problem running Orac under Linux.
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:
Download Orac from one of the CPAN sites listed earlier.
Unpack and install Orac into its own directory via an unzip program such as WinZip (see http://www.winzip.com/ ).
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.
Double-click on the
orac_dba.plicon, and the program should fire right up, as shown in Figure 4-2 (this screen also demonstrates the “one-time-only” initial database configuration).
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
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 ).
Now that you’re up and running, let’s take a look at what Orac can do by examining each of its basic menus:
- 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.
- 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]
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.
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.
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.
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.
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.
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.
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:
Select the DBA Tables Viewer option, which brings up a scrollable pick-list of DBA tables.
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.
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.
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.
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:
- 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 DDL
Generates the DDL necessary to create the table’s associated indexes.
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.
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.
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 neededrem Step 1CREATE DATABASE "orcl"NOARCHIVELOGREMOVE=>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 2rem 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 3rem @?/rdbms/admin/catparr.sqlrem @?/rdbms/admin/catexp.sqlrem @?/rdbms/admin/catrep.sqlrem @?/rdbms/admin/dbmspool.sqlrem @?/rdbms/admin/utlmontr.sqlcommit; 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:
You must make sure that the ARCHIVELOG situation is fully resolved before running the script.
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.”)
After
catproc.sqlis run near the end of the script, you’ll have to uncomment or add in any other@?/rdbms/adminscripts you may want to run after the initial database creation.
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, 5As 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.buffile you last saw it in (and on which host).
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.
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!).
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!
Table 4-2 summarizes the options available through the Tune Menu.
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 |
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).
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.
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.
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 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.
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:
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.
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
.. /imgdirectory).
- Orac Home Viewer
Allows access to files stored separately in your personal
$ORAC_HOMEdirectory, 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.
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:
Set the paper size and preferred portrait/landscape orientation.
Press the PS button to create a PostScript file in the
$ORAC_HOMEdirectory 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?).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.
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/ |
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$sysstatThere 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.
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 |
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):
Unpack your Orac tarball distribution file in Apache’s
.. /cgi-bindirectory.Rename
Orac-1.2.0/orac_dba.pltoOrac-1.2.0/orac_dba.ppl.Go to the
.. /pplugdirectory.Edit the
orac.htmlfile by renaming theOrac-x.y.zstring as follows:<embed src="/cgi-bin/Orac-1.2.0/orac_dba.ppl" enctype="application/x-perlplus">
Move
orac.htmlto your Apache.. /htdocsdirectory.Edit the
perlplus-secure.cgifile 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";
Move
perlplus-secure.cgidirectly into Apache’s.. /cgi-bin(making sure it’s executable).Copy
Orac-1.2.0/img/splash.gifto your Apache.. /iconsdirectory (it will be accessed here byorac.htmllater on).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 SYSTEMSetEnv 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 VariableSetEnv ORACLE_HOME / u01/app/oracle/product/8.1.5Shut down your Apache
httpdprogram 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).
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.
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.
