Perl for Oracle DBAs: Perl Scripts, Applications & Tips for Database Administrators

by Andy Duncan, Jared Still

Overview

Perl is a very powerful tool for Oracle database administrators, but too few DBAs realize how helpful Perl can be in managing, monitoring, and tuning Oracle databases. Whether you're responsible for Oracle9i, Oracle8i, or earlier databases, you'll find Perl an invaluable addition to your database administration arsenal.You don't need to be a Perl expert to use the excellent applications and scripts described in Perl for Oracle DBAs. The book explains what you need to know about Perl, provides a wealth of ready-to-use scripts developed especially for Oracle DBAs, and suggests many resources for further exploration. The book covers:

• The Perl language -- an introduction to Perl, its rich history and culture, and its extensive text processing and data transformation capabilities.
• The Perl/Oracle architecture -- Detailed information about Perl DBI, DBD::Oracle, the Oracle Call Interface (OCI), Oracle::OCI, extproc_perl, and mod_perl, the modules that allow Perl programs to communicate with Oracle databases.
• Perl applications for Oracle DBAs -- Profiles of the best Perl open source applications available for use and customization by Oracle DBAs: Perl/Tk, OraExplain, StatsView, Orac, DDL::Oracle, SchemaDiff, Senora, DBD::Chart, SchemaView-Plus, Oracletool, Karma, Embperl, and Mason.
• The Perl Database Administration (PDBA) Toolkit -- a comprehensive suite of specialized, ready-to-use scripts designed to help Oracle DBAs perform both routine and special-purpose administrative tasks: monitoring the Oracle alert log and databases, creating and managing Oracle user accounts, maintaining indexes and extents, extracting DDL and data, troubleshooting and tuning database problems, and much more.

The book also explains how Oracle DBAs and developers can extend the toolkit and solve their own database administration problems using Perl.

Product Details

ISBN-13:	9781449390914
Publisher:	O'Reilly Media, Incorporated
Publication date:	08/19/2002
Sold by:	Barnes & Noble
Format:	eBook
Pages:	622
File size:	5 MB

About the Author

Andy Duncan is the coauthor of Oracle & Open Source (O'Reilly, 2001), as well as Perl for Oracle DBAs (O'Reilly, 2002). The first book arose after Andy's creation, in 1998, of the Orac Perl/Tk tool for Oracle DBAs. Since then, he has worked mainly as an independent development and DBA consultant, and has counted both Oracle Corporation and Sun Microsystems among his long-term clients. In addition to performing Oracle, Perl, and Java consultancy work, Andy teaches as a senior instructor for Learning Tree International, covering both introductory and advanced Perl courses. He lives in Oxfordshire, England.

Jared Still has been an Oracle DBA in health insurance and manufacturing environments since 1994 (and version 7.0.13 of Oracle). He first began dabbling with Perl in 1993 and it was love at first sight. Perl became an integral part of his Oracle toolkit when it was used to rapidly prototype and implement complex reporting based on data in Oracle databases. Jared has been working with databases of various ilks since 1988, and along the way has also worked as a Unix system administrator. When not riding herd on the databases at the OK Corral, he likes to spend time tinkering on his car, fly fishing, or sitting on the deck in the backyard at his home doing absolutely nothing. He can be reached at [e-mail].

Table of Contents

	Preface	ix
Part I.	Introducing Perl for Oracle
1.	Perl Meets Oracle	3
	What is Perl?	4
	The Perl/Oracle Architecture	10
	Perl for Oracle DBAs	15
	For Further Information	24
2.	Installing Perl	28
	Installing Perl	28
	Installing Perl DBI	38
	Running Perl DBI	48
	Installing Cygwin	51
Part II.	Extending Perl
3.	Perl GUI Extensions	65
	Perl/Tk	66
	OraExplain	72
	StatsView	74
	Orac	82
	DDL::Oracle	84
	SchemaDiff	90
	Senora	92
	DBD::Chart	95
	SchemaView-Plus	104
	Open Source Perl IDEs	107
	Open Source Perl GUI Debuggers	108
4.	Perl Web Extensions	110
	Apache	111
	Oracletool	118
	Karma	127
5.	Embedding Perl into Apache with mod_perl	136
	mod_perl	137
	Apache::OWA	155
6.	Embedded Perl Web Scripting	161
	Embperl	162
	Mason	169
7.	Invoking the Oracle Call Interface with Oracle::OCI	178
	What is Oracle::OCI?	179
	What Is OCI?	180
	Installing Oracle::OCI	185
	Coding with Oracle::OCI	190
	The Future of Oracle::OCI	198
8.	Embedding Perl into PL/SQL	201
	Communication Between Perl and PL/SQL	202
	Embedding Perl Within Oracle	204
Part III.	The Perl DBA Toolkit
9.	Installing the PDBA Toolkit	227
	Introducing the PDBA Toolkit	228
	Toolkit Modules	232
	Installing the PDBA Toolkit for Unix	257
	Installing the PDBA Toolkit for Win32	261
	Configuring the PDBA Toolkit	265
10.	Performing Routine DBA Tasks with the PDBA Toolkit	279
	Managing User Accounts	280
	Maintaining Indexes	297
	Killing Sniped Sessions	305
	Managing Extent Usage	312
	Extracting DDL and Data	315
11.	Monitoring the Database with the PDBA Toolkit	328
	Monitoring the Alert Log	328
	Monitoring the Databases	342
12.	Building a Database Repository with the PDBA Toolkit	366
	Repository Table Structure	367
	Installing the Repository	369
	Loading the Repository with Data	374
	Reporting on Database Changes	377
	Reporting on SQL Execution Plans	392
13.	Extending the PDBA Toolkit	403
	Modifying a Script in the Toolkit	404
	Modifying a Module in the Toolkit	421
Part IV.	Appendixes
A.	The Essential Guide to Perl	439
B.	The Essential Guide to Perl DBI	459
C.	The Essential Guide to Regular Expressions	482
D.	The Essential Guide to Perl Data Munging	521
	Index	575