Re: deallocate unused space

  procedure unused_space(segment_owner IN varchar2,
                         segment_name IN varchar2,
                         segment_type IN varchar2,
                         total_blocks OUT number,
                         total_bytes OUT number,
                         unused_blocks OUT number,
                         unused_bytes OUT number,
                         last_used_extent_file_id OUT number,
                         last_used_extent_block_id OUT number,
                         last_used_block OUT number
                         );

• Returns information about unused space in an object (table, index,
• or cluster).
• Input arguments:
• segment_owner
• schema name of the segment to be analyzed
• segment_name
• object name of the segment to be analyzed
• segment_type
• type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
• Output arguments:
• total_blocks
• total number of blocks in the segment
• total_bytes
• the same as above, expressed in bytes
• unused_blocks
• number of blocks which are not used
• unused_bytes
• the same as above, expressed in bytes
• last_used_extent_file_id
• the file ID of the last extent which contains data
• last_used_extent_block_id
• the block ID of the last extent which contains data
• last_used_block
• the last block within this extent which contains data

The highwater mark is the last block of the table that has contained data.
The highwater mark is given by last_used_extent_file_id and last_used_extent_block_id.
unused_blocks gives you the number of blocks you can deallocate.

--
Hope this helps
Michel


cc <zhangc_at_netcom.ca> a écrit dans le message : JRmW4.356$qS3.148_at_tor-nn1.netcom.ca...
> I use ALTER TABLE tablename DEALLOCATE UNUSED in a 7.3.4 DB and I did
> queried the unused space by calling DBMS_SPACE.UPUSED_SPACE before the
> ALTER. But I still get the same number both from user_segments.bytes and
> dba_free_space.bytes for the tablespace the table is using. (some of the
> tables I did against to have several extents). As per Oracle manual only the
> space above the high water mark can be deallocated and the position of the
> high water mark can be found by calling DBMS_SPACE.UPUSED_SPACE, my problem
> is what is the high water mark it doesn't seem to me any of the OUT
> paramters for DBMS_SPACE.UPUSED_SPACE is about it. OR anywhere I did wrong ?
> Thanks - Chuck
>
>
>