Notes for handling ORA-4065 errors

Notes for handling ORA-4065 errors

When receiving channel feedback, a user prompts when performing software operations'ORA-4065 not executed, stored procedure Zl_XXXX_Finish changed or deleted', The user tries to re-compile the process and can use it again. However, after a while, the error will be prompted, and the user wants to completely solve the problem.

Step 1: view the official explanation of the error code

If there is an error message (ORA error code), we must first take a look at the official explanation of this error, as shown below:

Error description:

Error: ORA 4065
Text: not executed, altered or dropped % s
-------------------------------------------------------------------------------
Cause: Attempt to execute a stored procedure that has been altered or droppedthus making it not callable from
Calling procedure.
Action: Recompile its dependents.

Error: ORA 4068
Text: existing state of packages % s has been discarded
-------------------------------------------------------------------------------
Cause: One of errors 4060-4067 when attempt to execute a stored procedure.
Action: Try again after proper re-initialization of any application's state.

It can be seen that the solution can be solved after re-compilation is mentioned in the error explanation, but it is clear that this solution cannot completely handle this problem. We need to further find the solution to the error.

Step 2: Search for official documents

Then query the official documentation, find a process description is relatively close to the content, Summary of Causes for ORA-4068 and ORA-4065 in10gR2, which gives another cause of this error is as follows, the key content is as follows:

Bug 6136074 ORA-4068 ORA-4065 ORA-06508 ERRORS SIGNALED FOR VALID OBJECTS

Component: RDBMS
Fixed Ver (s): 10204 111
Symptom (s ):
Recompilation of a view might lead to inconsistent timestamps for some of theview's PLSQL dependents. The script in

Note 136697.1can be used to check for such problem DEPENDENCY $ entries, or use the SQLbelow:

Set pagesize 10000
Column d_name format a20
Column p_name format a20
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;

According to the content of the on-site documents, this problem may also be caused by bug6136074. The query statement can be used to query the inconsistent timestamps of the process. In general, the re-compilation process is used, the timestamp can be consistent, but sometimes it cannot be effective after compilation. At this time, we must delete the reconstruction method. here we need to see what the object is. We can find it as a public synonym here, you only need to delete the synonym and recreate it.

Solution Process

The official SQL query result shows that the timestamp of the public synonyms executed by Zl _ patient doctor's advice _ Finish is inconsistent. Here we delete the public synonyms in this process and recreate them to complete the troubleshooting.

When deleting a synonym, inform the user that it may cause temporary unavailability.

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian