Executing stored procedure from diff user

From: Saminathan Seerangan <saminathan_at_gawab.com>
Date: Fri, 25 Jul 2003 05:48:53 GMT
Message-Id: <26007.339515@fatcity.com>

Hi List,

Could you please help me out to resolve this issue? Basically i have created one stored procedure(show_space) in SYSTEM schema, then granted execute rights to PUBLIC and created public synonym.
When i conenct as diff user(MUT) I am not able to execute the procedure. Any help would be really appreciated.

TIA SQL> connect system/manager_at_psrsndb2
Connected.
SQL> @D:\share\oracle\Asktom\show_space8i.sql  51 /

Procedure created.

SQL> grant execute on show_space to public; Grant succeeded.

SQL> create public synonym show_space for show_space; Synonym created.

SQL> connect mut/mut_at_psrsndb2
Connected.

SQL> execute show_space('RW_RFX_HEADER'); BEGIN show_space('RW_RFX_HEADER'); END;

*
ERROR at line 1:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "SYSTEM.SHOW_SPACE", line 22
ORA-06512: at line 1

SQL> desc show_space
PROCEDURE show_space

 Argument Name                  Type                    In/Out

Default?

• ----------------------- ------

  ---

  P_SEGNAME VARCHAR2 IN P_OWNER VARCHAR2 IN DEFAULT P_TYPE VARCHAR2 IN DEFAULT

SQL> desc dbms_space
PROCEDURE FREE_BLOCKS

 Argument Name                  Type                    In/Out

Default?

• ----------------------- ------

  ---

  SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN FREELIST_GROUP_ID NUMBER IN FREE_BLKS NUMBER OUT SCAN_LIMIT NUMBER IN DEFAULT PARTITION_NAME VARCHAR2 IN DEFAULT PROCEDURE UNUSED_SPACE Argument Name Type In/Out Default?
• ----------------------- ------

  ---

  SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN TOTAL_BLOCKS NUMBER OUT TOTAL_BYTES NUMBER OUT UNUSED_BLOCKS NUMBER OUT UNUSED_BYTES NUMBER OUT LAST_USED_EXTENT_FILE_ID NUMBER OUT LAST_USED_EXTENT_BLOCK_ID NUMBER OUT LAST_USED_BLOCK NUMBER OUT PARTITION_NAME VARCHAR2 IN DEFAULT

  ---

  Free multi-lingual web-based and POP3 email service with a generous 15MB of storage, a choice of themes for your mailbox, message filtering, plus spam and virus protection

Received on Fri Jul 25 2003 - 00:48:53 CDT