DBMS_SPACE problem
407338Aug 15 2006 — edited Aug 15 2006Hi gurus,
I was studying Tom Kyte's book and he has a simple table,
create table t x int, y char(1000) default 'x');
then he has the DBMS_SPACE package to get the free and used blocks of the table after some inserts and updates,
declare v_freeBlocks number;
begin
dbms_space.free_blocks(
segment_owner => user,
segment_name => 'T',
segment_type => 'TABLE',
freelist_group_id => 0,
free_blks => v_freeBlocks
);
end;
/
but got these errors:
ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 152
ORA-06512: at line 3
His book is for 8.1.7 and earlier, and I am using 10gR2. I checked the Oracle doc on DBMS_SPACE and it says this,
This package runs with SYS privileges. The execution privilege is granted to PUBLIC. Subprograms in this package run under the caller security. The user must have ANALYZE privilege on the object
I tried both as sys or a user with dba privilege but it got the same errors and I am able to analyze the table.
Please help. Thanks a lot.
ben