In an Oracle 10g database, for data segments contained in a tablespace which is created with EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO, the storage can be adjusted online:
ALTER TABLE employees ENABLE ROW MOVEMENT;
ALTER TABLE employees SHRINK SPACE;
or
ALTER TABLE employees SHRINK SPACE CASCADE; --CASCADE includes indexes on the table;
To check if the table is a cadicate:
select tablespace_name, extent_management, segment_space_management
from dba_tablespaces
where tablespace_name = 'TS_EXP'
tablespace_name extent_management segment_space_management
TS_EXP LOCAL AUTO
