DBA Insider - Useful PL/SQL Packages

Thursday Jul 10th 2008 by Sean Hull

Oracle supplies quite a large complement of pl/sql stored procedures, packages, and functions, which can help with some of a DBA's day-to-day needs. This article looks at some of the useful ones that we can add to our DBA or Developer toolbox.

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

Writing 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 is:

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 offline.  

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);

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

In addition, if logminer is not already installed, you'll find the install script in $ORACLE_HOME/rdbms/admin/dbmslm.sql.


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

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved