Shrinking space of a table after deleting data

Using ASSM to reclaim and reuse storage of a segment / table

select count(*) from user_extents where segment_name='ERROR_LOG';

COUNT(*)
20


truncate table ERROR_LOG reuse storage;


select count(*) from user_extents where segment_name='ERROR_LOG';

COUNT(*)
17

Enable row movement for table to shrink space

SQL> alter table error_log enable row movement

table altered

SQL> alter table error_log shrink space

table altered


select count(*) from user_extents where segment_name='ERROR_LOG';

COUNT(*)
1

Note: you can reclaim / shrink space of only those tables which are located in a table which SEGMENT SPACE MANAGEMENT is automatic

You can also shrink the space associated with any index segments via the CASCADE clause:

SQL> alter table inv shrink space cascade;


When you shrink a table, Oracle re-organizes the blocks in a manner that consumes the least amount of
space. Oracle also re-adjusts the table’s high-water mark. This has performance implications for queries
that result in full table scans. In these scenarios, Oracle will inspect every block below the high-water
mark. If you notice that it takes a long time for a query to return results when there aren’t many rows in
the table, this may be an indication that there are many unused blocks (because data was deleted) below
the high-water mark.
You can instruct Oracle to not re-adjust the high-water mark when shrinking a table. This is done via
the COMPACT clause—for example:

SQL> alter table inv shrink space compact;

When you use COMPACT, Oracle defragments the table but doesn’t alter the high-water mark. You will
need to use the ALTER TABLE…SHRINK SPACE statement to reset the high-water mark. You might want to
do this because you’re concerned about the time it takes to defragment and adjust the high-water mark.
This allows you to shrink a table in two shorter steps instead of one longer operation.