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