Procedure uses DBMS_SPACE error
422674Dec 16 2005 — edited Dec 17 2005Hi ,
I use this Stored Procedure with DBMS_SPACE.SPACE_USAGE
( p_owner in varchar2,
p_name in varchar2,
p_type in varchar2 default 'TABLE' )
AS
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
PROCEDURE p( p_label in varchar2, p_num in number )
IS
BEGIN
dbms_output.put_line( rpad(p_label,50,'.') || p_num);
END;
BEGIN
dbms_space.space_usage
( segment_owner => p_owner,
segment_name => p_name,
segment_type => p_type,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes );
p( 'Unformatted Blocks', l_unformatted_blocks);
p( 'Unformatted Bytes',l_unformatted_bytes);
p( 'FS1 Blocks',l_fs1_blocks);
p( 'FS1 Bytes',l_fs1_bytes);
P( 'FS2 Blocks',l_fs2_blocks);
P( 'FS2 Bytes',l_fs2_bytes);
P( 'FS3 Blocks',l_fs3_blocks);
P( 'FS3 Bytes',l_fs3_bytes);
p( 'FS4 Blocks',l_fs4_blocks);
p( 'FS4 Bytes',l_fs4_bytes);
P( 'Full Blocks',l_full_blocks);
P( 'Full Bytes',l_full_bytes);
END;
When I run the Procedure one time it works after that occurs the error:
SQL> show user
USER ist "HR"
exec show_used('HR','RACF','TABLE');
*
FEHLER in Zeile 1:
ORA-10614: Operation not allowed on this segment
ORA-06512: in "SYS.DBMS_SPACE", Zeile 97
ORA-06512: in "JOEFISH.SHOW_USED", Zeile 23
ORA-06512: in Zeile 1
Can anyone help?
Regards
MArcel