Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 4 down vote accepted

alter table {table_name} enable row movement;

alter table {table_name} shrink space;

share|improve this answer
1  
exactly! if you delete 90% of the rows in the table, each block can still have 10% of the rows. Doing a SHRINK SPACE moves the rows from the end of the table to the beginning and release the empty blocks at the end of the table (lowering the high water mark) –  Ofir Manor Jul 14 '09 at 19:26
1  
Note that automatic segment space management must be enabled for the tablespace in order for this to work or you get an ORA-10635. –  DCookie Jul 14 '09 at 20:45

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.

share|improve this answer
1  
Justin - ALTER TABLE... SHRINK SPACE is an in-place, online operation - no downtown, no complicated scripts. So, it is a better fit than manual, offline reorganization. –  Ofir Manor Jul 15 '09 at 6:49
    
It is an online operation, but it does lock the table briefly. It is not something that you generally ought to be doing during business hours-- it does have a fair amount of overhead. It is also not possible if you have ROWID based materialized views or function based indexes. And it's generally not something you'd want to do after deleting a few thousand rows. –  Justin Cave Jul 15 '09 at 19:42

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.