RE: Size of Table

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 14 Sep 2010 09:51:20 -0400
Message-ID: <9F7D209EDCAB47FCAC9604D8C59C6404_at_rsiz.com>

Do you want the size of the allocated segments, the size of the blocks that currently contain pieces of rows, or the physical length of the rows in the blocks summed up?  

Or something else?  

Really, I am not being obstuse, but rather trying to understand which meaning of size you have in mind. Because rows are stored in blocks and the amount of unoccupied space in each block is variable, the which size you mean is important. It is not unheard of for folks to give the number of rows in a table as the answer to your question.  

If you want to see how much space is allocated to the table, a query on dba_segments is useful.  

If you compute statistics and examine dba_tables for the table in question you can see the number of blocks, number of empty blocks, and avg_space. Presuming you know your block_size for the tablespace where the table resides (which you can get from dba_tablespaces), you can calculate the number of used blocks times the block size and subtract the extension of avg_space to get a net size. But that will include block overhead, such as space for interested transaction slots.  

If the function vsize will operate on all the columns in your table, then you can select the sum of the vsize of each column.  

If the size you need is not one of these meanings, let us know what you do want.  

Regards,  

mwf  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Abhishek Gurung
Sent: Tuesday, September 14, 2010 7:36 AM To: Oracle Freelist
Subject: Size of Table  

Hi

I want to know how to find the exact size of a table containing lots of data.

Regards
Abhishek

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

Received on Tue Sep 14 2010 - 08:51:20 CDT