Oracle table still slow and huge after a mass DELETE: find the high water mark and reclaim space

#Symptom

You've purged 90% of a table. Then:

• Full table scans take as long as before.
• SELECT COUNT(*) on a nearly empty table is slow.
• DBA_SEGMENTS shows the same size as it did before.
• The tablespace has no more free space than it did.

That's expected. DELETE empties rows from blocks, but it doesn't lower the segment's high water mark (HWM). The HWM marks the highest block that's ever been used, and a full scan reads every block below it, including empty ones.

Before DELETE: [data][data][data][data][data][data] HWM
After DELETE:  [data][    ][    ][    ][    ][    ] HWM   ← scan reads all of it
After SHRINK:  [data] HWM

#Step 1: measure the waste

Compare actual data with allocated space, after refreshing statistics:

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'APP', tabname => 'ORDERS_HIST', cascade => TRUE);

SELECT t.table_name,
       t.num_rows,
       t.avg_row_len,
       t.blocks AS blocks_below_hwm,
       ROUND(t.blocks * ts.block_size / 1024 / 1024) AS scanned_mb,
       ROUND(t.num_rows * t.avg_row_len / 1024 / 1024) AS actual_data_mb,
       ROUND(s.bytes / 1024 / 1024) AS segment_mb
FROM dba_tables t
JOIN dba_segments s
  ON s.owner = t.owner AND s.segment_name = t.table_name AND s.segment_type = 'TABLE'
JOIN dba_tablespaces ts
  ON ts.tablespace_name = t.tablespace_name
WHERE t.owner = 'APP' AND t.table_name = 'ORDERS_HIST';

If scanned_mb is several times actual_data_mb, the table's mostly empty space. Partitioned tables need DBA_TAB_PARTITIONS.

For a block-level view, use DBMS_SPACE:

SET SERVEROUTPUT ON
DECLARE
  l_unf NUMBER; l_unf_b NUMBER;
  l_fs1 NUMBER; l_fs1_b NUMBER;
  l_fs2 NUMBER; l_fs2_b NUMBER;
  l_fs3 NUMBER; l_fs3_b NUMBER;
  l_fs4 NUMBER; l_fs4_b NUMBER;
  l_full NUMBER; l_full_b NUMBER;
BEGIN
  DBMS_SPACE.SPACE_USAGE('APP', 'ORDERS_HIST', 'TABLE',
    l_unf, l_unf_b, l_fs1, l_fs1_b, l_fs2, l_fs2_b,
    l_fs3, l_fs3_b, l_fs4, l_fs4_b, l_full, l_full_b);
  DBMS_OUTPUT.PUT_LINE('Full blocks: ' || l_full);
  DBMS_OUTPUT.PUT_LINE('0-25% free: ' || l_fs1);
  DBMS_OUTPUT.PUT_LINE('25-50% free: ' || l_fs2);
  DBMS_OUTPUT.PUT_LINE('50-75% free: ' || l_fs3);
  DBMS_OUTPUT.PUT_LINE('75-100% free: ' || l_fs4);
END;
/

A large 75-100% free count is your empty space. Check the tablespace type too, because SHRINK needs ASSM:

SELECT tablespace_name, segment_space_management
FROM dba_tablespaces
WHERE tablespace_name = (SELECT tablespace_name FROM dba_tables
                         WHERE owner = 'APP' AND table_name = 'ORDERS_HIST');

#Step 2: decide whether you need to do anything

Reclaiming isn't always worth it:

Inserts with /*+ APPEND */ go above the HWM and don't reuse free space. So a job that does mass deletes followed by direct-path inserts will keep growing, whatever you do. Fix that pattern, or the problem will return.

#Step 3: choose a method

#Option A: SHRINK SPACE

ALTER TABLE app.orders_hist ENABLE ROW MOVEMENT;

-- Phase 1: move rows down. DML can continue. Doesn't move the HWM.
ALTER TABLE app.orders_hist SHRINK SPACE COMPACT;

-- Phase 2: lower the HWM. Takes a short exclusive lock, so run in a quiet period.
ALTER TABLE app.orders_hist SHRINK SPACE;

-- Include dependent indexes
ALTER TABLE app.orders_hist SHRINK SPACE CASCADE;

ALTER TABLE app.orders_hist DISABLE ROW MOVEMENT;

Doing this in two phases keeps the locking short. It moves rows one by one, so it creates a lot of undo and redo. Watch archive log space, or you might hit ORA-00257. Standbys also have to apply all of that.

Warning

Row movement changes ROWIDs. Any application or job that stores ROWIDs will find wrong or missing rows. Restrictions also apply: SHRINK isn't available for all compressed tables, or tables with some index or column types, such as LONG. If it isn't allowed, Oracle returns an error, and you should check the documentation for your version.

#Option B: online MOVE

From 12.2, this runs while DML continues, and it maintains indexes:

ALTER TABLE app.orders_hist MOVE ONLINE TABLESPACE app_data;

It needs enough free space for the new copy, and it rebuilds the table completely. Without ONLINE, a move blocks writes and marks the indexes UNUSABLE. If that's what you did, check:

SELECT index_name, status
FROM dba_indexes
WHERE table_owner = 'APP' AND table_name = 'ORDERS_HIST' AND status <> 'VALID';

Rebuild any that are listed: ALTER INDEX app.idx_name REBUILD ONLINE;.

#Option C: TRUNCATE

If the table should be emptied, this is instant, and it resets the HWM:

TRUNCATE TABLE app.staging_orders;

Caution

TRUNCATE is DDL. It commits right away and can't be rolled back, and Flashback Query won't bring the rows back. Check the table name twice, and make sure that you've got a backup.

To keep only a small part of the rows, copy them, truncate, and insert them again during a maintenance window. Foreign keys that reference the table have to be handled first.

For recurring purges, use partitioning. Dropping an old partition is immediate, and it causes none of this. See partitioning large tables.

#Option D: online redefinition

If the table can't tolerate the other methods, DBMS_REDEFINITION builds a new copy while the table stays in use, and it swaps them at the end. It takes more steps, and it's best rehearsed. Check the Oracle documentation and test it on a copy first.

#Step 4: verify and reclaim disk space

Gather statistics again, as the optimizer needs to know about the change:

EXEC DBMS_STATS.GATHER_TABLE_STATS('APP', 'ORDERS_HIST', cascade => TRUE);

SELECT blocks, num_rows FROM dba_tables WHERE owner = 'APP' AND table_name = 'ORDERS_HIST';

SELECT ROUND(bytes / 1024 / 1024) AS mb FROM dba_segments
WHERE owner = 'APP' AND segment_name = 'ORDERS_HIST';

Compare query performance with the same statement and consistent gets. Plans may change, so check your key queries against their baselines.

Free space goes back to the tablespace, not to the OS. To return it, you can shrink the data file, down to the highest used block:

SELECT file_name, ROUND(bytes / 1024 / 1024) AS mb
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA';

ALTER DATABASE DATAFILE '/u02/oradata/APP/app_data01.dbf' RESIZE 20G;

If you see ORA-03297: file contains used data beyond requested RESIZE value, a segment still sits near the end of the file. Choose a larger size, or move that segment first. See tablespace full for related commands.

#Run it safely

#Prevent it next time

• Partition tables that have regular purges, and drop old partitions.
• Purge in small batches, on a schedule, rather than all at once every few years.
• Don't mix mass deletes with APPEND inserts on the same table.
• Track segment size against row counts for your biggest tables.
• Keep the Oracle DBA cheat sheet handy for space queries.