Home » RDBMS Server » Server Administration » Table Size
Table Size [message #209928] Mon, 18 December 2006 09:44 Go to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member
Hi,
I have to calculate the size of the tables in a schema.So i have issued this query:
select d.FILE_NAME,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.BYTES,s.TABLESPACE_NAME from dba_data_files d,dba_segments s where d.TABLESPACE_NAME=s.TABLESPACE_NAME and s.OWNER='XXX' order by s.BYTES; The output is:
FILE_NAME                            SEGMENT_NAME                 SEGMENT_TYPE    BYTES    TABLESPACE_NAME  Size(MB)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oramirr/oradata/AAAAA/XXX02.dbf         HHHH                          TABLE     4083154944     SSS         3894

/oramirr/oradata/AAAAA/XXX01.dbf         HHHH                           TABLE     4083154944     SSS        3894

This output says :the table size is 3894+3894. I know this couldnot be the size.B'cos the size of the datafie 1 and two are:

/oramirr/oradata/AAAAA/XXX01.dbf  - 8.0Gig /oramirr/oradata/AAAAA/XXX02.dbf  -1.1Gig 
My question is , how come the 3.8Gig table will resides in 1.1Gig.I donot figureout this.So the table size its showing here is wrong. So that i have to findout these things to get the size of the table:

1.Findout the the datafiles in which the table resides
2.What is the size of the table in each datafiles.
3.For this i must identify the segment id and the corresponding value of the each segment,so that i can add the size of each segment and get the exact size of the table.

Please help me to do the same.We are in urgent!!!

Also,
What is the buffer size of the Sun solaris 9.0 or 10?
How to calculate the buffer size while import the entire database or a schema?

Please help me.

-Raaj
Re: Table Size [message #209935 is a reply to message #209928] Mon, 18 December 2006 09:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1.Findout the the datafiles in which the table resides

Look at DBA_Extents. This will show you the file_id (from dba_data_files) and the size of each extent.

2.What is the size of the table in each datafiles.

Sum up the values from 1).

3.For this i must identify the segment id and the corresponding value of the each segment,so that i can add the size of each segment and get the exact size of the table.

Nope - you want to look at Extents, not Segments.

Also, the query you posted looks very iffy - the results that you posted were not generated by that query as there is no column "Size(Mb)" returned by the query.

The reason that you can't get the sizes to add up is that the query is returning a cartesian join between Dba_Data_files and Dba_segments.
Previous Topic: Reclaim undused space
Next Topic: Listener problem again 10g
Goto Forum:
  


Current Time: Thu Dec 08 02:29:52 CST 2016

Total time taken to generate the page: 0.07059 seconds