Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 26, 2005

Oracle 10g PL/SQL Enhancements, Part 1: DBMS_CRYPTO, DBMS_MONITOR, and DBMS_SERVER_ALERT

By Jim Czuprynski

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:

  • Newly supported encryption methods include Secured Hash Algorithm (SHA-1), Advanced Encryption Standard (AES), and MD5.
  • Several new block cipher-chaining modifiers have been added. These ciphers can be used to encrypt data according to pre-established methods.
  • The 128-bit MD5 and the 160-bit Secure Hash Algorithm (SHA) cryptographic hash functions are provided for even more security. These methods accept a variable-length string as input and hash the input, converting it to an output string of fixed length. This hashed value is unique to the input data and can be used to "fingerprint" the input - a valuable method for preserving data integrity.
  • Finally, new Message Authentication Code (MAC) functions that return key-dependent, one-way hashes are also provided. Data encrypted with this method requires that the recipient supply the identical key to decrypt it - useful, for example, to insure that a file has not been "hacked" by a virus.

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:

  • The DBA_ALERT_HISTORY view shows the history of any recently exceeded thresholds.
  • View DBA_ALERT_ARGUMENTS shows the additional arguments that are returned whenever a threshold is breached.
  • Finally, view DBA_THRESHOLDS lists the current thresholds that are in place for the entire database.


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

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM