DBMS_SPACE.SPACE_USAGE

When using this procedure you must ensure that the  tablespace in which the segment resides has segment space management set to  AUTO. If it is manual you are likely to get the following error:

ORA-10614: Operation not allowed on this segment Cause: This procedure can be used only on segments in  tablespaces with AUTO SEGMENT SPACE MANAGEMENT

One extra thing to check first is that you are performing  the operation on the correct segment… After using the DBMS_SPACE.SPACE_USAGE procedure for a table  segment, you may notice that there is a discrepancy between the number of  blocks shown when compared with the DBA_TABLES view – you need to gather stats on the segment first before querying DBA_TABLES.

Using the dbms_space.unused_space view shows the total blocks. The reason behind this is because the DBMS_SPACE.SPACE_USAGE procedure has this in the documentation:

“The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure”

Like it, share it...

Category: Oracle Packages