calculating size of a table - Page 2

# Thread: calculating size of a table

1. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally Posted by Vipassana
I think you have added more confusion
I think it's about time you finaly do some reading of oracle documentation, in particular The Concepts Manual.

Untill you don't have at least a slightest idea what each of your two queries are doing and untill you don't know some of the very basic Oracle database vocabulary, any further explanation will only add more confusion. But nevertheless, I'll try to address some of your questions:

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".
I'll leave this for your homework - I hope you'll managed to find out which of your two queries is doing what.

What does "all the rows" mean here?
It means all the rows that are currently in your PRODUCT table. It means NUM_ROWS from your first query.

And also what does "how much space is needed..." mean?

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)?"
As I said, take a look at the Concepts manual and read about "blocks" and "segments" and "extents", about "data files" and "tablespaces". It will all become so clear......

Join Date
Dec 2001
Location
UK
Posts
1,684
OK. Let's make this simple. The lack of detail in this answer is intentional!

Consider this:

You insert 1,000,000 rows into a new table, causing it to increase in size so it can hold all the rows. At the end of this load process the table is 1M in size. It's safe to say that it takes 1M to store 1,000,000 rows.

You then decide to delete 999,999 rows, leaving a single row in the table. The table has not shrunk so it is still 1M in size, but obviously has lots of emty space in it. Can we use the same logic as before to make a conclusion? No. If we did we would be saying that it takes 1M to store 1 row. That's obviously nonsense.

So what?

Well, measuring the size of a table by summing all the blocks or bytes gives you the size of the table, not the size of the data it is holding.

Estimating the size of the data based on num_rows and avg_row_length gives you an idea of the size of the data, not the current size of the table holding it.

What next?

Decide what you actually want to know:

- The size of the table currently.
- The size of the data in the table.
- Something else.

Cheers

Tim...

Join Date
Jan 2001
Posts
2,828
Hi Tim

regards
Hrishy

Join Date
Jan 2001
Posts
2,828
Hi

Oops i should be calling you rather Prof Tim

regards
Hrishy

Join Date
Dec 2001
Location
UK
Posts
1,684
Originally Posted by hrishy
Hi Tim

regards
Hrishy
You wouldn't say that if you ever worked with me. I can be a right ....

Originally Posted by hrishy
Hi

Oops i should be calling you rather Prof Tim

regards
Hrishy
Where did that come from?

6. Member
Join Date
Jul 2003
Posts
134
Hmmm lots of details. Good, now atleast I am clear that I lack certain basic fundamentals. Will give it a reading... and get back if I still have some clarifications. Thanks for all your time and patience guys.

Join Date
Jan 2001
Posts
2,828
Originally Posted by TimHall
You wouldn't say that if you ever worked with me. I can be a right ....

Where did that come from?

Well thats becoz you had all the patience in the world to explain to the students here :-) Prof Tim

8. Junior Member
Join Date
Jul 2007
Posts
1

## URGENT: Table size in 9i

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?

Thanks and regards,
rcm

9. did you actually type the tablename in lowercase?

10. Junior Member
Join Date
Jun 2015
Posts
1
Just encountered this post and thought some of the people encountering it as well and that would like to calculate existing row and table sizes in order to calculate the entire sizing impact of new rows in the database can use a script I wrote. The script predicts the extra size needed for new rows inserted into a table with the table indexes and all refereing tables (with foreign keys) as well. Might be useful as it was to me...

You can find it here.
(http://www.oraclehotspot.com/2015/06...ct-of-new.html)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•