| Table Size [message #209928] |
Mon, 18 December 2006 09:44  |
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
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  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
