Original author: Jim Czuprynski
LBL (Looking Before Leaping) Think twice
The techniques described above can achieve good results only when planning a set of new database objects or correcting the original database objects prior to development. But I will always find that when we are carrying out this development plan, the operations in run are often unauthorized. In other words, a good plan should protect existing applications from interruptions caused by the recoimal or invalidation caused by the change of database object changes.
Understand what a significant impact will occur before the object is effective
Before I start talking about changing the database object, you must emphasize a little, you must carefully check the database object to be changed to prevent invalid errors. Under the past few years, under an anxious developer and the competent supervisor, I have intentionally or unintentionally violated these recommendations, which usually leads to a decline in performance performance.
The following code can help you check which objects that will be compiled will be invalid.
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 8
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 Validpkg_security Package Body Valid
Secure_Employees Trigger Valid
Update_job_histo Trigger Valid
6 rows selected.
After recompilation, you must check which invalid objects. Then still check again
Many times, I have witnessed this situation: UTLRP.SQL recompacker or third-party software does not recompile all recently invalid objects. At least, this will lead to some problems; in the worst case, the application will not access the database at all unless someone pays attention to this.
For example, shortly, I spent 90 minutes plus one afternoon helped a programmer to debug the PowerBuilder program to access the ORA-00942 error "Table Not found" when developing the database, but the same code can be on the production database. Run correctly. When we are debugging, this error looks intermittent and reproduced.
I remembered this situation before and spent a lot of time to get to torture my brain, and I finally have irony. The culprit is an invalid database object. Finally, it was found to be because another programmer deleted and rebuilt the original table, but there is a lot of other database objects to reference this table, but he did not recompile these objects, causing them to be invalid.
Close to Global Temporary Tables
The last advice: If you use the global temporary table (GTTS) to store and calculate status information, the careful program changes the consequences of it. I used to make a most common change, change the GTT VARCHAR (15) field into varchar (25). However, this special GTT can be used to store each connection user information by specifying the on commit preserve rows option. Oracle stubbornly rejected Alter Table command unless I asked users of all applications to log out, unless the freezing of GTT was released. Maybe the order only needs a small meeting, but only needs to wait for a small party after the user is logged out, but if this change is going to do in a business peak, this impact may become very bad.
(End of full text)
