Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: quick question on decompressing a table...

Re: quick question on decompressing a table...

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Thu, 19 Feb 2004 00:55:39 +0200
Message-ID: <085201c3f672$54c028d0$73f823d5@porgand>


If you want to decompress the existing blocks as well, then you can just use "alter table move nocompress", with nologging and parallel if you want. (but test it out on a test table first, I had some problems with it in 9.2.0.1).

Tanel.

> Someone might disagree with me, but it strikes me that this is *much* more
> work than it's worth. Simply alter the table to not use compression. New
> data will not be compressed and While the data thats in it right now might
> be in compressed format, any change to that data will result in it's
> permenant uncompression. Since there isn't much data in it at this point,
I
> would not think that this would be that big of a deal....
>
> RF
>
> -----Original Message-----
> From: Chris Stephens
> To: oracle-l_at_freelists.org
> Sent: 2/18/2004 4:33 PM
> Subject: quick question on decompressing a table...
>
> We have a fact table that was mistakenly created with the compression
> option.
> Fortunately there is not (too) much data in the table as of yet.
>
> The table is partitioned and have bitmap indexes on all the foreign
> keys.
>
> Downtime isn't a huge deal...but minimizing it would be a positive
> thing.
>
>
>
> The easiest way I can think of to correct this is the following:
>
>
>
> Create table temp as select * from comp_table nologging;
>
>
>
> Drop table comp_table;
>
>
>
> Recreate table comp_table
>
>
>
> Insert /*+ append */ into comp_table select * from temp_table;
>
>
>
> Rebuild bitmaps
>
>
>
> Regrant privileges.
>
>
>
>
>
> ...any faster/easier way to do this? ...any gotchas working with
> compressed tables (not sure how it would relate to this situation)?
>
> ...I'm no expert on table compression and as it stands now, r'ing tfm
> at
> this point isn't won't be worth any time savings. I'm mostly just (a
> little) curious.
>
>
>
> Thanks for any suggestions
>
> Chris
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Feb 18 2004 - 16:55:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US