Re: Q about compressed table

Thanks Tanel,

I just changed it to accommodate for partitioning and it yielded about 1.19TB size. Considering most data is textual and a good compression candidate (leading rows have repeating values etc), this is logical.

Thanks once again
Rjamya

On 1/19/07, Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
>
> This query should give you a low-confidence estimate, based on
> dba_tables.avg_row_len and num_rows, so your stats must be up to date.
>
> I wrote it just now and it seems that I have missed something as it
> predicts the real space usage 3-5% lower when uncompressing the table with
> alter table move...
>
> I tested it on freelist managed tablespace, ASSM adds additional overhead.
>
> select
> t.num_rows -- number of rows in table
> * ( t.avg_row_len -- average row length
> + 2 -- there's a 2-byte entry in block row directory for each row
> + 3 -- dbms_stats seems to not account for row header bytes
> )
> * ( 100 -- calculate how much there's block overhead
> / ( 100
> - t.pct_free
> - (
> ( select sum( type_size * decode("TYPE", 'KTBIT', t.ini_trans, 1) )
> from v$type_size
> where "TYPE" in (
> 'KCBH',
> 'KTBBH',
> 'KDBH',
> 'KTBIT')
> )
> + t.avg_row_len * 0.5
> )
> / ts.block_size
> )
> ) estimated_size
> from
> dba_tables t,
> dba_tablespaces ts
> where
> t.tablespace_name = ts.tablespace_name
> and t.owner = '&1'
> and t.table_name = '&2'
> /
> Tanel.
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *rjamya
> *Sent:* Friday, January 19, 2007 22:53
> *To:* Oracle Discussion List
> *Subject:* Q about compressed table
>
> I have a 400GB compressed partitioned table in a 10gR2 db. Is there a way
> to estimate uncompressed size without exporting/dpexporting or copying the
> table into another schema? The estimate feature of expdp doesn't work all
> that well with compressed segments.
>
> TIA
> rjamya
> ----------------------------------------------
> Got RAC?
>
>

-- 
----------------------------------------------
Got RAC?

--
http://www.freelists.org/webpage/oracle-l