Let your application no longer make changes to the database "cold" (iii)

Program | data | Database


Original Author: Jim Czuprynski





 





LBL (looking before leaping) think twice before you do it





the technology described earlier can be very effective only if you plan a new set of database objects or modify existing database objects before you develop them. But I always find that when we do this kind of development planning, the running applications are inevitably interrupted. In other words, a good plan should protect existing applications from the disruption caused by recompilation or invalidation due to changes in database objects.





understand what significant impact will occur before the object is effective





before I start talking about changing database objects, it's important to emphasize that you should carefully examine every database object that you want to change to prevent an invalid error. In the past few years, with the urging of anxious developers and tickling executives, I have intentionally or unintentionally violated these recommendations, often leading to a drop in application performance.





The following code can help you check which objects will be invalidated by recompiling.





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,





All_objects O1,





all_objects O2





WHERE pd.referenced_object_id = o1.object_id





and pd.object_id = o2.object_id





and o1.object_name = ' EMPLOYEES '





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, be sure to check which objects are not valid. And then we'll check the
.




many times, I have seen a situation where the utlrp.sql or Third-party software has not recompiled all recently invalid objects. At the very least, this causes some problems; In the worst case, unless someone notices this, the application will not be able to access the database at all.





For example, I spent a whole 90 minutes and a afternoon helping a programmer debug a PowerBuilder program to access the ORA-00942 error "table not found" encountered by the Oracle development database. But the same code can run correctly on the production database. This error appears to be intermittent and reproducible as we debug the program.





I remember having been in this situation before and spending a lot of time torturing my brain, and ultimately ironically, the culprit is the invalid database object. The discovery was made because another programmer deleted and rebuilt the original table, but there were a bunch of other database objects referencing the table, and he didn't recompile the objects, which made them invalid.





closely watch out for global temporary tables





Last caveat: If you use a global temporary table (Gtts) to store and compute state information, be careful about the consequences of the program changing it. I have made one of the most mundane changes to change the GTT varchar (15) field to varchar (25). This particular GTT, however, specifies the on COMMIT PRESERVE rows option, which can be used by a package to store information for each connected user. Oracle stubbornly rejects the ALTER TABLE command unless I require all users of the application to log off and then freeze the GTT. Maybe that command only takes a little while, and the user logs off and waits a little while, but imagine that the impact could be very bad if the change is to be done at a peak of business.





 





(end of full text)