We help IT Professionals succeed at work.

How to compile dbms_utility in oracle?

Daniel Mina
Daniel Mina asked
on
Hello Experts,
I want to know how to compile invalid SYS objects especially "SYS.DBMS_UTILITY" as I've many invalid objects in SYS schema and when I try to compile them I get the following error message:

ORA-20001: Cannot recompile SYS objects
ORA-06512: at "SYS.DBMS_UTILITY", line 387
ORA-06512: at line 1

Oracle version: 11.2 g

Thanks,
Daniel Mina.
Comment
Watch Question

View Solution Only

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
johnsoneSenior Oracle DBA

Commented:
If after you run utlrp, there are still invalid objects take a look at DBA_ERRORS to see what's going on.
Muhammad Riaz ShahidDatabase Administrator

Commented:
Check if you have any invalid component?

select comp_name
from dba_registry
where status ='INVALID'

Open in new window

slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

>> Check if you have any invalid component?


Components are different from objects/packages like dbms_utility.  You find those with:

select object_name, object_type
from dba_objects
where status=''INVALID';


Muhammad Riaz ShahidDatabase Administrator

Commented:
My Idea was because author says there are many invalid objects under SYS which is not normal. It happened to me once after applying the bundle patch, many objects got invalid under SYS and they wouldn't recompile using utlrp. What was found that the CATPROC component got invalid so the solution was to recreate that by running catlog.sql & catproc.sql and then recompiling the objects by using utlrp.sql.