Re: num of blocks containing table data

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Tue, 09 Jun 1998 09:52:56 GMT
Message-ID: <357d0431.12187614@www.sigov.si>

On Tue, 09 Jun 1998 13:36:12 +0500, Umar FArooq <umar.farooq_at_cressoft.com.pk> wrote:

> Hi:
>Is there a way to know how many blocks of a table actually contain data.
>The blocks column of the user_tables gives teh high water mark blocks.
>BAsically I wish to know how many extra blocks are being visited by
>Oracle in case of a full table scan.
>

You can write a query to count all distinct "file and block sections" of rowids of that table. For Oracle7 you can use:

SELECT COUNT(DISTINCT SUBSTR(rowid,1,8) || SUBSTR(rowid,15,4)) FROM mytable;

In Oracle8, the format of ROWID has changed, check the manuals about the exact format.

>Thx.
>
>Umar Farooq Chaudhri
>umar.farooq_at_cressoft.com.pk

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jun 09 1998 - 04:52:56 CDT