Database error

[aparnakambha]

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

[PAVB]

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

[aparnakambha]

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

[PAVB]

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

[aparnakambha]

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

[PAVB]

Do you have a database startup trigger in place?

[aparnakambha]

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;

[PAVB]

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

[aparnakambha]

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.....

[PAVB]

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?"