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: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 18 Feb 2004 18:07:52 -0500
Message-ID: <D91D9D5A73FC694BBC52F1EB26AD410F02411EC2@MSGBOSCLD2WIN.DMN1.FMR.COM>


One of the problems in compressed tables is that you can't add columns to the table.
There are other problems dealing with compressed tables for example parallel direct loads does not work.

If there is no much data in the table I would simply recreate it. You need only two steps:
-Creating or loading a new table (you can: create table table-name nologging
parallel 10 <partition clause> as select ). or create a new partitioned table and use Parallel dml.
-Renaming the tables.

Regards,
Waleed

-----Original Message-----

From: Chris Stephens [mailto:ChrisStephens_at_affina.com] Sent: Wednesday, February 18, 2004 5:34 PM To: oracle-l_at_freelists.org
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
Received on Wed Feb 18 2004 - 17:07:52 CST

Original text of this message

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