Featured Database Articles

Open-source Technologies for Oracle DBAs and Developers

As a DBA, you have lots of
software, scripts, and applications, which help you with your day-to-day
operations. Some you've written yourself, and some may be commercial solutions
providing a lot of feature rich assistance to you.

However, there are also
times when a script or community created tool really fits the bill. Fortunately,
the open-source community around databases, and Oracle specifically, has
literally exploded in the last couple of years. Oracle has a page on OTN
detailing all of the projects they're involved in, and various community
projects. Furthermore, a search on SourceForge yields 52 pages of results,
over 500 projects mention Oracle specifically!

That's a lot to sift
through, so we're going to present some of the biggest projects here in this
two part series.

Libraries and language interfaces

If you're embarking on
software development, and at the stage of choosing a language, and development
platform, the first thing you'll need to know is what type of database
interface is supported. Fortunately, there is a plethora of choices.

If you're scripting in Perl,
for purposes of monitoring, or with mod_perl doing web development, look no
further than dbd::Oracle, the DBI driver for Oracle. It provides all of the
important features and keeps you from having to worry about the intricacies of
OCI. Connect, parse, execute, and fetch to your hearts content.

While you're on the Perl
bandwagon, you'll definitely want to get a hold of this Apache module. Rather
than running your code as a CGI, mod_perl brings the Perl interpreter into the
Apache memory space, speeding up executing, caching code, and providing
persistence. All good stuff you'll want to take advantage of.

Tracing + profiling tools

10046 is Oracle's extended
tracing facility and is very useful for diagnosing and tracing session activity
to pinpoint problems. etprof can be used to scan, profile, and format these
output files for easier reading.

This library provides a
consistent way to add logging messages to your pl/sql code for later code
tracing. Much like adding printf's to your C code, here you can add
"entering procedure X" messages at appropriate points in your code,
which can help you debug problems later.

Hotsos is big on the scene
in using trace data to isolate and pinpoint bottlenecks in the database. With
their own pioneering method, this library installs packages in the target
database for defining, measuring and utilizing SQL trace data.

Developer tools

There are quite a few tools
that do similar things to this one, but I'd say it's still worth a look. This
one includes explain plan, syntax highlighting, statement tracing, and a schema
browser. It's a GTK based tool, so will probably build easily on most Linux distros.
It's also been updated quite recently, so it is actively being developed.

As a consultant, I've walked
into countless sites, and had to review applications, and underlying database
schemas. Often there are disparate logins, with overlapping
schemas, and no one is quite sure what was originally created to do what. You
have to tiptoe around, so as not to interrupt anything on a production system
that is already well-oiled and working, but at the same time you want to
cleanup as much as possible, and eliminate pieces that aren't necessary
anymore. This SchemaDiff tool can fill such a niche. It looks at two Oracle
db schemas and will tell you the differences in terms of columns, and datatypes.
This can be a handy tool indeed. Caveats to keep in mind though are that it hasn't
been updated recently, so may not support 10g well. As always, buyer (of free
software!) beware. So review, and test, and modify as needed.

Stay tuned for part 2 of
this article, when we'll discuss administration, security, monitoring, and benchmarking
tools.