Re: How to retrieve how much disk space an Oracle table has occupied?

From: Sanjiv Singh <ssingh1961_at_yahoo.com>
Date: 13 Nov 2002 07:31:36 -0800
Message-ID: <36ee465c.0211130731.3a0f3514_at_posting.google.com>

johnny.yeung_at_unforgettable.com (Johnny Yeung) wrote in message news:<55b03025.0211130317.6c782fd6_at_posting.google.com>...
> Hi experts,
>
> I have gone through some system tables, like dba_tables, dba_extents.
> However, it seems I cannot get the size of a table inside Oracle. I
> believe it should be common for DBA who needs to monitor the storage
> on Oracle tables. Are there any scripts for getting this? Thanks in
> advance.
>
> Regards,
> Johnny.

Johnny,

Disclaimer: I am not an expert!

While there are certainly more sophisticated scripts/SQL to find table size; I use the table <dba_segments> and a query like:

select segment_name,

       tablespace_name, 
       bytes, blocks, extents EXT, 
       initial_extent I_EXT, 
       max_extents M_EXT

from dba_segments
and segment_type='TABLE'
where owner='<give a name of the schema owner>';

You will find that
bytes = total # of bytes of the table

blocks = # of blocks which when multiplied by block size (defined during DB creation and visible via show parameter or from V$PARAMETER view) should equal the number of bytes of the previous column

EXT = # of extents allocated currently

I_EXT = bytes in the initial extent consumed

M_EXT = The # of max. extents that can be allocated to this table; this is either the 'default storage' clause during tablespace creation or specified during table creation.

Ofcourse, you can change the query based on other columns of <dba_segments>. Hope this helps!

Sanjiv Received on Wed Nov 13 2002 - 16:31:36 CET