Link to home
Start Free TrialLog in
Avatar of Daniel Mina
Daniel Mina

asked on

How to compile dbms_utility in oracle?

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If after you run utlrp, there are still invalid objects take a look at DBA_ERRORS to see what's going on.
Avatar of Ora_Techie
Ora_Techie

Check if you have any invalid component?

select comp_name
from dba_registry
where status ='INVALID'

Open in new window

>> 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';

Open in new window


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.