Re: How to calculate size of table including size of its indexes too.

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Tue, 22 Jan 2008 16:30:14 -0700
Message-ID: <47967C86.20304@optimaldba.com>

Here is a simple call to extract this information.

  1 SELECT UPPER('&&table_name'),

  2         s.segment_type,
  3         SUM(s.bytes)/1048576 megs

  4 FROM user_segments s
  5 WHERE (s.segment_name,s.segment_type)

  6                        IN (SELECT t.table_name,
  7                                   'TABLE'
  8                            FROM   user_tables t
  9                            WHERE  t.table_name = UPPER('&&table_name')
 10                            UNION
 11                            SELECT i.index_name,
 12                                   'INDEX'
 13                            FROM   user_indexes i
 14                            WHERE  i.table_name = UPPER('&&table_name')
 15                           )

 16 GROUP BY s.segment_type
 17* ORDER BY 1 DESC
SQL> /

old   1: SELECT UPPER('&&table_name'),
new   1: SELECT UPPER('EMPLOYEES'),
old   9:                           WHERE  t.table_name = 
UPPER('&&table_name')
new   9:                           WHERE  t.table_name = UPPER('EMPLOYEES')
old  14:                           WHERE  i.table_name = 
UPPER('&&table_name')
new  14:                           WHERE  i.table_name = UPPER('EMPLOYEES')

UPPER('EM SEGMENT_TYPE             MEGS
--------- ------------------ ----------
EMPLOYEES INDEX                    .375
EMPLOYEES TABLE                   .0625

-- 
Daniel Fink

Oracle Performance, Diagnosis and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com



hansraj sao wrote:


> Hi Guys,

>  

> I am trying calculate size of table including size of its indexes too. 

> i am using below script to do so.

>  

> select a.owner,a.table_name,sum(b.bytes/1024/1024) AS SIZES,a.partitioned

> from dba_tables a,dba_segments b

> where a.table_name=b.segment_name

> and table_name ='FND_USER'

> GROUP BY a.owner,a.table_name ,a.partitioned

>  

> But i do not think this will give me size of indexes too. i do not  

> see any column in dba_segments which relate tables with its indexes.

>  

> Any idea on this?

>  

> -- 

> Thanks,

> Hansraj

>  

> ------------------------------------------------------------------------

>

> No virus found in this incoming message.

> Checked by AVG Free Edition. 

> Version: 7.5.516 / Virus Database: 269.19.9/1237 - Release Date: 1/22/2008 11:04 AM

>   




--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 22 2008 - 17:30:14 CST