Database error

We are facing the following error when we start our application:



ERROR at line 1:

ORA-06564: object SYS.DBMS_REGISTRY does not exist

ORA-06512: at "SYS.DBMS_UTILITY", line 114

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53

ORA-06512: at line 13



I have analysed and found that this issue is due to permission not granted to the user on the object DBMS_REGISTRY.

But when I grant permission to the user on this object (DBMS_REGISTRY), we are getting the same error on another object.



ERROR at line 1:

ORA-06564: object SYS.DBMS_REGISTRY_SERVER does not exist

ORA-06512: at "SYS.DBMS_UTILITY", line 114

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53

ORA-06512: at line 13

Please revert back......
Thanks in advance

What happens if you log into the system as SYS and query dba_objects looking for the offending object?

Hi

Thanks for the prompt response.....


The ouput is as follows:

SQL> select * from dba_objects where OBJECT_NAME='DBMS_REGISTRY';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS
DBMS_REGISTRY
897 PACKAGE
06-MAY-08 21-MAY-08 2008-05-06:07:29:16 VALID N N N


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS
DBMS_REGISTRY
898 PACKAGE BODY
06-MAY-08 06-MAY-08 2008-05-06:07:29:17 VALID N N N


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
PUBLIC
DBMS_REGISTRY
7237 SYNONYM
06-MAY-08 06-MAY-08 2008-05-06:07:29:19 VALID N N N

What happens when you grant privs on SYS.DBMS_REGISTRY while logged into the system as SYS?

When I give privs for one object to user (I am using a user other than SYS to login to the application ,but it has DBA role granted) then it's popping an error on another object

Do you have a database startup trigger in place?

We used the following script:

declare

own varchar2(100);

nam varchar2(100);

cursor pkgs is

select owner, object_name

from dba_objects

where object_type = 'PACKAGE';

begin

open pkgs;

loop

fetch pkgs into own, nam;

exit when pkgs%notfound;

sys.dbms_shared_pool.keep(own || '.' || nam, 'P');

end loop;

end;

Are you intentionally trying to pin all packages?
Shouldn't you be pinning only application packages by filtering by the owner?

I wont get this error if I make script as below is it?

declare

own varchar2(100);

nam varchar2(100);

cursor pkgs is

select owner, object_name

from dba_objects

where owner='SYS';

begin

open pkgs;

loop

fetch pkgs into own, nam;

exit when pkgs%notfound;

sys.dbms_shared_pool.keep(own || '.' || nam, 'P');

end loop;

end;


Please reply.....

Why in the world would you want to pin SYS packages?
Are you trying to burn all your memory pinning stuff that you would hardly use?

Ask yourself this question; "Why and what I would like to pin?"