DBA Insider – Useful PL/SQL Packages

There are
lots of day-to-day tasks that DBAs and developers encounter such as needing to
debug or instrument some code, or wanting to get the SQL to recreate
tables and indexes.  As it turns out, Oracle actually supplies quite a
large complement of pl/sql stored procedures, packages, and functions, which
can help with some of these day-to-day needs.  Some are commonly known,
while others are a bit more obscure.  But don’t let that hold you
back.  While digging around, perhaps you’ll discover a new gem or
two.  Here we’ll look at some of the useful ones that we can add to our
DBA or Developer toolbox.

Instrumenting & Tracing PL/SQL Code

Oracle stored procedures can be a challenging process at times, especially when
you’re faced with wanting feedback with an ongoing job.  Sure you can
write back to the sqlplus session with dbms_output.put_line, however between
buffering issues (with sqlplus configuration) and so on, this sometimes is not
sufficient.  Enter the dbms_application_info package.  This is a great
Oracle supplied package that allows you to tell Oracle about the goings-on
inside your code.  

As a first
step, use dbms_application_info.set_module to tell Oracle that your stored
procedure is starting.  We’ll show you how this helps later.

  (module_name=>'sean_example', action_name=>'starting loop');

Then later
on in your code, where you’re iterating through a particularly large loop, say
to load some data into a table add this:

dbms_application_info.set_client_info ('inserting row ' || rowcount);

If you
query v$session while your procedure is running, you’ll see details in the
module, and client_info columns.  

The fun doesn’t
stop there.  There is another view called v$session_longops for use by
developers as well.  We populate this view using dbms_application_set_session_longops. 
It’s like the previous set_client_info but on steroids.    You can
even use the "sofar" and "totalwork" fields to help Oracle
estimate total time.  Oracle will then populate the time_remaining
field.  Good stuff.  This is like easily building a progress bar for
your own pl/sql stored procedures and jobs.  

While you’re
busy digging around, and working with dbms_application_info, it would also be
good to look at dbms_alert.  This package allows you to register and wait
for events in your pl/sql code.  

Generating Create Statements

Ever wanted
to generate the create statements for the objects in your database?  We’ve
all had need to do this from time to time.  It provides a method of
documenting what’s in your database, and allows you to script rebuilding
objects as well.

If you just
want index objects, you might do something like this:

exp userid=sean/sean owner=sean file=sean.dmp
imp userid=sean/sean indexfile=sean.sql file=sean.dmp

However to
get a lot of the other objects, the export utility isn’t as helpful. 
Enter Oracle’s supplied dbms_metadata package.  Here’s an example:

SQL> select dbms_metadata.get_ddl ('TABLE', 's1', 'SEAN') from dual;

It’s not at
first obvious how you might use this to get the DDL for a whole schema–but you
can do it.  It makes sense once you see it, so here you go:

SQL> select dbms_metadata.get_ddl (object_type, object_name, user) from user_objects;

You can
further control the output formatting of get_ddl using the session_transform
function.  Check the documentation for details.

Partitioning a Table

If you’ve
ever been faced with the need to partition a table, you’ve likely pondered a
few potential solutions.  

One method
is to use the dbms_redefinition package.  Although it is not wildly fast
and efficient, it can do redefinitions while the objects are online, making the
timeframe of the redefinition process less of an issue.  The basic process

1. Verify
that the table is a candidate for redefinition:


2. Create a
new table with the partitioning layout you want.  Note you can also use
this to change column structures or tablespaces as well, if that is necessary.

3. Start the
redefinition process:


4. Finish the
redefinition process:


It is only
really during step four that you might have a small period where your table is

Another Trail of Database Activity

As we
explained above, there are times when you need to generate DDL for objects in
your database, and dbms_metadata is perfect for that job.  However, what
if you want to see the ongoing DDL and DML activity in your database? 
Enter Oracle’s logminer utility.  Logminer will generate a trail of sql statements,
which can be used for auditing, rebuilding objects, or a myriad of other uses.

First, be
sure your database is running in archivelog mode.  That’s obviously a
requirement.  The basic steps to use it would be something like this:

1. Switch
to a new logfile.

SQL> alter system switch logfile;

2. Issue
some sql statements, create a table, insert into a table, etc.

3. Switch
to a new logfile again.

SQL> alter system switch logfile;

4. In your archivelog destination
directory, find the newest logfile.  Let’s say it’s called sean_1234.arc.

5. Tell logminer
about the file:

dbms_logmnr.add_logfile(logfilename => '/path/to/my/sean_1234.arc');

6. Start logminer
for the past 5 minutes (24 hours, 60 minutes):

dbms_logmnr.start_logmnr (starttime => sysdate - 5 * 1/24/60, endtime => sysdate, options=>dbms_logmnr.dict_from_online_catalog);

you can specify online redolog files, and not specify a timeframe, for ongoing

In addition,
if logminer is not already installed, you’ll find the install script in


The Oracle
environment is a lot to learn and manage.  There are loads of tasks that
we need to perform everyday.  Luckily, as it turns out, Oracle has built
some powerful tools and packages to help us with some of those more common
needs, and it’s only a matter of knowing which ones, and where to look.   We’ve
attempted to highlight some of those more useful pl/sql stored procedures,
packages, and functions.  Hopefully this will help you in your day-to-day
job, but also perhaps it will kindle an interest in these more obscure
Oracle-supplied procedures, and functions.  What they lack in
documentation, they often make up for in power, and sheer problem-solving.

See All Articles by Columnist Sean Hull

Sean Hull
Sean Hull
Sean Hull is a business and technology consultant, author and speaker. He is the founder and senior consultant at Heavyweight Internet Group in New York and has worked as a database consultant and expert for ten years. He is the author of "Oracle and Open Source" on O'Reilly & Associates and his articles have appeared in Oracle's own OTN website, DBA Zine, ChangeThis.com, and many others. Using techniques, technologies, and perspectives discussed in the book, articles, and seminars, he has taken his career into exciting areas. He has served as Technology Consultant, Project Manager, CEO, and mentor with a background in programming, systems integration & administration, project development and management. He can be reached at shull@iheavy.com or visit http://www.iheavy.com for more info about consulting services.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles