There are lots of day-to-day tasks that DBAs and developersencounter

Tag : functionality fabric
There are lots of day-to-day tasks that DBAs and developersencounter such as needing to debug or instrument some code, orwanting to get the SQL to recreate tables and indexes. As it turnsout, Oracle actually supplies quite a large complement of pl/sqlstored procedures, packages, and functions, which can help withsome of these day-to-day needs. Some are commonly known, whileothers 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 ourDBA or Developer toolbox. Instrumenting & Tracing PL/SQL Code

Writing Oracle stored procedures can be a challenging process attimes, especially when you're faced with wanting feedback with anongoing job. Sure you can write back to the sqlplus session withdbms_output.put_line, however between buffering issues (withsqlplus configuration) and so on, this sometimes is not sufficient. Enter the dbms_application_info package. This is a great Oraclesupplied package that allows you to tell Oracle about the goings-oninside your code.

As a first step, use dbms_application_info.set_module to tellOracle that your stored procedure is starting. We'll show you howthis helps later.

Then later on in your code, where you're iterating through aparticularly large loop, say to load some data into a table addthis:

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

The fun doesn't stop there. There is another view calledv$session_longops for use by developers as well. We populate thisview using dbms_application_set_session_longops. It's like theprevious set_client_info but on steroids. You can even use the"sofar" and "totalwork" fields to help Oracleestimate total time. Oracle will then populate the time_remainingfield. Good stuff. This is like easily building a progress barfor your own pl/sql stored procedures and jobs.

While you're busy digging around, and working withdbms_application_info, it would also be good to look at dbms_alert. This package allows you to register and wait for events in yourpl/sql code. Generating Create Statements

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

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

However to get a lot of the other objects, the export utility isn'tas helpful. Enter Oracle's supplied dbms_metadata package. Here'san example:

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

You can further control the output formatting of get_ddl using thesession_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 isnot wildly fast and efficient, it can do redefinitions while theobjects are online, making the timeframe of the redefinitionprocess 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. Noteyou can also use this to change column structures or tablespaces aswell, 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 smallperiod where your table is offline. Another Trail of Database Activity

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

First, be sure your database is running in archivelog mode. That'sobviously a requirement. The basic steps to use it would besomething like this:

1. Switch to a new logfile.

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

3. Switch to a new logfile again.

4. In your archivelog destination directory, find the newestlogfile. Let's say it's called sean_1234.arc.

5. Tell logminer about the file:

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

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

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

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