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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
