Re: Exchange Partition Error

From: mike boligan <mboligan_at_yahoo.com>
Date: Wed, 2 May 2012 07:19:15 -0700 (PDT)
Message-ID: <1335968355.59033.YahooMailClassic_at_web113309.mail.gq1.yahoo.com>



Rodd,
   Sorry a little late on this,  found this website: http://www.oramoss.com/blog/labels/partitioning.html

Check the third blog entry, there is a checker_script which was used to find the problem you are trying to solve.

Mike

  • On Mon, 4/30/12, Rodd Holman <rodd.holman_at_gmail.com> wrote:

From: Rodd Holman <rodd.holman_at_gmail.com> Subject: Exchange Partition Error
To: oracle-l_at_freelists.org
Date: Monday, April 30, 2012, 2:41 PM

Hello listers,
I'm at a bit of a quandry here.  We have a DW load process that uses exchange partition to load the fact table. First we gather all the event data and transform it so that it is staged into the exact format as the fact table. Then we use a multi-stage process the merge the data and exchange it back into the fact.
There are three tables fact, exchange, and stage.  All are partitioned daily and of identical column/index structure.

First we load up the stage table with the extracted and transformed data. Next we truncate the exchange table.
Then we partition exchange the day from the fact table into the exchange table.
We merge the data from staging to exchange. Then we exchange back to the fact table.

Again, all three of these tables are identical in column structure, names, positions, etc.

This was all working for the past 9 months until 2 weeks ago.  We added a new column to the fact table and corresponding tables to handle a new metric.

Since then we are getting ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION. If any of you have run into this/resolve this, I would be very grateful for your insights.

To-date we've removed all PK/UK/NN constraints. I've manually rebuild the exchange table using CTAS. We've been through  Exchange Partition Signals ORA-14097 After Alter Table Add Column [ID 1201195.1]  on OSS and this hasn't fixed the issue either.

Dropping the column is not an option.  It's a metric the business demanded we add to the reporting.

We are almost at the point of renaming all three tables and rebuilding them from scratch.  Moving the data back in will take some time, the fact is over a billion rows of data. each daily partition is about 5 - 6 million rows of data.  So, I would like to avoid this option if possible.

Thanks in advance for your advice.

Rodd Holman

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 02 2012 - 09:19:15 CDT

Original text of this message