Re: table size

From: Jurij Modic <jmodic_at_src.si>
Date: Thu, 29 Oct 1998 09:19:53 GMT
Message-ID: <36382f9c.5935883@news.siol.net>

On Wed, 28 Oct 1998 20:05:43 -0800, "Winnie Liu" <oracle_dba_at_zdnetmail.com> wrote:

>How come no one remember the old method now... to find out the number of
>blocks used by the tables, use the ROWID of each row:
>
>select count(*) from
>(select distinct(substr(rowid,1,8)) from table1);
>
>you will get the number of oracle block used by the the whole table.

Actualy you also have to include the file_id portion of rowid into count as segments can span multiple files, eg

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

This method however does not count the blocks occupied by chained/migrated rows.

And also note that this is Oracle7 specific, in Oracle8 the rowid structure has changed.

>Winnie Liu
>Certified DBA

Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Oct 29 1998 - 03:19:53 CST