Re: HELP:How do I determine how much space is consumed by each table row?

From: Marc Marchioli <marcm_at_computek.net>
Date: 1995/09/14
Message-ID: <439pqg$1v9_at_news.computek.net>#1/1


In article <42pv5h$7vk_005_at_ujf-grenoble.fr>, Gilles.Bruno_at_ujf-grenoble.fr says...
>
>In article <42n5g1$8vr_at_gazette.medtronic.COM>, MasterG <martig4> wrote:
>>I have a table with a field which is a long raw data type. Is there any way to
>>determine how much space is being consumed by each row in this table? Also is
>>there a utility script one can run to determine how many blocks are used out of
>>those allocated for each table? Thanks in advance.
>/*
>
> table_usage.sql
>
> %W% %E% %U% SCCS ID
>
> script to check how full a table really is
>
> From "Oracle Performance Tuning"
>
> 11/22/93 SDB
>
>*/
>
>SET VERIFY OFF;
>UNDEFINE TAB_NAME;
>
>SELECT BLOCKS
> ALLOCATED_BLKS,
> COUNT(DISTINCT SUBSTR(T.ROWID,1,8) ||
> SUBSTR(T.ROWID,15,4)) USED, (COUNT(DISTINCT
> SUBSTR(T.ROWID,1,8) || SUBSTR(T.ROWID,15,4)) / BLOCKS)
> * 100 PCT_USED
> FROM
> SYS.DBA_SEGMENTS E,
> &&USER .&&TAB_NAME T
> WHERE
> E.SEGMENT_NAME = UPPER ('&&TAB_NAME')
> AND E.SEGMENT_TYPE = 'TABLE' GROUP BY
> E.BLOCKS ;
>
>
>
>-------------------------------------------------------------
> ('< Gilles BRUNO. Universite Joseph Fourier.
> ,',) Tel: +33 76 63 56 68 Fax: +33 76 51 42 74
> ''<< E-mail: Gilles.Bruno_at_ujf-grenoble.fr
>----""-------------------------------------------------------

Gilles has a good quick way. I have writtten a script to use the algorithm in the ORACLE Administrator's manual to calculate (really estimate) thhe actual size for the data in a table. I have found it quite good and use it regularly. See the posting Free ORACLE Reports and Utilities for UNIX (be carefull there are 8 parts). A Gilles, j'esper que je suis en France maintenant.pasc'est que j'ai habite en France dues ans. a bientot

Marc E. Marchioli	marcm_at_computek.net
The DataBase Group	Dallas, TX 75204
Received on Thu Sep 14 1995 - 00:00:00 CEST

Original text of this message