# 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

Original text of this message