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?
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.
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.