Re: How to determine used blocks in an extent.

In article <347f4595.0_at_news1.ibm.net>,

   "Roy Varghese" <rvarghese_at_ibm.net> wrote:
>I have a table that has been allocated a very large initial
>extent. Something like 1.7GB. It is contained in a tablespace
>of 2 GB. Only an ANALYZE TABLE would show the actual number of
>blocks which are free or used-up in the extent. However the
>ANALYZE TABLE command takes quite a long time to complete
>since there are too many rows in the table.
>
>Can someone suggest a faster way of determining the blocks'
>usage within the extent?

Try: select count(distinct(substr(rowidtochar(rowid),1,8))) from <table name>;

This will return the number of blocks that contain at least one row. Note that blocks that contain only chained and/or migrated rows are not included in this count.

Stefan.

--
Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.