Error: "ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" during online switch of table containing one or more RAW columns.
When a database has been upgraded from Oracle 8i to 9, there is one column being referenced that is not updated in a table.
Note metalink note:
Bug 2761650 ORA-14097 can occur on EXCHANGE partition when table has a RAW column
This note gives a brief overview of bug 2761650.
Product (Component): Oracle Server (Rdbms)
Range of versions believed to be affected: Versions >= 9 but < 10G
Versions confirmed as being affected: 22.214.171.124
Platforms affected: Generic (all / most platforms affected)
This issue is fixed in 126.96.36.199 (Server Patch Set) 10g Production Base Release
Migration / Upgrade / Downgrade
'ORA-14097' can occur when trying to EXCHANGE a partition containing one or more RAW columns if the table existed in the database prior to migrating from Oracle8i.
Oracle's recommendation is to recreate the table in Oracle 9i; however, this can be done by using T-lock switch method. Once the table has been reorganized in Oracle 9i using the t-lock, the issue should be resolved. Thereafter, the online switch method can be used in future reorganization.
To perform T-lock switch method against the table:
1. Check if the Quest triggers are still on using this query: select trigger_name, table_name, status from dba_triggers where trigger_name like 'QUEST%';
2. Drop the Quest triggers that are identified.
3. Perform T-Lock method reorganization for the table