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