My Oracle Support Banner

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

Last updated on FEBRUARY 07, 2019

Applies to:

Oracle Spatial and Graph - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

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

SQL> CREATE TABLE TEST_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 TEST
  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 TEST exchange partition feb10 with table TEST_STG;
alter table TEST exchange partition feb10 with table TEST_STG
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.