Re: How to calculate size of table including size of its indexes too.
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: hansrajsao@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 22 Jan 2008 16:30:14 -0700
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
Other related posts:
