Recovering logical space from a tablespace

Question

I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space.

A week ago I queried user_tablespaces and dba_data_files and noticed it had 20% of available logical space. I then procedeed with a cleanup and removed many records from tables in this tablespace. We were expecting to see a big increase in available space. Unfortunately when I queried the views I noticed the available space was now 20.5%.

Could this be due to data fragmentation? Can we "defrag" the tablespace somehow and recover the lost space? Or do we need to recreate the tablespace from scratch?

Answer 1

When you delete records there is nothing that automatically compacts the segment, therefore you will need to do a segment shrink to reclaim the space. Here is excerpt from the 11.2 Administrator's Guide on Reclaiming Wasted Space:

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

Objects with fragmented free space can result in much wasted space, and can impact database performance. The preferred way to defragment and reclaim this space is to perform an online segment shrink. This process consolidates fragmented free space below the high water mark and compacts the segment. After compaction, the high water mark is moved, resulting in new free space above the high water mark. That space above the high water mark is then deallocated. The segment remains available for queries and DML during most of the operation, and no extra disk space need be allocated.

Further down on the same page you can read this:

Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operations are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.

Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.

The page includes much more information on the issue including examples.

The "Segment Space and the High Water Mark" section from the Concepts Guide might also be useful.

Answer 2

Yes, it will be due to fragmentation.

To reclaim the space, first get a list of tables in the tablespace with the following query (ignoring partitions - edit your question if you're using them):

select distinct table_name from dba_tables where tablespace_name = 'DATA';

Then for each table, enable row movement:

alter table TABLEINDATAPARTITION enable row movement;

You can then shrink the table:

alter table TABLEINDATAPARTITION shrink space;

Then shrink the data files with:

alter database datafile '/path/to/my/file/data01.dbf' resize 20480M;

The datafile names can be obtained from the DBA_DATA_FILES view, which you are already aware of.