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 )

 

 780 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply