Re: how to check table size in oracle 9i

From: <fitzjarrell_at_cox.net>
Date: 17 Jun 2006 18:59:45 -0700
Message-ID: <1150595985.638634.197400@h76g2000cwa.googlegroups.com>

vasant.naidu_at_gmail.com wrote:
> manish wrote:
> > how to check table size in oracle 9i
> >
> > PLz help
>
> Hi,
>
> Let me walk you through a example.
>
> I want to find the size for the following table:
>
> OWNER TABLESPACE_NAME
> TABLE_NAME
> ------------------------------ ------------------------------
> ------------------------------
> PERF USERS
> BACKUP_DETAIL
>
> SQL> show parameter db_block_size
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> db_block_size integer 8192
>
> You can calculte in 2 ways both are mentioned below:
>
> select owner,segment_name,
> bytes/1024 as totsize_bytes,
> (blocks*8192)/1024 as totsize_block
> from dba_segments
> where owner='PERF'
> and segment_name='CONN';
>
> OWNER SEGMENT_NA BYTESIZE BLOCKSIZE
> ------ ---------- ---------- ----------
> PERF CONN 64 64
>
> Hope this helps.
>
> Regards,
> Vasant

That's wonderful, but where, exactly, is the size for the BACKUP_DETAIL table? I see the size for the CONN table, which, according to your stated problem, isn't relevant to the discussion. And why is the calculated byte size based upon the blocks and db_block_size still referred to as BLOCKSIZE? It isn't, it's the total bytes based upon the block count; the column header is misleading. You also assume, possibly wrongly, that the user has DBA access.

The query should be thus for a table in the user's schema:

select segment_name,

            bytes/1024 as totsize_bytes,
            (blocks*8192)/1024 as totbytes_by_blocks

from user_segments
where segment_name = upper('&1');

Should the user possess DBA privileges in the database:

select owner, segment_name,

            bytes/1024 as totsize_bytes,
            (blocks*8192)/1024 as totbytes_by_blocks

from dba_segments
where owner = upper('&1')
and segment_name = upper('&2');

These are as generic as possible, allowing the user to submit any valid segment name (and owner, in the dba_segments query) in order to produce results. Of course to truly answer the original question one must also restrict the segment_type to 'TABLE':

"how to check table size in oracle 9i"

Therefore the query would be:

select segment_name,

            bytes/1024 as totsize_bytes,
            (blocks*8192)/1024 as totbytes_by_blocks

from user_segments
where segment_name = upper('&1')
and segment_type = 'TABLE';

Or:

select owner, segment_name,

            bytes/1024 as totsize_bytes,
            (blocks*8192)/1024 as totbytes_by_blocks

from dba_segments
where owner = upper('&1')
and segment_name = upper('&2')
and segment_type = 'TABLE';

As for the size of the BACKUP_DETAIL table, well, we're still waiting for that answer. Possibly you can finally provide that information.

David Fitzjarrell Received on Sat Jun 17 2006 - 20:59:45 CDT