CMS Snapshot Analysis - Run Analyzer - Fatal Error - SQL Errors - ORA-04045 errors during recompilation revalidation

This page provides solution for the fatal error Sql Error ORA 04045 encountered during J2EE application analysis shown in the below analysis log :

Merging objects on server . . .

Merge completed.
SQL Error: ORA-04045: errores durante la recompilación/revalidación de <Localbase>.CLEANUPEXTERNALPROJECTS.
SQL Error: ORA-04027: autointerbloqueo durante la validación automática del objeto <Localbase>.PROJECT_DELETE.
SQL Error: ORA-06508: PL/SQL: no se ha encontrado la unidad de programa llamada : "<Localbase>.CLEANUPEXTERNALPROJECTS".
SQL Error: ORA-06512: en "<Localbase>.INT_FINAL_JOBANA", línea 100.
SQL Error: ORA-06512: en línea 1.
SQL Error: ORA-06512: en "<Localbase>.FINAL_JOBANA", línea 70.

The issue has been fixed in 7.3.10

Below is the step-by-step scenario leading to the problem:

Run J2EE application analysis.

The impact of the problem on the analysis or the dashboard is: Analysis fails with fatal error.

This issue occurs because there are invalid objects created in oracle schema after the analysis is run.

1. To identify the invalid objects, please run the below query:

   SELECT owner,
   object_type,
                object_name,
                status
   FROM dba_objects
   WHERE status = 'INVALID'
                 and owner = '<YOUR SCHEMA NAME>'
   ORDER BY owner, object_type, object_name

   Query result example

   OWNER	OBJECT_TYPE	OBJECT_NAME	STATUS
   <Local base name>	Function	AMT_F_CLEAN_KB	INVALID

   The invalid functions after the analysis are due to drop and create sequence in AMT_P_ functions.

   For instance AMT_P_PRE_TECHNO function that drop and recreate the sequence INT_SEQ1.

    

2. Attached is a Sql script  with updated function INT_ACCPRO_DEL that does not drop the INT_SEQ1 sequence. and FINAL_JOBANA that does not call dynamically function. Run the same on local base and recompile all the invalid functions.

   1. To recompile Oracle schema, you can follow the below links: Recompiling Invalid Schema Objects and Oracle: Recompile Invalid Objects
      Mostly,there are two processes:

      1. DBMS_UTILITY.COMPILE_SCHEMA.
      2. Two procedures in UTL_RECOMP.

3. Open CAST-MS and run analysis on the J2EE analysis unit only

   (Ensure that the analysis is run only on J2EE unit)

4. If analysis runs fine, run analysis on all analysis units