Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

table size with compress and with no compress

J1604Jul 12 2010 — edited Jul 13 2010
Hi,
I have a table occupies 35.5 GB space with 120 Mill records in it.
I thought compress would save me a lot of space so I did.

Here is how I compressed:
I have created a seperate tablespace and within the new tablespace I have created multiple database files each file is having 2 GB.
then Insert all the records off the original table to the new one then,
I ran query below so see size

SQL> select ((blocks*8192)-(blocks*avg_space))/1024/1024 "MB size", empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'CLMs';

MB size EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
2059.88281 0 0 0

----------------------------------------

select ((blocks*8192)-(blocks*avg_space))/1024/1024 "MB size", empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'CLMS';

MB size EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
35504.2422 0 0 0

in above queries first one results 2 gb over all space after compress and second one is the original with NO compress size.

I have a question Is these size computed are reliable .?
because with compress oracle inserts data into blocks within database files.
If these blocks are half way filled and left empty and then go to the next block and so on.

Is above sizes take in account for the empty blocks ..?

I am not sure how to explain this
Please help,
JP
This post has been answered by Solomon Yakobson on Jul 12 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 10 2010
Added on Jul 12 2010
12 comments
3,428 views