ORA-04045: errors during recompilation/revalidation

I got ” ORA-04045: errors during recompilation/revalidation ” error in Oracle database.

 

ORA-04045: errors during recompilation/revalidation

 

Details of error are as follows.

 

SQL> declare
2 cursor ora_dict_synonyms is
3 select o.object_id from dba_objects o
4 where o.owner = ‘PUBLIC’
5 and o.object_type = ‘SYNONYM’
6 and o.object_name like ‘ORA_%’;
7
8 cursor ddl_triggers is
9 select o.object_id from dba_triggers t, dba_objects o
10 where t.owner = o.owner and t.trigger_name = o.object_name
11 and o.object_type = ‘TRIGGER’
12 and (t.triggering_event like ‘%ALTER%’ or
13 t.triggering_event like ‘ÝL%’);
14 begin
15 for s in ora_dict_synonyms loop
16 dbms_utility.validate(s.object_id);
17 end loop;
18
19 for t in ddl_triggers loop
20 dbms_utility.validate(t.object_id);
21 end loop;
22 end;
23 /
declare
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYSTEM.DDL_TRIGGER
ORA-01031: insufficient privileges
ORA-06512: at “SYS.DBMS_UTILITY”, line 1283
ORA-06512: at line 20

 

 

errors during recompilation/revalidation

This ORA-04045 errors are related with the Privileges Issue for system User

 

 

The upgrade documents precisely states below in the section “Requirements and recommendations for Source database”
“Disable any custom triggers that would fire before/after DDL and enable them after the upgrade is complete.”

It is strongly recommended to abide by the pre-upgrade tasks as mentioned in the upgrade document.

 

TRIGGERS Should be disabled Prior to Upgrade.

1. We should disable all the triggers before staring the upgrade

To Disable :-

SQL> SELECT ‘ALTER TRIGGER ‘ || owner || ‘.’ || trigger_name || ‘ DISABLE;’ “ALTER Statement” FROM dba_triggers where TRIGGER_TYPE in (‘BEFORE EVENT’,’AFTER EVENT’,’AFTER STATEMENT’);
Example :-

alter trigger XXXXXXXXX disable;
To Enable:-

SQL> SELECT ‘ALTER TRIGGER ‘ || owner || ‘.’ || trigger_name || ‘ ENABLE;’ “ALTER Statement” FROM dba_triggers where TRIGGER_TYPE in (‘BEFORE EVENT’,’AFTER EVENT’,’AFTER STATEMENT’);
Example :-

alter trigger XXXXXXXXX ENABLE;

 

Or

 

2. Provide below Privileged

 

SQL> GRANT ADMINISTER DATABASE TRIGGER to SYSTEM;

Prior to upgrade to restart the Upgrade .

 

 

“_system_trig_enabled” = FALSE

Or set the following parameter to solve this error.

SQL> ALTER SYSTEM SET "_system_trig_enabled" = FALSE scope=both;
System altered.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )