Hi DBAs,
As one of you ever encounter this situation.
Oracle8i (8.1.6)
package : DBMS_SPACE is valid
I have procedure which calls this package(owned by SYS) with AUTHID
CURRENT_USER
If I execute the same package as another user other than SYS or SYSTEM it
works fine.

It does work as SYSTEM or SYS....I don't know why !!!

SET SERVEROUTPUT ON SIZE 100000
VARIABLE MYp_TabspSize NUMBER
BEGIN dope_sa.dlex_sp_verifySpace(p_TabspSize => :MYp_TabspSize); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 40
ORA-06512: at "DOPE_SA.DLEX_SP_VERIFYSPACE", line 41
ORA-06512: at line 1

Here's the code, can one try it against an 8.1.6 db...before I JUMP and I
really mean it.

CREATE OR REPLACE PROCEDURE dope_sa.dlex_sp_verifySpace
(
p_TabspSize OUT NUMBER
)
AUTHID CURRENT_USER
AS

TYPE l_seg_CURREF IS REF CURSOR;

l_seg_CUR l_seg_CURREF;
p_segment_name VARCHAR2(30);
p_segment_type VARCHAR2(30);
p_partition_name VARCHAR2(30);

--- change the username here.
p_dopeUser VARCHAR2(30) :='DOPE_SA';


p_current_space_alloc NUMBER;
p_free_space NUMBER;
p_total_space NUMBER;
p_space_to_deallocate NUMBER;
p_dummy NUMBER;
n_new_space NUMBER;
p_space_being_used NUMBER;
p_TotalSpaceUsedbyDOPE NUMBER:= 0;
p_TablespaceLimitSize NUMBER := 50000; --- 50MB

BEGIN
IF (l_seg_CUR%ISOPEN) THEN
CLOSE l_seg_CUR;
END IF;

OPEN l_seg_CUR FOR
'SELECT segment_name, segment_type
FROM user_segments WHERE segment_type IN (''TABLE'', ''INDEX'')
AND segment_name NOT LIKE ''SYS_%''';

LOOP
FETCH l_seg_CUR INTO p_segment_name, p_segment_type;
EXIT WHEN l_seg_CUR%NOTFOUND;

DBMS_SPACE.UNUSED_SPACE
(p_dopeUser,
p_segment_name,
p_segment_type,
p_dummy,
p_total_space,
p_dummy,
p_free_space,
p_dummy,
p_dummy,
p_dummy,
p_partition_name
);

--- converts it in kbytes (/1024)
p_current_space_alloc := ROUND(p_total_space/1024, 0);
p_space_being_used := ROUND((p_total_space - p_free_space)/1024,
0);
p_TotalSpaceUsedbyDOPE := p_TotalSpaceUsedbyDOPE +
p_space_being_used;

END LOOP;
CLOSE l_seg_CUR;

DBMS_OUTPUT.PUT_LINE('Total Used space in KBytes is:
'||TO_CHAR(p_TotalSpaceUsedbyDOPE)||' KBYTES');
IF (p_TablespaceLimitSize > (p_TotalSpaceUsedbyDOPE + 20000)) THEN

p_TabspSize := p_TotalSpaceUsedbyDOPE + 20000;
DBMS_OUTPUT.PUT_LINE('Succesfull upgrade requires that
tablespace BIC_SMALL to created with: ' ||TO_CHAR(p_TabspSize)||' KBYTES');
ELSE
p_TabspSize := p_TotalSpaceUsedbyDOPE + 20000;
DBMS_OUTPUT.PUT_LINE('Succesfull upgrade requires that
tablespace BIC_SMALL to created with: '||TO_CHAR(p_TabspSize)|| 'KBYTES');
END IF;
END dlex_sp_verifySpace;
/



thanks,
Regis
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************

Oracle LazyDBA home page