OraFAQ Forum: Server Administration » table size

Home » RDBMS Server » Server Administration » table size

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table size [message #527868]

Thu, 20 October 2011 09:12

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.

Report message to a moderator

Re: table size [message #527869 is a reply to message #527868]

Thu, 20 October 2011 09:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: table size [message #527876 is a reply to message #527868]

Thu, 20 October 2011 10:31

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: table size [message #527906 is a reply to message #527876]

Thu, 20 October 2011 14:28

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

ops$tkyte>

So that's how you named your user? /forum/fa/5767/0/

/forum/fa/5767/0/

Report message to a moderator

Re: table size [message #527940 is a reply to message #527906]

Thu, 20 October 2011 22:06

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.

Report message to a moderator

Re: table size [message #527946 is a reply to message #527940]

Thu, 20 October 2011 23:18

Michel Cadot
Messages: 68641
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

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?

Report message to a moderator

Re: table size [message #527960 is a reply to message #527956]

Fri, 21 October 2011 01:24

Michel Cadot
Messages: 68641
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

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?

Report message to a moderator

Re: table size [message #527998 is a reply to message #527988]

Fri, 21 October 2011 03:05

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: table size [message #528025 is a reply to message #527998]

Fri, 21 October 2011 05:15

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.

Report message to a moderator

Re: table size [message #528032 is a reply to message #528025]

Fri, 21 October 2011 05:28

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: table size [message #528034 is a reply to message #528032]

Fri, 21 October 2011 05:30

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: table size [message #528141 is a reply to message #527868]

Sat, 22 October 2011 02:24

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?

Report message to a moderator

Re: table size [message #528144 is a reply to message #528141]

Sat, 22 October 2011 02:45

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: table size [message #528148 is a reply to message #528144]

Sat, 22 October 2011 03:18

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 ..

Report message to a moderator

Re: table size [message #528155 is a reply to message #528148]

Sat, 22 October 2011 05:03

John Watson
Messages: 8929
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

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

ok...several times...the OCA) oracle press book might not be sufficient.

Report message to a moderator

Re: table size [message #528159 is a reply to message #528158]

Sat, 22 October 2011 05:38

John Watson
Messages: 8929
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?

Report message to a moderator

Re: table size [message #528807 is a reply to message #528159]

Thu, 27 October 2011 04:22

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?

Report message to a moderator

Re: table size [message #528812 is a reply to message #528807]

Thu, 27 October 2011 05:09

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Do you have the full book, or a stolen copy of it on CD?

Report message to a moderator

Re: table size [message #528847 is a reply to message #528812]

Thu, 27 October 2011 07:58

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

John Watson
Messages: 8929
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.

Report message to a moderator

Re: table size [message #528865 is a reply to message #528853]

Thu, 27 October 2011 08:40

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

Razz

Report message to a moderator

Re: table size [message #528866 is a reply to message #528853]

Thu, 27 October 2011 08:44

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I concur and I support you without any restriction.

Regards
Michel

Report message to a moderator

Re: table size [message #528870 is a reply to message #528866]

Thu, 27 October 2011 08:47

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?

Report message to a moderator

Re: table size [message #528873 is a reply to message #528870]

Thu, 27 October 2011 08:52

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

John, this was an answer to John's post.

Regards
Michel

Report message to a moderator

Re: table size [message #528877 is a reply to message #528873]

Thu, 27 October 2011 08:56

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

LOL...ok.. .. whatever....

Report message to a moderator

Re: table size [message #528879 is a reply to message #528877]

Thu, 27 October 2011 08:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

LOL? What is funny? You steal work from others?

Regards
Michel

Report message to a moderator

Re: table size [message #528883 is a reply to message #528879]

Thu, 27 October 2011 09:03

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

Laughing

you know what. good day to you.

Report message to a moderator

Re: table size [message #528895 is a reply to message #528883]

Thu, 27 October 2011 10:27

Michel Cadot
Messages: 68641
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

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: table size [message #528906 is a reply to message #528901]

Thu, 27 October 2011 11:16

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, sorry for my pour english... Smile

Smile

I fix it in the post and thanks for pointing it.

Regards
Michel

Report message to a moderator

Previous Topic:	constraint
Next Topic:	dblink in the same server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:59:42 CDT 2024