LBL: Looking Before Leaping
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.
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.
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
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"
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
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.
recompiling, always check for invalidated objects. Then check again.
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.
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.
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.
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