Concurrent EXCHANGE PARTITION / IND PARTITON REBUILD

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Mon, 1 Jul 2013 23:30:23 +0800
Message-ID: <CAMNBsZuvWjY6aaDAdiOeHbYg6tb10mX56QDLp9c1M9cFptUNRw_at_mail.gmail.com>

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.

Simple enough when dealing with one Partition only in a session.

However, since we have multiple streams (Business Units) running together
(concurrently), we need the ability to do this for two or more Partitions
concurrently. Thus, there will be two or more EXCHANGE PARTITIONs (for different Partitions) running concurrently. The Exchange is pretty fast since I first exchange with an empty (interim table). I can use INCLUDING INDEXES WITHOUT VALIDATION as well. The WITHOUT VALIDATION prevents having to validate the rows as the (now populated) interim table becomes a Partition of the Archival Table.

The issue is this : What sort of locks does Oracle take that may prevent two or more concurrent
(a) EXCHANGE PARTITION
(b) REBUILD INDEX PARTITION

operations running against the same Table / Index from two or more sessions ?

My simple tests (only two concurrent sessions, even tested with WITH VALIDATION moving the interim table to the Archival Table) show no errors but Oracle Support suggests that while this may work, I might occasionally hit ORA-14766. I understand their point about cursor invalidation -- and that it happens at the Table level, not the Partition level. Their suggestion is valid : Loop and retry if I get an ORA-14766.  Unfortunately, the environment I am doing this is non-PLSQL --- only dynamically generated SQLs.

Has anyone run multiple EXCHANGE PARTITION and/or REBUILD INDEX PARTITION operations concurrently ?

-- 

Hemant K Chitale
http://hemantoracledba.blogspot.com


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

Received on Mon Jul 01 2013 - 17:30:23 CEST