Re: Concurrent EXCHANGE PARTITION / IND PARTITON REBUILD

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Mon, 01 Jul 2013 11:40:06 -0500
Message-ID: <51D1B0E6.6050005_at_oracle.com>

Could you compress and/or move the older partitions without moving them to a separate table? You would still gain the benefits of storage tiering and compression tiering, and you'd be able to make the tablespaces read only, without having to deal with referential integrity issues when removing data from a table...KJ

-- 
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
(650) 607-0392 (o)
(415) 710-8828 (m)

On 7/1/13 10:30 AM, Hemant K Chitale wrote:


> Databases running 11.2.0.2 and 11.2.0.3

> We have an application where we need to archive data.  Since data is in

> partitioned tables, Range Partitioned by a Composite Key of BUSINESS_UNIT +

> FISCAL_YEAR + QUARTER it is possible to move older data to an Archive table

> using EXCHANGE PARTITION.

>

> I do this :

> 1.  Exchange an old Partition to an Intermediate Table and then

> 2.  Exchange that Intermediate Table with a Partition of an Archival Table.

> 3.  Then use ALTER TABLE ... MOVE PARTITION .. COMPRESS to rebuild the

> Partition in the Archival Table as a Compressed Partition.  This also

> allows me to move the Partition to an Archival Tablespace

> 4.  Finally REBUILD the corresponding Index Partition for the Archival

> Table.  This also allows me to move the Index Partition to an Archival

> Index Tabespace.

>

> Sometime in the future, the Archival Tablespaces could be made read only /

> unplugged / transported to another databases.

>

>

>


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

Received on Mon Jul 01 2013 - 18:40:06 CEST