CODEWITHSUNDEEP
oracle-database
b.roth
b.roth

Reputation: 9552

Oracle 10 g - Unable to free up space in tablespace

The tablespace in Oracle 10g is almost 100% used.

Size (MB) = 571,768.0
Used (MB) = 571,534.0

I just deleted (and committed) thousands of records in a table that belongs to a schema associated with that tablespace. Surprisingly, no space was freed up according to the Tablespaces page on Enterprise Manager.

Question: is there anything that I need to do to force Oracle to release the space corresponding to the deleted records?

Upvotes: 3

Views: 21570

Answers (3)

FerranB
FerranB

Reputation: 36827

The space you are seeing is for SEGMENTS on tablespace.

A Tablespace contains Segments. Each Segment is associated to an Index or a Table. Segments contains extends, and extends contains blocks. An for a table, a block contains rows.

When you delete rows on a table you are freeing the space ocuppied by rows but the space for new segments remains equal.

To free this space you can try to:

ALTER TABLESPACE xxx COALESCE;

or

ALTER TABLE xxx MOVE;

The first one, will "combine all contiguous free extents into larger contiguous extents". Depending on your configuracion, this can be executed by Oracle automatically. Also, may be it does not free to much because the location of data relative to the highwatermark on segment.

The second one "lets you relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.". Be carefull with this because you need free space to achieve this, execute it against another tablespace or add more datafiles.

Upvotes: 6

Amit
Amit

Reputation: 143

If you are using oracle 10g or greater, you can purge the recycle bin using command 

purge recylebin

or even purge the contents related to the tablespace using 

purge tablespace

This should free up the space which is deleted but not yet available , please note this would be made available automatically when the space stress occurs for the given tablespace.

In addition, You can use the segment advisor to find all the segments that you can "shrink", and easily reclaim your space.
Read more at Segment Shrink

And the last one you can use with locally managed tablespaces

Alter Tablespace tablespace_name shrink space

This would free as much space as possible while maintaining other attributes.

Upvotes: 1

sangretu
sangretu

Reputation: 1208

Rebuild your indexes.

Upvotes: 0

Related Questions