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

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
    (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.

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
I have constructed a new package, HR.PKG_SENSITIVE_DATA, with two public functions,
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.

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

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.
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
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
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

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

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
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
shows the code to set up these thresholds via the

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.)

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:

    view shows the history of any recently exceeded thresholds.

    shows the additional arguments that are returned whenever a threshold is

  • Finally, view DBA_THRESHOLDS
    lists the current thresholds that are in place for the entire


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

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles