calculating size of a table

[Vipassana]

Does this forumla still work good in Oracle 10g for calculating the size of a table?

AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15

Or is there anyother better way to calculate? Pls advice.

[davey23uk]

select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' will give you the real table size

the above query never was accurate and was dependant on the table being analyzed right up to date

surely you have this information since you work for oracle?

[Vipassana]

hey thx dave. used to work for oracle, not anymore.

and so, u r saying we need not analyze the table if i am using

select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' ??

hmm.. big diff between this and the query i mentioned (though i was analyzed up to date)

[davey23uk]

of course you need to analyze the table for the CBO to work properly! How much of a difference?

[Vipassana]

select avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15
from dba_tables where table_name = 'PRODUCT'
/

AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15
------------------------------------------
13925.12

SQL> select sum(bytes) from user_segments where segment_name = 'PRODUCT';

SUM(BYTES)
----------
65536

[jmodic]

So? One is (aproximately) how much space is needed to store all those rows, the other is how much space is actually allocated by that table.

[Vipassana]

So? One is (aproximately) how much space is needed to store all those rows, the other is how much space is actually allocated by that table.

I think you have added more confusion

First of all which is which? I presume the first statement means "how much space is needed to store all those rows" and the second means "the other is how much space is actually allocated by that table".

Secondly I didnt understand what you meant by "how much space is needed to store all those rows". What does "all the rows" mean here? You mean the rows that are existing? And also what does "how much space is needed..." mean? You mean what is the maximum space it can occupy?

Thridly I didn't understand what you meant by "the other is how much space is actually allocated by that table". You mean how is actually used? If so "used out of what (how much)?"

Two statements you made and they are so cryptic

[TimHall]

Hi.

Sorry for coming late to the party, but if you're using 10g you might want to take look at this:

http://www.oracle-base.com/articles/...rce_estimation

It takes some of the guesswork out of sizing estimations and growth trends.

Cheers

Tim...

[bazza]

Vipassana, what do you want to know?

How big your table is now? - use dba_segments

How big your table could be? - use your formula and consider how many rows will be inserted and the rate they are inserted.

Perhaps a refresh of the concepts manual is in order.

Bazza

[mrameshchandra]

Hi,

the query

select sum(bytes) from user_segments where segment_name = 'tablename'; is giving sum(bytes) as blank, not even a zero. Please help. Does the user need dba permission to run this query?

Please reply asap.

Thanks and regards,
rcm