Oracle 10 g - Unable to free up space in tablespace

Question

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?

Answer 1

alter table {table_name} enable row movement;

alter table {table_name} shrink space;

Answer 2

The page you are looking at in Enterprise Manager is almost certainly reporting free space by comparing DBA_DATA_FILES (which tells you the total sizes of the various files allocated to a tablespace) with DBA_SEGMENTS (which tells you the total size of all the segments allocated to objects in the tablespace). This will not change simply because you deleted some data.

When you delete data, you free up space within the blocks and extents allocated to a particular object. So if you delete 100 MB worth of data from the FOO table (and associated FOO indexes), the size of the FOO segment would not decrease. But there would now be space in that segment that would accommodate another 100 MB worth of inserts into FOO. If you are deleting space from FOO in order to free up space for other segments in the tablespace, you would need to reorganize FOO after deleting the data-- this is a somewhat involved process that likely requires downtime as well as a fair bit of testing. Unless you are permanently reducing the size of an object, it is generally not advisable-- if FOO is eventually going to see another 100 MB of inserts, it's not beneficial to shrink the FOO segment only to have it grow again.

You can use the DBMS_SPACE package to see how much space is available in different segments. That will tell you about empty blocks as well as blocks that are in various states of being filled (0-25% full, 25-50% full, 50-75% full, and 75-100% full). When you delete data, you will generally cause an increase in the number of empty and partially empty blocks allocated to a table.

Answer 3

Just as a follow-on to the accepted answer, you can use the Segment Advisor (via a call to dbms_advisor) to estimate the space savings of running the shrink space operation. [You don't need a license pack to run the segment advisor]

You could then save some time and just target the segments where you'd get most benefit.

Answer 4

Usually it is better to create a temporary table as create table temp_table name as select * from table_you_deleted_from; then drop the indexes on the original table disable constraisnt pointing to that table and then drop and recreate this table again as create table table_you_deleted_from name as select * from temp_table; recreate you indexes and enable the constraints.

Here you basically reduced your table size and index tree length, i.e. faster retrival going forward.