I am using Oracle 9.2 on Windows NT. I am using Enterprise Manager to monitor the tablespace. After deleting the records from a table, I found out that the space is still not empty in the tablespace map. Then I tried alter table mytable_name move. This deallocates the space, but the initial extent is located after some space. Is it fragmentation? How can I empty the space efficiently?
All database systems try to do as little work as possible. This lets us achieve very high throughput rates in highly active systems. When you remove records from a table, the database does not release that space back to the entire system. It is released back to the table to accommodate newly inserted rows though. But the space will still show up as allocated. Why release the space back to the system when the space will be acquired by new inserts in the future?
When you move the table to a new tablespace with the ALTER TABLE MOVE command, you are forcing the allocation of enough space to hold the table's data, and the releasing of the old space in the old tablespace. This is done by the rules you set up for the tablespace and extent management of that object. There will always be an initial extent, even if the table is empty.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.