Re: How much space do my data occupy?

In article <3j8ve6$oil_at_newsbf02.news.aol.com>, chrish7260_at_aol.com (ChrisH7260) says:
>
>Although, I don't know the exact makeup of ROWID off the top of my
>head,...
>part of it designates a block id. I have used a sql something like
>
>select sum(distinct(substr(rowidtochar(rowid), 1, 8)))
>from table;
>
>To determine the number of blocks actually occupied by a table. Then
>multiply by your blocking factor; usually 2k or 4k. That seems to be
>pretty
>accurate, although I'm not sure that the substr() should start at 1 for
>8,...
>I don't have the mannul here to check the position.
>
>Chris Hennessy
Try

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 ,table_in_question t where e.segment_name = upper('table_in_question') and e.segment_type = 'TABLE' group by e.blocks;

output should look like

ALLOCATED_BLKS		USED		PCT_USED
--------------		----		--------
2560			1728		67.5

Regards
GAG Received on Mon Mar 06 1995 - 21:19:16 CET