0

(Oracle) I'm trying to do an exchange partition on an empty partitioned table to fill it with data from a non-partitioned table. I'm getting the following error and can't find the cause. I'm not 100% certain of the version, I know it < 11g. I'll have to double check and update my post.

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
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.

What I have tried

  • Verified both tables have same number of columns, order of columns, data types, constraints and indexes

  • Verified there are no hidden columns on my newly partitioned empty table

  • I have not altered the original table in any way shape or form

  • I tried making a copy of the original table and doing the exchange partition with that on the off chance something may be writing to the original table and Oracle doesn't like that.

  • I tried using Dbms.Redefine, but don't have the rights to run that and it's not an option for the DBA to grant me rights to that.

Code

Create copy of original table:

create table foo as select * from LOGGING_EVENT

Creating partitioned table

create table LOGGING_EVENT_PARTITIONED
(
  TIMESTMP number(20,0) not null,
  FORMATTED_MESSAGE varchar2(4000 BYTE),
  LOGGER_NAME varchar2(254 BYTE) not null,
  LEVEL_STRING varchar2(254 BYTE) not null,
  THREAD_NAME varchar2(254 BYTE),
  REFERENCE_FLAG number(38,0),
  ARG0 varchar2(254 BYTE),
  ARG1 varchar2(254 BYTE),
  ARG2 varchar2(254 BYTE),
  ARG3 varchar2(254 BYTE),
  CALLER_FILENAME varchar2(254 BYTE) not null,
  CALLER_CLASS varchar2(254 BYTE) not null,
  CALLER_METHOD varchar2(254 BYTE) not null,
  CALLER_LINE char(4 BYTE) not null,
  EVENT_ID number(10,0) primary key 
)
partition by range (event_id)
interval(10000)  (
  partition p1 values less than (10000)
);

Exchanging partition:

alter table LOGGING_EVENT_PARTITIONED
exchange partition p1
with table foo
without validation
update global indexes;

Error:

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
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.

Verifying original and partitioned tables are the same

New Partitioned Table New Partitioned Table

Original Table Original Table

  • Look for unused columns in the source table. – Mat Jun 26 '15 at 4:50
  • these picture are not useful. Can you show us a describe foo and a describe LOGGING_EVENT_PARTITIONED – miracle173 Jun 26 '15 at 7:28
  • why do you file the foo table with a select? Why don't you directly fill the LOGGING_EVENT_PARTITIONED with a select from LOGGING_EVENT? – miracle173 Jun 26 '15 at 7:31
  • Is the FOO table same in term of constraints to the LOGGING_EVENT_PARTITIONED table. Particularly NOT NULL Constraint – vishad Jun 26 '15 at 10:12
  • If your DBA does not like to grant execute on DBMS_REDEFINITION then ask him/her to partition your table by himself. If he/she still refuse, ask what is his/her job actually. – Wernfried Domscheit Jun 29 '15 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.