At the point I started writing this post it seemed the problem I’d recently encountered was not directly documented. It is documented, but I didn’t find the relevant MOS notes until I had already found what the problem was. Hopefully this post will save others some time.
The problem was reported to me as an ORA-14097 on partition exchange when the tables have the same column definitions.
For those that don’t have this particular Oracle error number committed to memory, the following is the oerr output:
14097, 00000, "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" // *Cause: The corresponding columns in the tables specified in the // ALTER TABLE EXCHANGE PARTITION are of different type or size // *Action: Ensure that the two tables have the same number of columns // with the same type and size.
The obvious things to check are column order, column data type, column size and column constraints. This had already been done by the person reporting the issue, so it was time to take a deeper look. I was assured that the same partition exchange had worked in other copies of the same databases and was failing for the first time in this particular copy.
Here’s a quick example of the problem situation (recreated with simplified tables)…
Describing both the partitioned (P) and non-partitioned table (T) in the exchange
SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER C2 NOT NULL VARCHAR2(1) SQL> desc p Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER C2 NOT NULL VARCHAR2(1) SQL>
No visible difference in the describe output, but the exchange fails…
SQL> alter table p exchange partition p_y with table t; alter table p exchange partition p_y with table t * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION SQL>
Querying SYS.COL$ showed there were two COL$ columns that had different values for columns between the tables being exchanged: Two columns in the tables had different values for DEFLENGTH (length of column default value definition) and one had a different value for PROPERTY. I spent a bit of time working out why DEFLENGTH was different[1], and after satisfying myself that it wasn’t responsible for the ORA-14097 I turned my attention to PROPERTY. One table had a value of 0 for all columns and the other had a value of 0 for all but one, which was 1073741824.
Using the demo tables to demonstrate difference in SYS.COL$.PROPERTY
SQL> select name, property 2 from col$ 3 where obj# = (select object_id 4 from dba_objects 5 where owner = 'DEMO' 6 and object_type = 'TABLE' 7 and object_name = 'T') 8 minus 9 select name, property 10 from col$ 11 where obj# = (select object_id 12 from dba_objects 13 where owner = 'DEMO' 14 and object_type = 'TABLE' 15 and object_name = 'P') SQL> / NAME PROPERTY ------------------------------ ---------- C2 1073741824 SQL>
It turns out that if I’d searched for “1073741824 ORA-14097 COL$.PROPERTY” then I would have found this which clearly identifies the problem. However, it was MOS ID 1112544.1 “Streams Capture Failing With ORA-26744 And ORA-26766” that gave me what I needed regarding how a column comes to have a property value of 1073741824. As the note states:
There is a column added to the table with a non-null default value.
What I hadn’t realised at this point is that COL$.PROPERTY was not always set to 1073741824 when a non-null default value column is added to a table and that this is a result of the 11g ADD COLUMN optimisation. The person that pointed this out also explained a more interesting bug that was introduced with the feature and hopefully he’ll blog about that soon.
Anyway, I’ve since found the following MOS notes which cover the situation, but if you don’t know the history of the table and you don’t know that a column with COL$.PROPERTY of 1073741824 means it was added after initial creation (with a default value and not null constraint) then you don’t know that you’re hitting “ORA-14097 At Exchange Parttion After Adding Column With Default Value”.
Relevant MOS Notes
- Common Causes of ORA-14097 At Exchange Partition Operation [ID 1418545.1]
- ORA-14097 At Exchange Parttion After Adding Column With Default Value [ID 1334763.1]
These notes both cover a workaround using event 14529 if CTAS is being used to create the new table.
__________
1 – The value of DEFAULT_LENGTH for a column depends on whitespace as shown below:
SQL> create table a (c1 number default 1, 2 c2 number default 1 not null, 3 c3 number default 1 not null, -- tab 4 c4 number default 1 not null, -- spaces 5 c5 number default 1not null 6 ) 7 / Table created. SQL> select column_name, default_length 2 from user_tab_cols 3 where table_name = 'A' 4 order by 1 5 / COLUMN_NAME DEFAULT_LENGTH ------------------------------ -------------- C1 1 C2 2 C3 2 C4 6 C5 1 SQL>
It makes no difference to partition exchange, but it caught my attention as a difference between the tables that I wanted to understand.
ORAganism 