perl.com: The Fusion of Perl and Oracle

The Fusion of Perl and Oracle
by Andy Duncan | Pages: 1, 2

The Perl DBA Toolkit

To take advantage of this synergy between Perl and Oracle in our toolkit, we've blended the two streams together into four key DBA areas. These are password serving, the performance of routine DBA tasks, the monitoring of the database, and the building of a database repository for informational time-traveling.

The completed Perl DBA Toolkit scripts described below in Table 2 allow it to work securely around a network without clear passwords being passed around, thereby enabling you to have one toolkit point of control for all of your databases, no matter where they're located.

Table 2. Password serving

Script	Description
`pwd.pl`	Password server daemon that encrypts passwords via a TCP socket; works remotely with the other Perl scripts via the toolkit module set.
`pwc.pl`	Client that remotely retrieves encrypted passwords from the password server, easing the secure database access overhead imposed by other scripts.
`pwd_service.pl`	Installs the password server as a daemon (on Unix) or service (on Win32).

The scripts in Table 3 perform a wide variety of DBA tasks, including the creation of new users from the command line, the creation of new users via duplicated accounts, and the creation of multiple accounts with automatically mailed passwords. They also cover the maintenance of indexes, the killing of sniped database sessions, the management of extent usage, and the extraction of DDL and data for SQL*Loader transfer.

Table 3. Routine database administration

Script	Description
`ddl_oracle.pl`	Generates the DDL necessary to recreate schemas, tables, indexes, views, PL/SQL, materialized views, and other objects.
`sqlunldr.pl`	Dumps entire schemas to comma-delimited files and generates the SQLLoader scripts necessary to reload them. Also dumps LONG RAW and BLOB objects, converting them to hex format via the Oracle HEX_TO_RAW function in the SQLLoader control file in order to convert the data back into binary format.
`create_user.pl`	Creates Oracle users from the command line. You can create a user and assign passwords, tablespaces, and privileges, all with one easy command-line call. Best of all, you can use this script to pre-configure different groups of runtime privileges.
`drop_user.pl`	Drops a database user by first dropping all of their tables and indexes before dropping the account. Doing so avoids most of the resource- intensive SQL recursion incurred when dropping an account containing many tables and indexes.
`dup_user.pl`	Duplicates an account, with the source user's system privileges, object privileges, roles, and quotas assigned directly to the target user.
`mucr8.pl`	When creating a large number of users, this utility creates them all with a single operation. Configurable permissions are granted, and the passwords automatically generated get emailed back to the new account owners.
`kss.pl`	Kills sniped sessions (which are lapsed sessions on busy databases consuming unnecessary memory resource).
`kss_NT.pl`	Win32 version of kss.pl.
`kss_service.pl`	Used to create an appropriate snipe killing service on Win32.
`idxr.pl`	Determines if an index should be rebuilt and, if so, rebuilds it. Checks on a per-schema basis, and is configured to check indexes based on days since the index was last analyzed. A configurable time limit is imposed, which allows index rebuilds to fit within a predefined time schedule.
`maxext.pl`	Monitors the size and number of extents in tables and indexes. If they're nearing a maximum allowed or if the object will be unable to extend because of limited free space, it notifies the DBA. This script is most useful for databases that use dictionary-managed extents.

Table 4 lists our remote monitoring scripts, which help to maximize the availability of your databases by alerting you to both error conditions reported in the Oracle alert log and to problems with database connectivity. Some of them can even phone you up.

Table 4. Database monitoring

Script	Description
`chkalert.pl`	Daemon that monitors Oracle alert logs for error conditions and notifies the DBA via either email messages or pager calls. Oracle's alert.log files contain important error messages as well as a log of database startup and shutdown messages.
`chkalert_NT.pl`	Win32 version of chkalert.pl.
`chkalert_service.pl`	Utility script that creates a Win32 service for chkalert_NT.pl.
`dbup.pl`	Working alongside chkalert.pl, a highly configurable database connectivity monitor that checks to see if databases are up and available.
`dbup_NT.pl`	Win32 version of dbup.pl.
`dbup_service.pl`	Creates the Win32 service for dbup_NT.pl.
`dbignore.pl`	Utility script used with dbup.pl to temporarily disable connectivity checks on an individual database (e.g., while maintenance is being performed).

Table 5 summarizes what we've called repository scripts. These compare different database schema versions over time, detecting database changes (official or otherwise). They also store SQL execution plans within a library cache to allow comparison between current execution plans and plans previously collected; this way, the scripts can report on changed execution plans and the reasons behind the changes.

Table 5. Repository and DDL "time travel"

Script	Description
`baseline.pl`	Creates the baseline for the PDBA repository, establishes "time travel" control of DDL (Data Definition Language), and stores the entire database structural change record across time boundaries.
`spdrvr.pl`	Perl driver for SQL*Plus that reports on information created by baseline.pl.
`sxp.pl`	Collects and stores SQL statements from the data dictionary and generates accompanying execution plans for later comparison with other plans.
`sxpcmp.pl`	Examines the current SQL statements, generating execution plans.
`sxprpt.pl`	Generates reports based on the stored SQL and execution plans.

Perl Philosophy

There is something else that makes Perl different from other computer languages, which may move it closer towards the rugged individualists of American business; it has three major philosophical virtues. And so does Objectivism. Coincidence? I'll leave it to you to decide whether the two sets of virtues below are in any way related:

The three great virtues of the Perl programmer, as originally defined by Larry Wall, are: Laziness, the quality that makes you write labor-saving programs to increase productivity; Impatience, the injustice you feel when applications are inefficient, which makes you write clever programs to anticipate your needs, and Hubris, the pride that makes you create great solutions, which others will say only good things about.

The three cardinal virtues of Objectivist ethics are: Purpose, the recognition that productive work is how man's mind sustains his life; Reason, the use of rationality as the only guide to considered action and wealth creation, and Self-Esteem, the recognition that as man is a being of self-made wealth, it is this route through which he can acquire the pride of the self-made soul.

Ayn Rand's life works were about wealth creation, and the individual. However they were not just about money. They were also about any form of wealth or ideas creation, where wealth is the product of one person's mind. And with the Perl Artistic License copyright, it is clear the wealth of Perl was created and is owned by Larry Wall. Although he decides to give it away, this is entirely his right. Perl even possesses its own culture of freedom, reflected in the Perl catch phrase TMTOWTDI--There's More Than One Way To Do It-- and its own free-trade area, the Comprehensive Perl Archive Network, or CPAN. This is where thousands of worldwide Perl developers swap modules, in exchange for respect from the rest of Perl society. Indeed, the respect that Larry Wall has duly earned is priceless. He may remain unable to buy MiG jets with it, but it still makes him a major keynote speaker at technology conferences, just like our other shrugging Atlas in this article, Larry Ellison.

The Sign of the Dollar

The Two Larrys are both free men of the mind who live on two sides of the same coin. They have created between them two of the world's great American productive inventions, Perl and Oracle, which work well together because they arise from the same intellectual substrate. Without the pioneering work of both Wall and Ellison, the world would be both spiritually and materially poorer. And here's a final thought. For those who've read Atlas Shrugged, you'll know the basic value symbol of the free men of the mind; it was a golden dollar symbol. And by a bizarre twist in our tale, every Perl script ever written is full of basic value variables, each preceded by a dollar symbol. Another coincidence? Possibly. But more bizarrely, if you take a vinyl copy of John Lennon's 1970s anthem, "Imagine," and play it backward, it says "Perl for Oracle DBAs." No kidding.

(If you'd like to learn more about Ayn Rand's work, check out this Web site.)