How full are a table's blocks, and how much space would a new table need? DBMS_SPACE answers both. SPACE_USAGE reports how many blocks of a segment are full and how many have free space, and CREATE_TABLE_COST estimates the space a table would take from its row size and count.
Code for This Guide
The main examples are in the examples/pkg-performance folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_space.space_usage(owner, name, type,
unformatted_blocks, unformatted_bytes,
fs1_blocks, fs1_bytes, fs2_blocks, fs2_bytes, -- 0-25%, 25-50% free
fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, -- 50-75%, 75-100% free
full_blocks, full_bytes);
dbms_space.create_table_cost(tablespace, avg_row_size, row_count, pct_free,
used_bytes, alloc_bytes);SPACE_USAGE works for segments in tablespaces with automatic segment space management, the default.
Usage and an Estimate
Example:
declare
v_unf number; v_unf_b number; v_fs1 number; v_fs1_b number; v_fs2 number;
v_fs2_b number; v_fs3 number; v_fs3_b number; v_fs4 number; v_fs4_b number;
v_full number; v_full_b number; v_used number; v_alloc number;
begin
dbms_space.space_usage(user, 'FLIGHTS', 'TABLE', v_unf, v_unf_b, v_fs1, v_fs1_b, v_fs2,
v_fs2_b, v_fs3, v_fs3_b, v_fs4, v_fs4_b, v_full, v_full_b);
dbms_output.put_line('FLIGHTS: ' || v_full || ' full blocks, '
|| (v_fs1 + v_fs2 + v_fs3 + v_fs4) || ' with free space');
-- what would a table of 10 million 120-byte rows need?
dbms_space.create_table_cost('USERS', 120, 10000000, 10, v_used, v_alloc);
dbms_output.put_line('10 million rows: ' || round(v_used / 1024 / 1024)
|| ' MB used, ' || round(v_alloc / 1024 / 1024) || ' MB allocated');
end;
/Output:
FLIGHTS: 32 full blocks, 3 with free space 10 million rows: 1324 MB used, 1344 MB allocated PL/SQL procedure successfully completed.
FLIGHTS has 32 full blocks and 3 with some free space. A table of 10 million rows of 120 bytes, with 10 percent free space per block, would use about 1.3 GB, allocated in slightly larger extents.
Things to Know
- Many blocks with lots of free space after large deletes suggest that the table could be shrunk.
- CREATE_TABLE_COST has a second form that takes the column types instead of an average row size.
- For indexes, pass the index name and type 'INDEX' to SPACE_USAGE.
Related Guides
Conclusion
DBMS_SPACE.SPACE_USAGE shows how full a segment's blocks are, and CREATE_TABLE_COST estimates the space a planned table needs. Use them to plan storage and to spot tables worth reorganizing.
