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.
dbms_application_info.set_module (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:
dbms_redefinition.can_redef_table
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:
dbms_redefinition.start_redef_table
4. Finish the redefinition process:
dbms_redefinition.finish_redef_table
It is only really during step four that you might have a small period where your table is offline. |
