I came across an interesting issue yesterday whereby a partition exchange load routine wasn’t working for my colleague Jon. The ensuing investigation seems to point at the possibility of a bug, whereby after exchanging a partition Oracle may have inadvertently transposed columns in the target table. I’ve created a test script to illustrate the problem and logged an SR with Metalink to see what they make of it.
In light of my recent post on DBMS_APPLICATION_INFO I’d better state that we found the problem on 10.2.0.2 on HP-UX and then tested it against 9.2.0.6 on HP-UX and found the same results so it seems the issue has been there a while in terms of Oracle releases.
The investigation started down a different avenue – as they often do – with the inability to complete a partition exchange due to the Oracle error:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Reissuing the exchange partition command with the EXCLUDING INDEXES clause allowed the exchange to proceed so we figured there was definitely a problem with the indexes.
Next we tried dropping all the indexes on both sides of the exchange and recreating them…but when we retried the exchange partition it failed again with the same error.
Right, drastic measures time…we dropped all the indexes on both sides and ran the exchange which, with no indexes around, worked fine (with INCLUDING INDEXES clause in place as it would normally be on this process).
Next we proceeded to add the indexes one by one, starting with the b trees for the primary key and unique keys. This worked without a problem so we moved on to the bitmap indexes which seemed to be fine for the first few but then after adding a few of them it suddenly stopped working. Through a process of trial and error we were able to determine that if two specific bitmap indexes were on the tables then the exchange would fail with ORA-14098.
So, what was so special about those two bitmaps and the columns they were on. We selected the details from dba_tab_columns for the two columns involved in these bitmap indexes and realised that the only difference was that they were at a different column position (COLUMN_ID) on the tables – which begged the question “If these two tables are not quite the same then how can we be allowed to exchange them?”
Well, I guess, in our case, we were able to say that the two objects being exchanged had the same columns but just not necessarily in the same order (those of you who love classic comedy will I’m sure be recalling Morecambe and Wise with Andre Previn right about now)…should this mean we can swap the tables or not ?
To try and illustrate the scenario I built a test script…the output from a run follows…sorry it’s a bit long…but it does prove some useful conclusions (I think)
> @test_pel_bug_mismatch_columns
> REM Drop the tables…
> DROP TABLE jeff_test
/
Table dropped.
> DROP TABLE jeff_test_ptn
/
Table dropped.
> REM Create the tables…
> CREATE TABLE jeff_test_ptn(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column3 NUMBER NOT NULL
4 ,column2 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 PARTITION BY RANGE (partition_key_col)
8 (
9 PARTITION p001 VALUES LESS THAN (1000001)
10 ,PARTITION p002 VALUES LESS THAN (MAXVALUE)
11 )
12 /
Table created.
> CREATE TABLE jeff_test(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column2 NUMBER NOT NULL
4 ,column3 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 /
Table created.
> REM Populate the table…
> INSERT /*+ APPEND */
2 INTO jeff_test_ptn(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,3)
11 , MOD(idx,4)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> INSERT /*+ APPEND */
2 INTO jeff_test(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,5)
11 , MOD(idx,6)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> REM Count the rows in COLUMN2 and COLUMN3 for both tables…
>
> REM Should see 3 rows…all NUMBERS
> SELECT column2,COUNT(1) FROM jeff_test_ptn GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 12000
2 12000
0 12000
3 rows selected.
>
> REM Should see 4 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 9000
2 9000
3 9000
0 9000
4 rows selected.
>
> REM Should see 5 rows…all NUMBERS
> SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 7200
2 7200
4 7200
3 7200
0 7200
5 rows selected.
>
> REM Should see 6 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 6000
2 6000
4 6000
5 6000
3 6000
0 6000
6 rows selected.
>
> REM Now lets try and swap the partition and the table…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
Table altered.
>
> REM Surprisingly, it lets us do the above operation without complaining.
> REM Even worse…it transposes the values in COLUMN2 and COLUMN3…
>
> REM Should see 5 rows…but we see 6 rows
> SELECT column2,COUNT(1) FROM jeff_test_ptn GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 6000
2 6000
4 6000
5 6000
3 6000
0 6000
6 rows selected.
>
> REM Should see 6 rows…but we see 5 rows
> SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 7200
2 7200
4 7200
3 7200
0 7200
5 rows selected.
>
> REM Should see 3 rows…but we see 4 rows
> SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 9000
2 9000
3 9000
0 9000
4 rows selected.
>
> REM Should see 4 rows…but we see 3 rows
> SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;
COLUMN3 COUNT(1)———- ———-
1 12000
2 12000
0 12000
3 rows selected.
>
> REM Now, lets try again but with COLUMN2 and COLUMN3
> REM being of different datatypes…
>
> REM Drop the tables…
> DROP TABLE jeff_test
2 /
Table dropped.
> DROP TABLE jeff_test_ptn
2 /
Table dropped.
> REM Create the tables…
> CREATE TABLE jeff_test_ptn(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column3 NUMBER NOT NULL
4 ,column2 DATE NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 PARTITION BY RANGE (partition_key_col)
8 (
9 PARTITION p001 VALUES LESS THAN (1000001)
10 ,PARTITION p002 VALUES LESS THAN (MAXVALUE)
11 )
12 /
Table created.
> CREATE TABLE jeff_test(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column2 DATE NOT NULL
4 ,column3 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 /
Table created.
> REM Populate the table…
> INSERT /*+ APPEND */
2 INTO jeff_test_ptn(partition_key_col
3 ,column1
4 ,column2
But it is important to try description now free sample of viagra note that Kamagra is not an aphrodisiac. They are cialis canadian prices greyandgrey.com considered as a liver and kidney tonic. Sildamax cialis 20 mg is prepared in the clinically clean and healthy conditions to provide safe solution for ED treatment. The most cautious consumers seek online stores that can offer you generic cialis professional medicines. 5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , SYSDATE + MOD(idx,3)
11 , MOD(idx,4)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> INSERT /*+ APPEND */
2 INTO jeff_test(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , SYSDATE + MOD(idx,5)
11 , MOD(idx,6)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> REM Count the rows in COLUMN2 and COLUMN3 for both tables…
>
> REM Should see 3 rows…all DATES
> SELECT column2,COUNT(1) FROM jeff_test_ptn GROUP BY column2;
COLUMN2 COUNT(1)
——————– ———-
27-SEP-2006 11:49:03 12000
28-SEP-2006 11:49:03 12000
26-SEP-2006 11:49:03 12000
3 rows selected.
>
> REM Should see 4 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 9000
2 9000
3 9000
0 9000
4 rows selected.
>
> REM Should see 5 rows…all DATES
> SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;
COLUMN2 COUNT(1)
——————– ———-
30-SEP-2006 11:49:03 7200
27-SEP-2006 11:49:03 7200
28-SEP-2006 11:49:03 7200
29-SEP-2006 11:49:03 7200
26-SEP-2006 11:49:03 7200
5 rows selected.
>
> REM Should see 6 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 6000
2 6000
4 6000
5 6000
3 6000
0 6000
6 rows selected.
>
> REM Now lets try and swap the partition and the table…
> REM It will fail with error…
> REM ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
>
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
ALTER TABLE jeff_test_ptn
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
>
> REM So, it only fails when the columns have the same datatypes.
>
> REM Now, lets say they are the same datatype and look at how bitmap indexes
> REM are affected in the PARTITION EXCHANGE process…
>
> REM First lets recreate the tables with COLUMN2 and COLUMN3
> REM having the same datatype…
> REM Drop the tables…
> DROP TABLE jeff_test
2 /
Table dropped.
> DROP TABLE jeff_test_ptn
2 /
Table dropped.
> REM Create the tables…
> CREATE TABLE jeff_test_ptn(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column3 NUMBER NOT NULL
4 ,column2 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 PARTITION BY RANGE (partition_key_col)
8 (
9 PARTITION p001 VALUES LESS THAN (1000001)
10 ,PARTITION p002 VALUES LESS THAN (MAXVALUE)
11 )
12 /
Table created.
> CREATE TABLE jeff_test(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column2 NUMBER NOT NULL
4 ,column3 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 /
Table created.
> REM Populate the table…
> INSERT /*+ APPEND */
2 INTO jeff_test_ptn(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,3)
11 , MOD(idx,4)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> INSERT /*+ APPEND */
2 INTO jeff_test(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,5)
11 , MOD(idx,6)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
>
> REM Now lets create a bitmap index on COLUMN1 on both tables…
> CREATE BITMAP INDEX jtp1 ON jeff_test_ptn(column1) LOCAL
2 /
Index created.
> CREATE BITMAP INDEX jt1 ON jeff_test(column1)
2 /
Index created.
>
> REM …and now try PARTITION EXCHANGE…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
Table altered.
> REM It works fine.
>
> REM Now lets create a bitmap index on COLUMN4 on both tables…
> CREATE BITMAP INDEX jtp4 ON jeff_test_ptn(column4) LOCAL
2 /
Index created.
> CREATE BITMAP INDEX jt4 ON jeff_test(column4)
2 /
Index created.
>
> REM …and now try PARTITION EXCHANGE…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
Table altered.
>
> REM It works fine.
>
> REM Now lets create a bitmap index on COLUMN2 on both tables…
> CREATE BITMAP INDEX jtp2 ON jeff_test_ptn(column2) LOCAL
2 /
Index created.
> CREATE BITMAP INDEX jt2 ON jeff_test(column2)
2 /
Index created.
>
> REM …and now try PARTITION EXCHANGE…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
WITH TABLE jeff_test
*
ERROR at line 3:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Right, now what can we conclude from the above ?
1. If you don’t have indexes on your table then you can do an exchange even if the columns are in a different order – but you will silently transpose the columns. No errors are given and I think this is a bug.
2. You will only hit the problem in 1 if the columns which are transposed are of the same datatype – I’ve not checked for scale/precision/length – it may be, for example, that a VARCHAR2(20) and VARCHAR2(10) being transposed would raise an ORA-14097 error.
3. If you have indexes on columns which are transposed then the exchange will fail with ORA-14098. I don’t know whether this is a bitmap index specific thing as I’ve not tested it any further.
4. If you only have indexes on columns which are not transposed then you can do the exchange and there will be no errors – but your data is obviously transposed silently.
Eeek! I can see why it happens like that when the two tables have the same ‘shape’ as long as the number of columns and the data types match it will fit!
Our exchange routines create the exchange table on the fly as a SELECT * from partitioned_table WHERE 1=2 so we always have the same definitions for source and target. In part we do it that way so we used the correct tablespaces for the exchange, but that’s another story
Jeff,
I suspect the table column order is the culprit. I believe (not found any reference yet) that while exchanging partitions, oracle merges using column order and not by the columns names.
Your tables are created with different column order for column2 and column3 hence the problem.
Pl correct me if I am wrong.
Rama
If you fix this “bug”, then you won’t be able to rename a column using EXCHANGE PARTITION. It’s not so important these days, but it once was!
yeah I’ve scripting a huge migration from monthly tables to a table with monthly partitions. I’ve found that when my devs would add a column to a table, they would also edit the script to create future monthly tables, but put the new column in the middle so columns dealing with similar groups of data are next to each other. Pointless for them, headaches for me.
Thanks all for your comments.
Pete – sounds like a very simple but effective solution to the issue…just not sure how it would fit in our OWB centric architecture.
Jeff – we wrote a PL/SQL package to create the empty exchange tables and use this from OWB – the only downside is that we must remember to create a exchange table for the duaration of deploying the OWB code that uses it!
…yeah, that’s the problem…with OWB wanting to have a “correct” shaped definition of the staging table in the repository to build mappings against.
I had the same issue as well when testing some partition maintenance code to exchange out from the partitioned table. Metalink Note # 72332.1 has a good discussion on it. Ended up using dbms_metadata to get the DDL from the table and build the new table to hold the exchanged partition. It would be nice if we could just exchange a partition out to a table segment without having to create one in the first place.
Thanks for the reference.
When you get interviewed for Oracle Magazine sometime you could always suggest that as your “What feature would you most like to see in Oracle?”
😉
>> It would be nice if we could just exchange a partition out to a table segment without having to create one in the first place.
I submitted a similar-but-opposite enhancement request to allow us to add a table as a completely new partition of a different table without the need to exchange with an existing partition.
Hi
I get this kind of error messages, when I have a global index on a primary key.
then I changed the index as local unique index with the partition key as suffix.It works fine.
Is this a still bug in 10.2.2.
I like your blog on partitions, I am reading slowly one a day…..
Thank you very much
CT
Hi CT – I’m not sure if this is a problem beyond 10.2.0.2 – I’ve not got anything later than that at my disposal currently.
Thanks for reading.
Jeff
Hi All,
I have big problem with Partition Exchange method.I have two tables one is non partitioned and other one range partiioned(mothly).I need to load the data from source to current partiion.
My Database structure :
———————-
1)Source table is non partitioned
2)Source table has all BItmap indexes(global)
3)Target table is range partiioned
4)Target table indexes are bitmap and local.
5)When i move the data from source to target all indexes on the source table and target table’s Primary Key(Indexed) and Alternative Primary Key(Unique and Indexed) changing the STATUS to ‘UNUSABLE’.
6)When i rebuild the indexes source tables indexes changing the
status to VALID.
But Target table’s Primary Key(Index) and Alternative Unique Key(Index) is not changing to VALID state.getting error “Rebuild indexes on Partiion table is not whole part”
My exchange code :
ALTER TABLE XYZ partiion PART_DEC_9999 with table PQR
EXCLUDE INDEXES
WITHOUT VALIDATION.
I need suggestions from experts..
Thanks
Sheker
sheker73@yahoo.com
Sheker?
I’d need a log of your SQL*Plus session to see exactly what you are doing and to try and help you…
Regards
Jeff