DBMS_SPACE Segment space usage for ASSM tablespace: set serverout on declare fs1_bytes number; fs2_bytes number; fs3_bytes number; fs4_bytes number; fs1_blocks number; fs2_blocks number; fs3_blocks number; fs4_blocks number; fullbytes number; fullblocks number; unformatted_bytes number; unformatted_blocks number; begin dbms_space.space_usage( 'YONG', --username 'TEST', --segment name 'TABLE', --segment type unformatted_blocks, unformatted_bytes, fs1_blocks, fs1_bytes, fs2_blocks, fs2_bytes, fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, fullblocks, fullbytes); dbms_output.put_line('FS1 Blocks = '||fs1_blocks||' and Bytes = '||fs1_bytes); dbms_output.put_line('FS2 Blocks = '||fs2_blocks||' and Bytes = '||fs2_bytes); dbms_output.put_line('FS3 Blocks = '||fs3_blocks||' and Bytes = '||fs3_bytes); dbms_output.put_line('FS4 Blocks = '||fs4_blocks||' and Bytes = '||fs4_bytes); dbms_output.put_line('Full Blocks = '||fullblocks||' and Bytes = '||fullbytes); dbms_output.put_line('Unformatted Blocks = '||unformatted_blocks||' and Bytes = '||unformatted_bytes); end; / FS1 blocks (0 to 25% free space) are fuller than FS2 blocks (25 to 50% free space), which are fuller than FS3... For SecureFile LOB, use the code in Doc 1669501.1: declare segment_size_block NUMBER; segment_size_byte NUMBER; used_block NUMBER; used_byte NUMBER; expired_block NUMBER; expired_byte NUMBER; unexpired_block NUMBER; unexpired_byte NUMBER; begin dbms_space.space_usage ('OWNER', 'SYS_LOB0000077243C00041$$', 'LOB', segment_size_block, segment_size_byte, used_block, used_byte, expired_block, expired_byte, unexpired_block, unexpired_byte, null); dbms_output.put_line('segment_size_blocks = '||segment_size_block); dbms_output.put_line('segment_size_bytes = '||segment_size_byte); dbms_output.put_line('used_blocks = '||used_block); dbms_output.put_line('used_bytes = '||used_byte); dbms_output.put_line('expired_blocks = '||expired_block); dbms_output.put_line('expired_bytes = '||expired_byte); dbms_output.put_line('unexpired_blocks = '||unexpired_block); dbms_output.put_line('unexpired_bytes = '||unexpired_byte); end; / In 12c, if you get error PLS-00307: too many declarations of 'SPACE_USAGE' match this call try adding dbms_space.spaceusage_exact (or dbms_space.spaceusage_fast) as the 4th argument (called suoption) to dbms_space.space_usage (even though the syntax is supposed to allow it to be omitted), e.g. dbms_space.space_usage ('YONG', 'SYS_LOB0000257097C00037$$', 'LOB', dbms_space.spaceusage_exact, segment_size_block, segment_size_byte, used_block, used_byte, expired_block, expired_byte, unexpired_block, unexpired_byte, null); Other note: For non-ASSM (MSSM) tablespace, use dbms_space.free_blocks instead, or you would get ORA-10618. Note:116565.1 (https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=116565.1) "The UNUSED_SPACE procedure refers to unused space *above* the high water mark in a segment." "The FREE_BLOCKS procedure refers to the blocks in a segment *below* the high water mark whose number of rows falls below the PCTUSED attribute, and therefore are candidate for new inserted rows." Note that Oracle intentionally avoids using the word "unused" in the second sense; space that was used before but is not any more is not called unused space, but called free blocks instead. ***** BEGIN CODE FOR TEST ***** create table test (x int); select segment_space_management from dba_tablespaces where tablespace_name = (select tablespace_name from user_tables where table_name = 'TEST'); insert into test select rownum from (select * from dual connect by 1=1) where rownum <= 10000; var total_blocks NUMBER var total_bytes NUMBER var unused_blocks NUMBER var unused_bytes NUMBER var last_used_extent_file_id NUMBER var last_used_extent_block_id NUMBER var last_used_block NUMBER var free_blks NUMBER begin DBMS_SPACE.UNUSED_SPACE ( 'YONG', 'TEST', 'TABLE', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :last_used_extent_file_id, :last_used_extent_block_id, :last_used_block); end; / begin DBMS_SPACE.FREE_BLOCKS ( 'YONG', 'TEST', 'TABLE', 0, :free_blks); end; / print exec dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) select num_rows, blocks, empty_blocks, avg_space, avg_row_len from user_tables where table_name = 'TEST'; alter table test allocate extent; --check again alter table test allocate extent (instance 1); --check again ***** END CODE FOR TEST ***** Test done in 9.2.0.1. Comments are to the right of <--. SQL> create table test (x int); Table created. SQL> select segment_space_management from dba_tablespaces where tablespace_name = 2 (select tablespace_name from user_tables where table_name = 'TEST'); SEGMEN ------ MANUAL <-- AUTO would cause dbms_space.free_blocks to throw ORA-10618 (Operation not allowed on this segment) If AUTO, use dbms_space.space_usage instead to get space usage below HWM. SQL> insert into test select rownum from (select * from dual connect by 1=1) where rownum <= 10000; 10000 rows created. SQL> var total_bytes NUMBER SQL> var unused_blocks NUMBER SQL> var unused_bytes NUMBER SQL> var last_used_extent_file_id NUMBER SQL> var last_used_extent_block_id NUMBER SQL> var last_used_block NUMBER SQL> var free_blks NUMBER SQL> begin 2 DBMS_SPACE.UNUSED_SPACE ( 3 'YONG', 4 'TEST', 5 'TABLE', 6 :total_blocks, 7 :total_bytes, 8 :unused_blocks, <-- reports free space above HWM 9 :unused_bytes, <-- reports free space above HWM 10 :last_used_extent_file_id, 11 :last_used_extent_block_id, 12 :last_used_block); 13 end; 14 / PL/SQL procedure successfully completed. SQL> begin 2 DBMS_SPACE.FREE_BLOCKS ( 3 'YONG', 4 'TEST', 5 'TABLE', 6 0, 7 :free_blks); <-- reports free space below HWM 8 end; 9 / PL/SQL procedure successfully completed. SQL> print TOTAL_BLOCKS ------------ 32 TOTAL_BYTES ----------- 131072 UNUSED_BLOCKS ------------- 0 UNUSED_BYTES ------------ 0 LAST_USED_EXTENT_FILE_ID ------------------------ 11 LAST_USED_EXTENT_BLOCK_ID ------------------------- 17 LAST_USED_BLOCK --------------- 32 FREE_BLKS ---------- 1 SQL> exec dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) PL/SQL procedure successfully completed. SQL> select num_rows, blocks, empty_blocks, avg_space, avg_row_len from user_tables where table_name = 'TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN ---------- ---------- ------------ ---------- ----------- 10000 31 0 0 3 SQL> alter table test allocate extent; <-- Not specifying instance so space is added above HWM Table altered. SQL> begin 2 DBMS_SPACE.UNUSED_SPACE ( 3 'YONG', 4 'TEST', 5 'TABLE', 6 :total_blocks, 7 :total_bytes, 8 :unused_blocks, 9 :unused_bytes, 10 :last_used_extent_file_id, 11 :last_used_extent_block_id, 12 :last_used_block); 13 end; 14 / PL/SQL procedure successfully completed. SQL> begin 2 DBMS_SPACE.FREE_BLOCKS ( 3 'YONG', 4 'TEST', 5 'TABLE', 6 0, 7 :free_blks); 8 end; 9 / PL/SQL procedure successfully completed. SQL> print TOTAL_BLOCKS ------------ 64 <-- Doubled TOTAL_BYTES ----------- 262144 <-- Doubled UNUSED_BLOCKS ------------- 32 <-- Changed from 0 UNUSED_BYTES ------------ 131072 <-- Changed from 0 LAST_USED_EXTENT_FILE_ID ------------------------ 11 LAST_USED_EXTENT_BLOCK_ID ------------------------- 17 LAST_USED_BLOCK --------------- 32 FREE_BLKS <-- space below HWM ---------- 1 <-- No change because space is added above HWM SQL> exec dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) PL/SQL procedure successfully completed. SQL> select num_rows, blocks, empty_blocks, avg_space, avg_row_len from user_tables where table_name = 'TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN ---------- ---------- ------------ ---------- ----------- 10000 31 0 0 3 <-- None changed SQL> alter table test allocate extent (instance 1); <-- Specifying instance so space is added below HWM (non-ASSM tablespace only). In contrary to documentation, you CAN use the instance clause even on a single instance database. Table altered. SQL> var total_bytes NUMBER SQL> var unused_blocks NUMBER SQL> var unused_bytes NUMBER SQL> var last_used_extent_file_id NUMBER SQL> var last_used_extent_block_id NUMBER SQL> var last_used_block NUMBER SQL> var free_blks NUMBER SQL> begin 2 DBMS_SPACE.UNUSED_SPACE ( 3 'YONG', 4 'TEST', 5 'TABLE', 6 :total_blocks, 7 :total_bytes, 8 :unused_blocks, 9 :unused_bytes, 10 :last_used_extent_file_id, 11 :last_used_extent_block_id, 12 :last_used_block); 13 end; 14 / PL/SQL procedure successfully completed. SQL> begin 2 DBMS_SPACE.FREE_BLOCKS ( 3 'YONG', 4 'TEST', 5 'TABLE', 6 0, 7 :free_blks); 8 end; 9 / PL/SQL procedure successfully completed. SQL> print TOTAL_BLOCKS ------------ 96 <-- another 32 blocks added TOTAL_BYTES ----------- 393216 UNUSED_BLOCKS ------------- 32 <-- no change because this reports space above HWM UNUSED_BYTES ------------ 131072 <-- no change LAST_USED_EXTENT_FILE_ID ------------------------ 11 LAST_USED_EXTENT_BLOCK_ID ------------------------- 81 <-- Changed from 17 (meaning?) LAST_USED_BLOCK --------------- 32 <-- no change (meaning?) FREE_BLKS ---------- 33 <-- Changed from 1 because space is added below HWM SQL> exec dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) PL/SQL procedure successfully completed. SQL> select num_rows, blocks, empty_blocks, avg_space, avg_row_len from user_tables where table_name = 'TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN ---------- ---------- ------------ ---------- ----------- 10000 63 0 0 3 <-- Blocks changed from 31. It counts used data blocks, "used" meaning below HWM, regardless having data or not Comments on procedure free_blocks (or space_usage in case of ASSM): Running this procedure fully scans the table (or index) unless scan_limit is specified. This procedure just returns number of blocks on the freelist. For a regular table or index (for instance not LOB), I can't think of a good reason why we need this expensive operation while we can achieve the same in a very efficient way: dump segment header block and look at blocks under hwm (select 'alter system dump datafile ' || header_file || ' block ' || header_block || ';' from dba_segments where segment_name = '&TABLEorINDEX'; check trace file for "#blocks in seg. hdr's freelists:").