Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_SPACE problem

407338Aug 15 2006 — edited Aug 15 2006
Hi 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 12 2006
Added on Aug 15 2006
3 comments
500 views