Home » RDBMS Server » Server Administration » table size
table size [message #527868] Thu, 20 October 2011 09:12 Go to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
19 35 2810 3

Ok, the above shows us:

- we have 55 blocks allocated to the table (still)
- 35 blocks are totally empty (above the HWM)
- 19 blocks contains data (the other block is used by the system)
- we have an average of about 2.8k free on each block used.

Therefore, our table

- consumes 19 blocks of storage in total.
- of which 19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

not too sure this calculation is accurate for getting the size (data)of the table.
Re: table size [message #527869 is a reply to message #527868] Thu, 20 October 2011 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: table size [message #527876 is a reply to message #527868] Thu, 20 October 2011 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once more:

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: table size [message #527906 is a reply to message #527876] Thu, 20 October 2011 14:28 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
ops$tkyte>

So that's how you named your user? ./fa/5767/0/
Re: table size [message #527940 is a reply to message #527906] Thu, 20 October 2011 22:06 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /

BLOCKS      EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
19              35          2810           3


Ok, the above shows us:

- we have 55 blocks allocated to the table (still)
- 35 blocks are totally empty (above the HWM)
- 19 blocks contains data (the other block is used by the system)
- we have an average of about 2.8k free on each block used.

Therefore, our table

- consumes 19 blocks of storage in total.
- of which 19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

not too sure this calculation is accurate for getting the size (data)of the table.
Re: table size [message #527946 is a reply to message #527940] Thu, 20 October 2011 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want size of the data then user AVG_ROW_LEN*NUM_ROWS, assuming the statistics are up to date (which is also assume in your query).

Regards
Michel

[Updated on: Fri, 21 October 2011 05:28]

Report message to a moderator

Re: table size [message #527956 is a reply to message #527946] Fri, 21 October 2011 01:07 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Fri, 21 October 2011 12:18
If you want size of the data then user AVG_ROW_LEN*NUM_ROWS, assuming the statistics are up to date


you mean:

alter table t compute statistics;

select avg_row_len*num_rows from dba_tables where table_name='T';


will give your the size (in K) of your data in the table?
Re: table size [message #527960 is a reply to message #527956] Fri, 21 October 2011 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean this but NOT using alter table, using dbms_stats.

And yes this will give you the actual size of your data as they are stored. Now I don't know what you can do with this information.

Regards
Michel

[Edit: missing word]

[Updated on: Fri, 21 October 2011 05:28]

Report message to a moderator

Re: table size [message #527988 is a reply to message #527960] Fri, 21 October 2011 02:53 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Fri, 21 October 2011 14:24
I mean this but NOT using alter table, using dbms_stats.

Regards
Michel


oh is actually analyze table t compute statistics;

why use dbms_stats?

Does using the analyze...screw up the table optimizer/ execution plan?

Quote:

And yes this will give you the actual size of your data as they are stored. Now I don't what you can do with this information.


when people ask what is the size of your table. MEaning the data in the table used up how size/space in the table.

So :

select byte/1024/1024 from dba_segments where segment_name='T';


also give you the size of your data in the table?

Re: table size [message #527998 is a reply to message #527988] Fri, 21 October 2011 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
oh is actually analyze table t compute statistics;

why use dbms_stats?

Because the former is obsolete and optimizer is now written for dbms_stats statistics.

Quote:
also give you the size of your data in the table?

No, it gives the size allocated to your segment.

Regards
Michel
Re: table size [message #528025 is a reply to message #527998] Fri, 21 October 2011 05:15 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
in that case, my posted code is of what use? Cos i thot that was the way to find out the data (used) in the table.
Re: table size [message #528032 is a reply to message #528025] Fri, 21 October 2011 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
in that case, my posted code is of what use?


This is what I said:

Quote:
Now I don't know what you can do with this information.


Regards
Michel
Re: table size [message #528034 is a reply to message #528032] Fri, 21 October 2011 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Better tell us what information you need and we'll tell you how to get it.

Regards
Michel
Re: table size [message #528141 is a reply to message #527868] Sat, 22 October 2011 02:24 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
so what it means when people mention they want to know the size of the table being used/ occupy so far.

How to go about doing that. I am still confuse and trying to really understand it.

When you mention, allocated to the table. U mean the tablespace you assign to the table.

create tablespace ts_something
  logging
  datafile '/dbf1/ts_sth.dbf' 
  size 32m 
  autoextend on 
  next 32m maxsize 2048m
  extent management local;


then

create table t ....tablespace ts_somthing...

so can i said initial allocated size is 32MB and is empty.

so when there is data inserted , how do one get the used space of the 32MB?
Re: table size [message #528144 is a reply to message #528141] Sat, 22 October 2011 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts

Please read it, your lack of the basics does not allow you to understand.

Regards
Michel
Re: table size [message #528148 is a reply to message #528144] Sat, 22 October 2011 03:18 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Sat, 22 October 2011 15:45
Database Concepts

Please read it, your lack of the basics does not allow you to understand.




i have been reading...and just need some help to understand it further.That's why have posted some code to help ..hands on ..
Re: table size [message #528155 is a reply to message #528148] Sat, 22 October 2011 05:03 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
You seem to be confusing two figures:

1. The space used within a table segment by the rows.
2. The space used by the table segment in the tablespace.

Have you really read the Concepts Guide?
http://www.oracle.com/pls/db112/to_toc?pathname=server.112%2Fe25789%2Ftoc.htm&remark=portal+%28Books%29
Chapters 11 and 12, but of course you must read the entire book. Several times.

(edit: corrected the URL)

[Updated on: Sat, 22 October 2011 05:05]

Report message to a moderator

Re: table size [message #528158 is a reply to message #528155] Sat, 22 October 2011 05:33 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
ok...several times...the OCA) oracle press book might not be sufficient.
Re: table size [message #528159 is a reply to message #528158] Sat, 22 October 2011 05:38 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
I wrote the OCA Oracle Press Book. Many readers have told me that the relationship between logical and physical storage is explained perfectly. What did you not understand?
Re: table size [message #528807 is a reply to message #528159] Thu, 27 October 2011 04:22 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
i am at the managing tablespace.

it did not mention much about the table size I guess it is on the chap managing schema object / table chap?
Re: table size [message #528812 is a reply to message #528807] Thu, 27 October 2011 05:09 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Do you have the full book, or a stolen copy of it on CD?
Re: table size [message #528847 is a reply to message #528812] Thu, 27 October 2011 07:58 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
John Watson wrote on Thu, 27 October 2011 18:09
Do you have the full book, or a stolen copy of it on CD?



oei, i did not stole it. I borrow it from the library! I did not got hold of the book. The cd is available only.

[Updated on: Thu, 27 October 2011 07:58]

Report message to a moderator

Re: table size [message #528853 is a reply to message #528847] Thu, 27 October 2011 08:11 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
If you have only the CD, I don't see how it can be a legal. I am not going to give any assistance to people who steal from me. Goodbye.
Re: table size [message #528865 is a reply to message #528853] Thu, 27 October 2011 08:40 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
John Watson wrote on Thu, 27 October 2011 21:11
If you have only the CD, I don't see how it can be a legal. I am not going to give any assistance to people who steal from me. Goodbye.


what are you talking about? I borrowed it from the library as the book is not available.

Whatever.... Razz
Re: table size [message #528866 is a reply to message #528853] Thu, 27 October 2011 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I concur and I support you without any restriction.

Regards
Michel
Re: table size [message #528870 is a reply to message #528866] Thu, 27 October 2011 08:47 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Thu, 27 October 2011 21:44
I concur and I support you without any restriction.

Regards
Michel


you agree with who?
Re: table size [message #528873 is a reply to message #528870] Thu, 27 October 2011 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John, this was an answer to John's post.

Regards
Michel
Re: table size [message #528877 is a reply to message #528873] Thu, 27 October 2011 08:56 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
LOL...ok.. .. whatever....
Re: table size [message #528879 is a reply to message #528877] Thu, 27 October 2011 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LOL? What is funny? You steal work from others?

Regards
Michel
Re: table size [message #528883 is a reply to message #528879] Thu, 27 October 2011 09:03 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Thu, 27 October 2011 21:58
LOL? What is funny? You steal work from others?

Regards
Michel


i did not steal anything. Is a surprise that you 2 couldn't read english properly. I borrowed it FROM the LIBRARY
.

Laughing Laughing Laughing

you know what. good day to you.
Re: table size [message #528895 is a reply to message #528883] Thu, 27 October 2011 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK you borrowed it, but the CD should not be gotten WITHOUT the book, purchase or borrowing.
Has your library the book? If not, then it is a steal.
Did your library made several copies of the CD (more than it has the book)? If yes, then it is a steal.

Regards
Michel

[Edit: Add a missing "not"]

[Updated on: Thu, 27 October 2011 11:16]

Report message to a moderator

Re: table size [message #528901 is a reply to message #528895] Thu, 27 October 2011 11:05 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Thu, 27 October 2011 16:27
but the CD should be gotten WITHOUT the book, purchase or borrowing.

should not be gotten?
Re: table size [message #528906 is a reply to message #528901] Thu, 27 October 2011 11:16 Go to previous message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, sorry for my pour english... Smile
I fix it in the post and thanks for pointing it.

Regards
Michel
Previous Topic: constraint
Next Topic: dblink in the same server
Goto Forum:
  


Current Time: Fri Apr 18 09:32:15 CDT 2014

Total time taken to generate the page: 0.10033 seconds