ORA-14097 while exchanging partitions with table having SDO_GEOMETRY column (Doc ID 1321560.1)

Last updated on JUNE 02, 2011

Applies to:

Oracle Spatial - Version: 10.2.0.4 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

ALTER TABLE EXCHANGE PARTITION fails with an ORA-14097 error.

SQL> CREATE TABLE VESSEL_STG
  2  (
  3    ID           NUMBER(8)           NOT NULL,
  4    NAME         VARCHAR2(32 CHAR)   NOT NULL,
  5    LAST_SEEN_AT TIMESTAMP(6)        NOT NULL,
  6    DESTINATION  VARCHAR2(32 CHAR)       NULL,
  7    GEOMETRY     MDSYS.SDO_GEOMETRY      NULL,
  8    COMPANY      VARCHAR2(32 CHAR)       NULL
  9  );

Table created.

SQL> CREATE TABLE VESSEL
  2  (
  3    ID           NUMBER(8)           NOT NULL,
  4    NAME         VARCHAR2(32 CHAR)   NOT NULL,
  5    LAST_SEEN_AT TIMESTAMP(6)        NOT NULL,
  6    DESTINATION  VARCHAR2(32 CHAR)       NULL,
  7    GEOMETRY     MDSYS.SDO_GEOMETRY      NULL,
  8    COMPANY      VARCHAR2(32 CHAR)       NULL
  9  )
 10  COLUMN GEOMETRY NOT SUBSTITUTABLE AT ALL LEVELS
 11  PARTITION BY RANGE (LAST_SEEN_AT)
 12  (
 13    PARTITION JAN10 VALUES LESS THAN (TIMESTAMP' 2011-02-01 00:00:00'),
 14    PARTITION FEB10 VALUES LESS THAN (TIMESTAMP' 2011-03-01 00:00:00'),
 15    PARTITION MAR10 VALUES LESS THAN (TIMESTAMP' 2011-04-01 00:00:00')
 16  );

Table created.

SQL> alter table VESSEL exchange partition feb10 with table VESSEL_STG;
alter table VESSEL exchange partition feb10 with table VESSEL_STG
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms