Oracle 10g Table size after deletion of multiple rows
As part of maintenance, we remove a considerable amount of records from one table and wish to dispose of this space for other table. The thing is that I'm checking the size of the table and it shows the same original size before the delete. What am I missing here?
To check the size, I'm looking at the dba_segments.
Answer
There are some concepts you need to know about tables before you can really understand space usage. I will try and give you the 5 second tour ...
A table is made up of extents, these can vary in size, but lets just assume they are 1MB chunks here.
When you create a table, normally 1 extent is added to it, so even though the table has no rows, it will occupy 1MB.
When you insert rows, Oracle has a internal pointer for the table known as the high water mark (HWM). Below the HWM, there are formatted data block, and above it there are unformated blocks. It starts at zero.
If you take a new table as an example, and start inserting rows, the HWM will move up, giving more and more of that first extent to be used by the table. When the extent is all used up, another one will be allocated and the process repeats.
Lets says you fill a three 1MB extents and then delete all the rows - the table is empty, but Oracle does not move the HWM back down, or free those used extents. So the table will take 3MB on disk even though it is empty, but there is 3MB of free space to be reused in it. New inserts will go into that space below the HWM until it is filled up again before the HWM is move again.
To recover the space, if your table is using ASSM, you can use the command:
alter table t1 shrink space;
If you are not using ASSM, the you need to think about a table reorg to recover the space.