Compilation of oracle objects are pretty easy where there is no activity on the database. If the objects are heavily accessed and the compilation takes place then you should be prepared for the worst case scenarios.Depending on the object dependency and access rates, sometimes downtime is required.
Offline the system and compile the objects and then compile the invalid objects. One weird thing happened to us when we were compiling on a heavily accessed object. Everything worked perfectly and the we compiled all the
invalid objects then put the system online but then afterwards we saw there are invalid objects reported from the users but when we checked there are no invalid objects on the system.
select * from dba_objects o where last_ddl_time > trunc(sysdate)+18/24 and status <> 'VALID';
Returned 0 rows. It was pretty clear that there were no invalid objects on the system.
Then we found a document on the Support Oracle “ORA-4068 Errors for Valid Objects (Doc ID 835792.1)”. There happened to be a logical corruption on the dependency tree and the only way to find it, is querying the underlying $ views of sys (explained in the document)
select do.obj# d_obj, do.name d_name, do.type# d_type, po.obj# p_obj, po.name p_name, to_char(p_timestamp, 'DD-MON-YYYY HH24:MI:SS') "P_Timestamp", to_char(po.stime, 'DD-MON-YYYY HH24:MI:SS') "STIME", decode(sign(po.stime - p_timestamp), 0, 'SAME', '*DIFFER*') X from sys.obj$ do, sys.dependency$ d, sys.obj$ po where P_OBJ# = po.obj#(+) and D_OBJ# = do.obj# and do.status = 1 /*dependent is valid*/ and po.status = 1 /*parent is valid*/ and po.stime != p_timestamp /*parent timestamp not match*/ order by 2, 1;
It says in the support document that you need to drop/create symbolic links but the objects we had was procedures.
The solution we found to fix the dependency tree was simple. After adding a new line inside the procedure and recompile it and check the dependency query if they are gone.
I can say It is one of the worst things you can face on a Production system 🙂