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.
DBD::Oracle (http://search.cpan.org/dist/DBD-Oracle/)
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.
mod_perl (http://perl.apache.org/)
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.
Apache::OWA (http://sourceforge.net/projects/owa/)
The parallel of mod_perl for
pl/sql developers is this great Apache module. Wonder how Apache got into the
middle tier of Fusion? Here’s a hint. See also the old and much deprecated
OWS Killer.
cx_Oracle (http://www.python.net/crew/atuining/cx_Oracle/)
Prefer to do your
programming in Python. You’re covered here as well.
If you plan to develop an
application in C++ there are a number of wrapper libraries to give you OCI
functionality. Take your pick from the ones below.
tinyOcci (http://sourceforge.net/projects/tinyocci)
ORA++ OCI Library (http://mywebpages.comcast.net/jimcainadmin/orapp/)
OCIPlus (http://ociplus.sourceforge.net/)
oci-wrappers (http://sourceforge.net/projects/oci-wrappers)
Oracle has been big on the
PHP bandwagon now for the last few years. There is even a special PHP Dev
Center over at OTN: http://www.oracle.com/technology/tech/php/index.html
So there is lots of support
in the PHP space for Oracle.
OCIPHPSess (http://sourceforge.net/projects/ociphpsess/)
This library helps you build
scalable PHP code for Oracle by giving you shared session functionality.
Tracing + profiling tools
etprof (http://sourceforge.net/projects/etprof)
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.
Orate (http://orate.gnuadvantage.com/
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 Oracle Instrumentation Library (http://sourceforge.net/projects/hotsos-ilo/)
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
GTKO – (http://gsqlr2.sourceforge.net/)
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.
SchemaDiff – (http://sourceforge.net/projects/schemadiff/)
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.
