I have a tablespace of 100 MB. Current usage is that 70 MB has 6 Lakh records. Now if I delete 4 lakh records, will the tablespace show more free space? My experience is that the size never changes even if data is deleted. Why is it so? No one has yet anwsered this question, even Mr. Tom Kyte of Oracle. I am hopeful of an anwser from you.
Actually Tom has answered this question several times on his site. You can find the following answer at http://asktom.oracle.com/pls/ask/f?p=4950:8:267180628148108421::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1336002638218,
When an extent is allocated to a table (or index, etc) it belongs to that table until you either drop or truncate the table.
When you delete the data from the table -- the blocks will go onto the freelist for that table (assuming the amount of space on a block that was freed fell below the pctused). These blocks will be used for subsequent inserts and updates into this table.
When you delete data from the index -- if the block the index entry was on is now "empty" -- that block will go back onto the freelist to be used anywhere in the index struct. Else that block stays where it is and data that would naturally go onto that block (because of where it is in the b*tree) will go there.
Space is effectively reused when you delete. Your database will not show any new free space in dba_free_space -- it will have more blocks on freelists and more empty holes in index structures.
So....the answer is that once space is allocated it will never again show up as "free space" unless the storage is deallocated (using drop or truncate). That's why you don't see any difference.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.