Check the reclaimable space from schema by identifying unused or free space in tables, indexes, and tablespaces

Check Unused Space in Tables

This query helps identify tables with reclaimable space due to deleted or updated rows.

SELECT owner, segment_name, segment_type, tablespace_name,
bytes/1024/1024 AS size_mb,
(bytes - blocks * 8192)/1024/1024 AS reclaimable_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
ORDER BY reclaimable_mb DESC;

Identify Tables with High Wasted Space (Using DBMS_SPACE Package)

Note: Replace ‘SCHEMA_NAME’ and ‘TABLE_NAME’ with actual values.

DECLARE
    v_unformatted_blocks NUMBER;
    v_unformatted_bytes  NUMBER;
    v_fs1_blocks         NUMBER;
    v_fs1_bytes          NUMBER;
    v_fs2_blocks         NUMBER;
    v_fs2_bytes          NUMBER;
    v_fs3_blocks         NUMBER;
    v_fs3_bytes          NUMBER;
    v_full_blocks        NUMBER;
    v_full_bytes         NUMBER;
BEGIN
    DBMS_SPACE.UNUSED_SPACE(
        segment_owner      => 'SCHEMA_NAME',
        segment_name       => 'TABLE_NAME',
        segment_type       => 'TABLE',
        total_blocks       => v_unformatted_blocks,
        total_bytes        => v_unformatted_bytes,
        unused_blocks      => v_fs1_blocks,
        unused_bytes       => v_fs1_bytes,
        fs1_blocks         => v_fs2_blocks,
        fs1_bytes          => v_fs2_bytes,
        fs2_blocks         => v_fs3_blocks,
        fs2_bytes          => v_fs3_bytes,
        full_blocks        => v_full_blocks,
        full_bytes         => v_full_bytes
    );

    DBMS_OUTPUT.PUT_LINE('Reclaimable Space (MB): ' || (v_fs1_bytes + v_fs2_bytes + v_fs3_bytes) / 1024 / 1024);
END;
/

Check Unused Space in Indexes

SELECT owner, index_name, tablespace_name,
(num_rows * avg_leaf_blocks_per_key - leaf_blocks) * 8192 / 1024 / 1024 AS reclaimable_mb
FROM dba_indexes
WHERE owner = 'SCHEMA_NAME'
ORDER BY reclaimable_mb DESC;

Check Fragmented Tablespaces

SELECT tablespace_name, file_name,
bytes/1024/1024 AS total_size_mb,
(bytes - free_bytes)/1024/1024 AS used_size_mb,
free_bytes/1024/1024 AS free_size_mb
FROM dba_data_files
JOIN (SELECT tablespace_name, file_id, SUM(bytes) AS free_bytes
FROM dba_free_space GROUP BY tablespace_name, file_id)
USING (tablespace_name, file_id)
ORDER BY free_size_mb DESC;

Solution for Reclaiming Space

For Table and Indexes

If significant reclaimable space is found in table, you can shrink segments:

ALTER TABLE schema_name.table_name ENABLE ROW MOVEMENT;
ALTER TABLE schema_name.table_name SHRINK SPACE;
ALTER TABLE schema_name.table_name DISABLE ROW MOVEMENT;

Rebuild indexes:

ALTER INDEX schema_name.index_name REBUILD;