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.
dbms_application_info.set_module (module_name=>'sean_example', action_name=>'starting loop');
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);
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.
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
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
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;
further control the output formatting of get_ddl using the session_transform
function. Check the documentation for details.
Partitioning a Table
ever been faced with the need to partition a table, you’ve likely pondered a
few potential solutions.
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
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
4. Finish the
It is only
really during step four that you might have a small period where your table is
Another Trail of Database Activity
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.
sure your database is running in archivelog mode. That’s obviously a
requirement. The basic steps to use it would be something like this:
to a new logfile.
SQL> alter system switch logfile;
some sql statements, create a table, insert into a table, etc.
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
if logminer is not already installed, you’ll find the install script in
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