INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

(OP)
Our data warehouse is 5TB compressed. What is a good algorithm to use to calculate the UNCOMPRESSED size?

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

RE: How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

Hi Dave,

If you are referring to RMAN backup, perhaps you could query RC_BACKUP_DATAFILE_DETAILS view?
Otherwise the compression utility may have an option and provide the answer.
3eyes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

Ooops, missed the "size of tablespace segments" part from the title.
noevil

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

(OP)
Thanks LK. Are you aware of any data dictionary view that gives the compression ratio on a segment basis?

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

RE: How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects


Unfortunately not, because the database stores compressed rows where duplicate values are replaced with a short reference to a symbol table stored at the beginning of the block.
Therefore the compression ratio will depend on the duplicity of data in the rows.

Nevertheless, to get a rough estimate of the uncompressed size for one table I would create an uncompressed table with a sample of the data:

CODE

CREATE TABLE My_Table_Uncompresses
AS
    SELECT *
      FROM My_Table SAMPLE BLOCK ( 10 ); 

And compare sizes using this formula:

uncompressed size = ( compressed #rows ) * uncompressed ( #blocks / #sample.rows )

HTH
pirate

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

as far as I am aware there is no real way to get that info when tables are already compressed.

One way of getting an approximation is to for each table get a sample set of records (to occupy at least 1000 blocks) copied to an uncompressed version of the table and then either using dbms_compression package to determine possible compression, or determining space used before compression, issuing alter table move compress, get new size and compare to uncompress size to determine ratio.

hard working and slowish depending on number of tables.

And do see this as it highlights a "correct" way of doing it. http://decipherinfosys.wordpress.com/2007/01/24/es...

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close