RE: Table Size

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 15 Oct 2003 12:24:25 -0800
Message-ID: <F001.005D3425.20031015122425@fatcity.com>

The query proposed by Anthony Hsu assumes that the tablespace has an 8K block size. Instead use the query below. Of course the result will be in bytes, not gigabytes, but any good DBA can instantly convert bytes to gigabytes or terabytes at a glance. :) P.S. I include LOB indexes in the table total. Some people might argue that those should be in the index total, not the table total, but I figure that without the LOB column those indexes wouldn't be there so they should properly be counted in the table total.

select sum (x.bytes)
from
(select a.bytes

  from dba_segments a
  where a.owner = '&&table_owner'

        and a.segment_name = '&&table_name'
        and a.segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

 union all
 select d.bytes
  from
    dba_tab_columns b, dba_lobs c, dba_segments d   where
    b.owner = '&&table_owner'

    and b.table_name = '&&table_name'
    and b.column_name = c.column_name
    and d.owner = c.owner
    and d.segment_name = c.segment_name
    and d.segment_type in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')

 union all
 select g.bytes
  from
    dba_tab_columns e, dba_lobs f, dba_segments g   where
    e.owner = '&&table_owner'

    and e.table_name = '&&table_name'
    and e.column_name = f.column_name
    and g.owner = f.owner
    and g.segment_name = f.index_name
    and g.segment_type in ('LOBINDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

) x ;

-----Original Message-----
Hsu, Anthony C., ,CPMS

Try:

SELECT segment_type, segment_name,BLOCKS*8192/1024 "Kb"

        FROM   DBA_SEGMENTS
        WHERE  OWNER=UPPER('<owner>') AND SEGMENT_NAME = UPPER('<table_name>');

 -----Original Message-----

    Could somebody help me in finding the actual size of an oracle table in GB.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Wed Oct 15 2003 - 15:24:25 CDT