Re: Exchange Partition Error
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 30 Apr 2012 23:15:35 +0100
Any dropped columns still sitting there unused?
Rodd Holman[1]
30 April 2012 19:41
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
abillion 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
----------------------------------------------------------------------------
--- Links ---
1 mailto:rodd.holman@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
