| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Deallocate unused (above high water mark)
There's always some confusion around this, as Oracle can't decide
internally what to call them: unused or empty.
Here's a bit I wrote up on the subject a couple years ago while doing
some capacity planning stuff. It was written mostly as a reminder to
myself.
DBA_TABLES.EMPTY_BLOCKS:
DMS_SPACE.UNUSED_SPACE:UNUSED BLOCKS:
blocks that are below the HWM and have never been used
The number unused blocks reported by DBMS_SPACE.UNUSED_SPACE is identical to the EMPTY_BLOCKS column in DBA_TABLES.
The difference is that the table must be analyzed for the EMPTY_BLOCKS column to be populated, whereas the UNUSED BLOCKS returned from the DBMS_SPACE.UNUSED_SPACE will return the correct value even if the table has not been analyzed, or has not been analyzed recently.
Jared
"Richard Foote" <richard.foote_at_bigpond.com>
Sent by: oracle-l-bounce_at_freelists.org
05/13/2004 06:20 AM
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: Re: Deallocate unused (above high water mark)
Hi Mladen,
Blocks that have been emptied are *not* considered empty blocks. Empty blocks are only those of the Drew Barrymore variety.
Simple test. Analyze table after you've deleted all the rows and check out the (lack of) difference in the empty_block count.
Cheers
Richard
 |
 |