RE: quick question on decompressing a table...
- From: "Subbiah, Stalin" <SSubbiah@xxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Feb 2004 17:26:59 -0800
Chris,
You could replace insert append with alter table
rename command and skip reloading data from temp to
comp_table altogether.
here goes the steps...
create table temp nologging parallel as select * from comp_table;
drop table comp_table;
alter table temp rename to comp_table;
rebuild indexes with nologging and parallel if possible and turn on logging
for both table and index when complete.
Obvious thing, backup affected datafiles immediately after this operation as
this make previous backups unusable.
hth,
skumar
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Chris Stephens
Sent: Wednesday, February 18, 2004 2:34 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
