Oracle Safety Blankets: Insulating Your Applications Against Database Changes - Page 2

July 29, 2003

LBL: Looking Before Leaping

The preceding techniques work just fine when planning a set of new objects for deployment, or revising an existing set of objects before deployment. However, I have also found that disruption to working applications is indirectly proportional to the planning for their deployment. In other words, a well-planned deployment usually protects applications against disruption caused by invalidation and recompilation of modified database objects.

Know what the impact is before invalidating an object.
Before I make a change to a database object, I make it a point to take a sanity check of the objects that will be invalidated. Obvious? Absolutely! And, I've obviously absolutely accidentally violated this common-sense advice several times over the years because I was hurried by an anxious developer or harried manager, usually to the diminution of an application's performance.

Here's some sample code that can help you identify what objects would be invalidated by a recompilation of an object (in this case, the EMPLOYEES table in the HR schema):

SQL> SET WRAP OFF
SQL> TTITLE CENTER "Parent and Dependent Objects"
SQL> BREAK ON par_typ SKIP 1 ON par_sts SKIP 1 ON par_obj SKIP 1 NODUPLICATES
SQL> COLUMN par_typ FORMAT A12 HEADING "Type"
SQL> COLUMN par_sts FORMAT A08 HEADING "Status"
SQL> COLUMN par_obj FORMAT A16 HEADING "Parent"
SQL> COLUMN dep_obj FORMAT A16 HEADING "Child"
SQL> COLUMN dep_typ FORMAT A12 HEADING "Type"
SQL> COLUMN dep_sts FORMAT A08 HEADING "Status"
SQL> SELECT
  2     O1.object_type par_typ,
  3     O1.status      par_sts,
  4     O1.object_name par_obj,
  5     O2.object_name dep_obj,
  6     O2.object_type dep_typ,
  7     O2.status      dep_sts
  8    FROM
  9     public_dependency PD,
 10     all_objects O1,
 11     all_objects O2
 12   WHERE PD.referenced_object_id = O1.object_id
 13     AND PD.object_id = O2.object_id
 14     AND O1.object_name = 'EMPLOYEES'
 15   ORDER BY par_obj;

                          Parent and Dependent Objects
Type         Status   Parent           Child            Type         Status
------------ -------- ---------------- ---------------- ------------ --------
TABLE        VALID    EMPLOYEES        PKG_SECURITY     PACKAGE BODY INVALID
                                       BV_EMPLOYEES     VIEW         VALID
                                       EMP_DETAILS_VIEW VIEW         VALID
                                       PKG_SECURITY     PACKAGE BODY VALID
                                       SECURE_EMPLOYEES TRIGGER      VALID
                                       UPDATE_JOB_HISTO TRIGGER      VALID

6 rows selected.

After recompiling, always check for invalidated objects. Then check again.
Sometimes I have seen situations where the UTLRP.SQL recompiler or third-party software does not recompile all recently invalidated objects. At a minimum, this can lead to some frustration; in the worst case, an application could be prevented from accessing the database for some time unless a user notices immediately.

For example, I recently spent about 90 minutes one afternoon helping a developer debug a PowerBuilder application that was returning an ORA-00942 "table not found" Oracle database error when run against our development database. The same code ran fine against the production database. The error seemed to appear and reappear intermittently even while we were debugging the application code.

I knew I had seen this type of error in the past, and spent several minutes racking my brain, when it finally hit me that invalidated objects were the culprit. It turned out that another developer had been dropping and recreating a table that was used by several dozen other database objects, but had neglected to recompile all of the invalidated dependent objects.

Watch out for global temporary tables.
One last caveat: If you are using global temporary tables (GTTs) to store and accumulate stateful information, be aware of the impact of altering GTTs on the applications that may be utilizing them indirectly.

I recently had to expand a column in a GTT from VARCHAR2(15) to VARCHAR2(25), a simple operation for a "normal" table. However, this particular GTT was being used by a package to store stateful information per each user session via the ON COMMIT PRESERVE ROWS option. Oracle steadfastly refused to allow the ALTER TABLE operation to complete until I had asked all users running the application to log out, thus freeing the GTT for modification. It only took a few moments to complete this, and only a few users were logged in at the time, but the impact could have been much worse if an emergency change to a GTT had to be deployed during peak business operations.

» See All Articles by Columnist Jim Czuprynski