如何查询由Direct Path /*+ APPEND */插入导致的表空间浪费量？

Great question! Since your table has only had Direct Path Inserts (no deletes, updates, or conventional inserts), the wasted space here is exactly the unused blocks below the high water mark (HWM)—because Direct Path Insert skips those entirely and creates new blocks above the HWM every time. Here are a few reliable ways to calculate that wasted space:

1. Quick Estimation Using Data Dictionary Views #

This method uses stats from USER_TABLES and USER_SEGMENTS to get a ballpark figure. Note: it relies on up-to-date table statistics, so refresh them first if they're stale.

First, refresh stats (run one of these):

-- Option 1: Analyze table (older approach)
ANALYZE TABLE YOUR_TABLE_NAME COMPUTE STATISTICS;

-- Option 2: Use DBMS_STATS (preferred for modern Oracle versions)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'YOUR_SCHEMA_NAME', TABNAME => 'YOUR_TABLE_NAME');

Then run the query to calculate wasted space:

SELECT
    t.table_name,
    ROUND((t.blocks * s.block_size)/1024/1024, 2) AS hwm_total_mb,
    ROUND((t.num_rows * t.avg_row_len)/1024/1024, 2) AS actual_used_mb,
    ROUND(((t.blocks * s.block_size) - (t.num_rows * t.avg_row_len))/1024/1024, 2) AS wasted_mb
FROM
    user_tables t
JOIN
    user_segments s ON t.table_name = s.segment_name
WHERE
    t.table_name = 'YOUR_TABLE_NAME'; -- Replace with your table name

2. Precise Calculation with DBMS_SPACE Package #

For a more accurate measurement, use Oracle's built-in DBMS_SPACE.UNUSED_SPACE procedure. This directly queries the segment's space usage details without relying on statistics.

Run this PL/SQL block:

DECLARE
    l_total_blocks NUMBER;
    l_total_bytes NUMBER;
    l_unused_blocks NUMBER;
    l_unused_bytes NUMBER;
    l_last_used_extent_file_id NUMBER;
    l_last_used_extent_block_id NUMBER;
    l_last_used_block NUMBER;
BEGIN
    DBMS_SPACE.UNUSED_SPACE(
        segment_owner => 'YOUR_SCHEMA_NAME', -- Replace with your schema
        segment_name => 'YOUR_TABLE_NAME',   -- Replace with your table
        segment_type => 'TABLE',
        total_blocks => l_total_blocks,
        total_bytes => l_total_bytes,
        unused_blocks => l_unused_blocks,
        unused_bytes => l_unused_bytes,
        last_used_extent_file_id => l_last_used_extent_file_id,
        last_used_extent_block_id => l_last_used_extent_block_id,
        last_used_block => l_last_used_block
    );

    DBMS_OUTPUT.PUT_LINE('Space Waste Details for ' || 'YOUR_TABLE_NAME' || ':');
    DBMS_OUTPUT.PUT_LINE('Total Segment Size: ' || ROUND(l_total_bytes/1024/1024, 2) || ' MB');
    DBMS_OUTPUT.PUT_LINE('Unused (Wasted) Space: ' || ROUND(l_unused_bytes/1024/1024, 2) || ' MB');
END;
/

Since your table has no deletes or updates, the unused_bytes value here is exactly the empty space below the HWM that Direct Path Insert never touched—pure wasted space.

3. Visualize Extent Allocation (Optional) #

To confirm that Direct Path Insert is only adding new extents above the HWM, you can check the segment's extents:

SELECT
    extent_id,
    blocks,
    ROUND(bytes/1024/1024, 2) AS mb,
    block_id
FROM
    dba_extents
WHERE
    owner = 'YOUR_SCHEMA_NAME'
    AND segment_name = 'YOUR_TABLE_NAME'
ORDER BY
    extent_id;

You'll notice earlier extents have mostly unused blocks, while newer extents hold your actual data—this confirms where the waste is happening.

Bonus: How to Recover Wasted Space #

If you want to reclaim that wasted space, run:

ALTER TABLE YOUR_TABLE_NAME MOVE;

This reorganizes the table to pack data below the HWM, then lowers the HWM to match the actual data size. Important: Rebuild any indexes on the table afterward, as the MOVE operation invalidates them:

ALTER INDEX YOUR_INDEX_NAME REBUILD;

内容的提问来源于stack exchange，提问作者Matthew Moisen