Oracle 10g PL/SQL Enhancements, Part 1: DBMS_CRYPTO, DBMS_MONITOR, and DBMS_SERVER_ALERT
May 26, 2005
Synopsis. With the addition of over fifty new PL/SQL packages as well as enhancements to a majority of existing DBMS packages, Oracle 10g has added a plethora of new tools to every Oracle DBA's tool belt. This article - the first is a series -- explores some of the more intriguing packages and illustrates their usefulness in real-world situations.
During my career in information technology, I have found that there are at least two or three different but workable solutions to any problem, and given enough time, a developer will choose the solution that is most flexible and elegant. Unfortunately, experience has also proven that for every good solution, at least two non-optimal solutions exist. The difference between elegant code and poorly written code often comes down to a developer's decision to attack a problem by following the solution down a "blind alley" until deadline pressure leaves no choice but to implement now, and clean up the mess later.
As a DBA, I really enjoy stepping out of the "nuts and bolts" database administration role occasionally and digging into some new code to appreciate elegant solutions to problems that developers face every day. Oracle 10g's new and enhanced series of DBMS packages provided me with some food for thought, and here are some of my discoveries.
Encrypting and Decrypting Data Effectively: DBMS_CRYPTO
One of the more time-consuming and frustrating efforts in which I participated involved creating and testing an encryption scheme for sensitive client information stored in one of our production databases. One of our clients had insisted upon an MD5-compliant strategy, including a hash-and-salt methodology. At the time, the only tool available was DBMS_OBFUSCATION_TOOLKIT, and it took our team of DBAs and developers several days to shake out all of the eccentricities.
Oracle 10g has come to the rescue with the new DBMS_CRYPTO package. DBMS_CRYPTO makes short work of encrypting and decrypting data using many of the more complex cryptographic algorithms available today:
Whereas DBMS_OBFUSCATION_TOOLKIT only supported the RAW and VARCHAR2 datatypes, DBMS_CRYPTO now supports CLOB and BLOB datatypes in addition to the RAW datatype. A set of overloaded functions and procedures are provided for encryption, decryption, and hashing methods; the functions are intended to be used for working with VARCHAR2 and RAW datatypes, while the procedures are intended for handling the various LOB datatypes.
I will illustrate the strengths of these new features with a few relatively simple examples of encryption and decryption for two sensitive pieces of information, a Social Security Number and a Credit Card Number. As shown in Listing 1.1, I have constructed a new package, HR.PKG_SENSITIVE_DATA, with two public functions, ENCRYPTOR and DECRYPTOR, which will handle the encryption and decryption of these input data strings. Note that I have used another new utility package, UTL_I18N, to handle the translation of character data to raw data and vice versa.
Listing 1.2 shows a simple example that utilizes these functions to encrypt and decrypt Social Security Numbers and credit card numbers using the Secured Hash Algorithm (SHA1) in concert with the Electronic Code Book cipher and zero-padding. I created a small table and stored the encrypted values within it using the ENCRYPTOR function, then retrieved and decrypted these encrypted values using the DECRYPTOR function.
Improved SQL and Session Tracing with DBMS_MONITOR
Oracle 10g provides a major improvement over the various tracing and monitoring tools that were somewhat scattered about the database (e.g. DBMS_STANDARD, DBMS_UTILITY). The new DBMS_MONITOR package provides tools for starting and halting SQL tracing and statistics gathering of client sessions. DBMS_MONITOR also permits tracing or statistics gathering at a much more granular level across all the sessions that invoke an application. It is also now possible to limit tracing or statistics gathering to only selected modules or activities within those applications.
To illustrate, I have created a new table, SH.ORDERS, and a simple package, SH.PKG_ORDERS, that has three primary modules: ADDNEWORDER, UPDATEORDERSTATUS, and DISPLAYORDERS. These modules either add new orders, update existing order status, or query order entry information from the database. As shown in Listing 1.3, I have set up notification for each different action that this package can perform via calls to DBMS_APPLICATION_INFO within the new package.
It is now a relatively simple matter to track statistics or trace the SQL being executed within each module of this package. Listing 1.4 illustrates several examples of how to use the appropriate DBMS_MONITOR procedures to monitor statistics and activity for a specific module and action, or to deactivate an existing monitoring activity. The new V$SERV_MOD_ACT_STATS dynamic view will contain the results from all active statistics gathering sessions, as shown in Listing 1.5.
Staying Alert for Danger: DBMS_SERVER_ALERT
Before Oracle 10g, I had to rely on third-party tools or Oracle Enterprise Manager to monitor the database to insure that critical performance thresholds were not being violated. While these tools are certainly adequate for the job, I had also hoped for methods that might give me better control over thresholds, including full control from within the database itself.
My wishes were granted with the new DBMS_SERVER_ALERT package, which Oracle 10g uses to integrate database-monitoring services within the database itself. DBMS_SERVER_ALERT allows an Oracle DBA to set up thresholds for several dozen predefined critical database performance "choke points." When a threshold reaches its defined peak, DBMS_SERVER_ALERT also permits retrieval of that information, and allows expanded formatting of the threshold messages returned. Configuring thresholds with DBMS_SERVER_ALERT can be handled via the new Oracle 10g Enterprise Manager User interface, but since it is also a standard package, I can also use it independently of that GUI tool.
To illustrate, I will set up an alert for the USERS tablespace so that if its used space exceeds 10% of its available space, a warning condition will result, and if it exceeds 20% of its available space, a critical threshold will result. Listing 1.6 shows the code to set up these thresholds via the DBMS_SERVER_ALERT.SET_THRESHOLD procedure.
A threshold is not much use unless it can be monitored, of course. The easiest method for monitoring the resulting threshold violations is to observe them passively. A simple query against the new DBA_OUTSTANDING_ALERTS view will show any changes in the threshold values. (I will save the demonstration for monitoring them dynamically for the next article in this series when I discuss the new e-mail package, UTL_MAIL.)
Listing 1.7 shows a sample query that illustrates this observation method, and also lists some additional views that are useful for interrogating the status of all thresholds:
Oracle 10g has sharpened and expanded the Oracle DBA's toolkit with new and improved PL/SQL packages that provide capabilities to encrypt and decrypt data with complex encryption schemes and monitor statistics about the database and its various user sessions from within the database itself. The next article in this series will focus on several of the new PL/SQL utility packages, including new and improved methods to transmit e-mail and attachments from within an Oracle database.
References and Additional Reading
While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:
B10739-01 Oracle Database Administrator's Guide
B10750-01 Oracle Database New Features Guide
B10802-01 PL/SQL Packages and Types Reference