There are many books on Perl and many books on Oracle, but until now there have been no books dedicated to describing the relationship between these two popular technologies. Our aim is to bridge the gap between the world’s leading data-processing language and the world’s leading database. The Perl language was created with the goal of making “the easy things easy and the hard things possible.” Oracle’s ever-expanding purpose is to provide a complete database environment for the entire interconnected world. This mission makes Oracle a tough taskmaster — and thus an environment ideally suited to Perl, because being an Oracle database administrator is one of the toughest jobs around.
Oracle DBAs need enormous intelligence, infinite patience, and considerable courage. We think they also need Perl. The Perl open source language is a many-splendored thing; you can write scripts with it, develop GUIs with it, create web sites with it, generate XML with it — and you can probably hang your towels from it! Perl fills data warehouses and runs on virtually every operating system around. Perl is the toolkit without limitations, the salvation of your 24x7 lifestyle. If Gandalf the Wizard were to choose a scripting language, he would choose Perl.
Our mission in this book is to show you how Perl can revolutionize your life as an Oracle DBA. We’ll focus on four aspects of the Perl/Oracle connection:
- The Perl language itself
We’ll introduce you to the Perl language, with its rich history and culture, present some language basics, and shine some light on CPAN, the Comprehensive Perl Archive Network, the main distribution point for Perl modules.
- The Perl/Oracle architecture
We’ll introduce you to the modules that allow Perl programs to communicate with Oracle databases.
- Perl applications for Oracle DBAs
We’ll profile about a dozen of the best ready-made applications written in Perl for use by Oracle DBAs. These provide help with database administration, monitoring, tuning, and daily troubleshooting. They also provide components you can use in your own Perl scripts, should you choose to add a little program development to your daily DBA routine.
- The PDBA Toolkit
We’ll present, for your enjoyment, an Oracle database administration toolkit we’ve written ourselves. The Perl Database Administration (PDBA) Toolkit contains nearly 100 Perl scripts and reusable modules that perform operations ranging from creating new Oracle users to monitoring the Oracle alert log to building a repository of database information for use in tuning and troubleshooting. All of this code is available on the O’Reilly web site (http://www.oreilly.com/catalog/oracleperl/pdbatoolkit).
If you’re an Oracle DBA who is trying desperately to keep up with the daily demands of administering, monitoring, and tuning your Oracle databases, this book is for you. We are Oracle DBAs ourselves, and we know how difficult your job can be. This book provides both information and software that we hope will ease your burden.
Although the primary audience is Oracle DBAs, many DBAs end up being devel-opers from time to time, and there is no better language than Perl for writing those quick scripts. Anyone doing Perl development will find Chapter 7, Chapter 8, and Chapter 13, as well as the appendixes, particularly useful.
This book assumes no prior experience with Perl, though some knowledge of the language will help you get the most out of the material presented here. Although the book’s appendixes explore the essential syntax of Perl, Perl DBI (DataBase Interface), Perl’s regular expressions, and Perl’s data-munging modules, a complete Perl tutorial is beyond our scope. Our goal here is to jump-start your explorations into the intersection where Perl meets Oracle. We’ll provide plenty of suggestions for where to go next on your journey.
Both Perl and Oracle run on virtually every hardware platform and operating system. To demonstrate this ubiquity, we’ve used a wide variety of OS platforms and Oracle versions in the preparation of this book. Oracle versions range from Oracle7.3 through Oracle9i. OS platforms include Linux Red Hat 6, Linux SuSE 7.3, Solaris 8, Windows NT 4, Windows 2000, and others. We’ve focused on Unix and Win32 operating systems, but we’ve also included specific installation instructions for particular operating system variants when necessary.
Against this irresistible surge of platforms, our immovable rock is the Perl version we’ve used on all of these operating systems. Perl 5.6.1 was the latest stable Perl release available as we wrote this book and developed the toolkit software. We also used the most current stable version of Perl DBI, Version 1.20, in conjunction with Perl DBI’s Oracle-specific driver module, DBD::Oracle (DataBase Driver for Oracle), Version 1.12.
Tip
By the time you read this book, it’s possible that the latest stable versions on the CPAN web site will have been upgraded, particularly if Perl itself is upgraded to Perl6, which was under development as we wrote this book. We’ll be updating our toolkit as an open source project in order to cope with any such Perl enhancements.
This book is divided into four parts:
Chapter 1 introduces the Perl language and explains why it is such a helpful language for Oracle database administrators. It also provides an overview of the main components of the Perl/Oracle architecture.
Chapter 2 describes how to install Perl on Unix and Win32 systems. It also describes how to install Cygwin, a Unix-like development environment you can install on your Win32 machine.
Chapter 3 describes Perl/Tk, an extensive GUI-based toolkit for Perl, as well as a number of applications that provide Oracle DBAs with graphically oriented tools for performing database administration. These include OraExplain, StatsView, Orac, DDL::Oracle, SchemaDiff, Senora, DBD::Chart, SchemaView-Plus, and a variety of Perl GUI integrated development environments (IDEs) and debuggers.
Chapter 4 discusses the relationship between Perl and the Apache web server, and focuses on two Oracle applications that use a web browser as their user interface: Oracletool and Karma.
Chapter 5 explains how the use of Apache’s mod_perl module can greatly improve the performance of Perl web-based CGI (Common Gateway Interface) scripts used with Oracle. This chapter also covers several related Apache modules: Apache::Registry, Apache::DBI, and Apache::OWA (used to connect mod_perl to Oracle’s PL/SQL Web Toolkit).
Chapter 6 describes two applications, Embperl and Mason, that demonstrate the advantages of embedded scripting, a method that allows Perl code to be embedded within web pages. These tools provide a mechanism for filling your production web pages with dynamic Oracle data and creating your own Oracle web tools, while separating content from design issues.
Chapter 7 covers Oracle::OCI, a Perl module that provides a more extensive interface to Oracle’s Oracle Call Interface (OCI) than is possible with Perl DBI.
Chapter 8 discusses extproc_perl, a Perl module that communicates with the Oracle PL/SQL language’s external procedure C library system (known as EXTPROC). This module and the others described here allow Perl code to be embedded directly in PL/SQL programs.
Chapter 9 introduces the components of the Perl Database Administration Toolkit (PDBA) and explains how to install it and build the toolkit’s password server.
Chapter 10 describes the toolkit’s Perl scripts that help DBAs perform day-to-day administration. We’ll cover managing user accounts, maintaining indexes, killing sniped sessions, managing extent usage, and extracting DDL (Data Definition Language) and data.
Chapter 11 describes the toolkit’s Perl scripts that can be used to monitor both the Oracle alert log (containing database error and status messages) and the connectivity of the databases.
Chapter 12 describes the toolkit’s Perl scripts that allow you to build a repository in which to store information about the many changes made to an Oracle database’s tables, indexes, roles, schemas, and other objects.
Chapter 13 provides information that will be helpful if you decide to modify any of the scripts or modules in the toolkit. We’ll take a detailed look inside one of the toolkit’s scripts and modules and illustrate how you can change it to suit your specific database administration needs.
Appendix A summarizes basic Perl syntax, including object-oriented features.
Appendix B presents the main Perl DBI application programming interface (API) functions.
Appendix C describes the basics of regular expressions (regexes), patterns of literals and metacharacters used extensively by Perl for pattern matching.
Appendix D summarizes the Perl data-munging modules that are helpful in formatting and transforming data for data warehouses and other such Oracle applications; it includes sections on numeric, date, conversion, and XML modules.
The full source code for the PDBA Toolkit is available on the O’Reilly web site at:
| http://www.oreilly.com/catalog/oracleperl/pdbatoolkit |
The toolkit is a fully open source-compliant project, and we welcome all contributions to extend it. In line with the OSI (Open Source Initiative) guidelines,[1] the PDBA Toolkit is freely available for download over the Internet under the Perl Artistic License.[2] We’ll try our best to keep this code up to date as Perl and the many modules described in this book are upgraded. Our goal is to have you be able to download the latest and greatest version of the toolkit at all times as we seek constantly to improve it.
In addition to the toolkit programs, we have also provided a large number of stand-alone Perl programs in the book and on our site. We’ll also try to keep this code up to date and available for download at the O’Reilly web page cited earlier.
The following typographical conventions are used in this book:
- Italic
Used for filenames, directory names, and URLs. It is also used for emphasis and for the first use of a technical term.
-
Constant width Used for code examples.
-
Constant width bold Used occasionally in code examples to highlight statements being discussed.
We have tested and verified the information in this book and in the source code to the best of our ability, but given the number of tools described in this book and the rapid pace of technological change, you may find that features have changed or that we have made mistakes. If so, please notify us by writing to:
| O’Reilly & Associates |
| 1005 Gravenstein Highway |
| Sebastopol, CA 95472 |
| (800) 998-9938 (in the U.S. or Canada) |
| (707) 829-0515 (international or local) |
| (707) 829-0104 (fax) |
You can also send messages electronically. To be put on the mailing list or request a catalog, send email to:
| info@oreilly.com |
To ask technical questions or comment on the book, send email to:
| bookquestions@oreilly.com |
We have a web site for this book where you can find updated links to Perl and Oracle software discussed in this book, along with errata (previously reported errors and corrections are available for public view there). You can access this page at:
| http://www.oreilly.com/catalog/perloracledba |
To download the PDBA Toolkit, you can go directly to:
| http://www.oreilly.com/catalog/oracleperl/pdbatoolkit |
For more information about this book and others, see the O’Reilly web site:
| http://www.oreilly.com |
As you might expect, a tremendous number of people from the Perl and Oracle communities have helped us put this book together, including many of the creators of the actual tools discussed here. We cannot thank them enough (although we’ll do our best). We also are very grateful to the whole O’Reilly editorial and production team.
First of all I have to thank my wife and beg her not to throw me out of the house for having deserted our family (my son Ross, four-and-a-half, and daughter Ellie, two-and-a-bit) for the last six months while completing this magnum opus. I had thought my previous book, Oracle & Open Source, written with Sean Hull, was as tough as it would get on a family, but I was proved wrong. Daddy could often only be located by following the trail of pizza crumbs and Dr. Pepper cans from the fridge to the darkest recesses of the house where he hid, tip-tapping away on a variety of workstations. But all will be redeemed if you get as much out of this book as I have, in its long-fingered probings into the darkest recesses of Perl for Oracle DBAs. My wife Sue has been the magnificent rock upon which I built my effort, and without her and our beautiful children, you can just take everything else and give it all away. I am forever in their debt.
As with all O’Reilly books, this has also been an immense collaborative effort involving more than just the writing team of myself, Jared, and our omniscient editor, Debby Russell. I would like to thank Tim Bunce, the father of Perl DBI, for his help and support over the past five years, and everyone else who has helped us achieve our goal of producing this book, particularly our technical reviewers who did such a magnificent job under tight deadline pressure: Stephen Andert, Tim Bunce, Ben Evans, Lance Hollman, Thomas A. Lowery, Ilya Sterin, and Richard Sutherland.
Many others also helped us ensure that this book was both as accurate and as up-to-date as we could possibly make it. My deepest thanks to all of them: Dean Arnold, Jeffrey W. Baker, Doug Bloebaum, Ronald Bourret, Thomas Boutell, Hans-Bernhard Broeker, Alan Burlison, Damian Conway, Martin Drautzburg, Thomas Eibner, Kim Fowler, Andy Gillen, Lars Hecking, Russell Herbert, Roger Hipperson, Dan Horne, Jeff Horwitz, Sean Hull, Randy Kobes, Robert Lupton, Doug MacEachern, Edmund Mergl, Julian Moss, Alistair Orchard, Ian Pilgrim, Alan Ranger, Eric S. Raymond, Gerald Richter, Dave Rolsky, Dave Roth, Nick Semenov, Steve Shaw, Jonathan Swartz, Svante Sörmark, Jesse Reed Vincent, Adam vonNieda, and Ken Williams.
My future bar bill is now immense, possessing gravitational mass in its own right. However, before I finish, I have to thank our editor Debby Russell, who has done so much to support us and sculpt our natural techno-speak into what we hope you’ll find to be an invaluable guiding light towards the Perl and Oracle mithril of a deep subterranean world. She also brought this book down from being a ridiculous 1,000-page cave troll into the more-or-less manageable wood-elf you hold in your hands without losing a single important point. And finally, I have to thank David Gray, for his album, White Ladder, without which the completion of my half of this book would have been simply impossible, Dr. Pepper or no Dr. Pepper.
I must first thank my own personal goddess, my wife, Carla. Although her husband spent many early mornings, late nights, weekends, and even several days of vacation time over a period of months, sequestered away in his hobbit hole of a computer warren producing code and text, she remained supportive and understanding.
Next I must thank my coauthor Andy. A true human dynamo, Andy was a whirlwind of activity while we collaborated on this project, and a source of inspiration on several occasions. He and Debby Russell are responsible for bringing our massive first draft down to a manageable size.
Andy and I both owe a deep debt of gratitude to everyone who has assisted us in the creation of this book. Andy has already mentioned those who have given us direct help in this project. I would like to reiterate our thanks to Tim Bunce for his outstanding work on the Perl DBI and DBD::Oracle modules. I would take it even further and thank Larry Wall for the inspired moment when he first decided to create Perl.
Hats off to the entire open source community as well. Without the dedication and hard work of so many talented individuals, computing would be far less interesting. Trite? Maybe, but nonetheless sincere.
I’m grateful to my friends who listened patiently when they asked what “the book” was about when they learned I was working on one, even though they had never heard of Perl or Oracle, and for the fact they are still my friends, in spite of missed social occasions.
Finally, I thank my parents, Jerry and Betty. They had no idea that their combined genes would create progeny with a predilection for Perl, Unix, Linux, databases, and a fascination with ones and zeros arranged in meaningful patterns. Thanks Dad, thanks Mom.
[1] The Open Source Initiative (OSI) is a “non-profit corporation dedicated to managing and promoting the Open Source Definition for the good of the community” (seehttp://www.opensource.org/).
[2] The Perl Artistic License “state(s) the conditions under which a package may be copied, such that the copyright holder maintains some semblance of artistic control over the development of the package, while giving the users of the package the right to use and distribute the package in a more-or-less customary fashion, plus the right to make reasonable modifications” (see http://www.perl.com/pub/a/language/misc/Artistic.html).
Get Perl for Oracle DBAs 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.
