Forum Stats

  • 3,767,858 Users
  • 2,252,726 Discussions


Using dbms_redefinition

User_DUYMH Member Posts: 31 Blue Ribbon
edited Oct 6, 2021 1:58PM in SQL & PL/SQL


Version - Oracle Database 19c Enterprise Edition Release - Production

I am trying to migrate a partitioned table with varray columns stored as LOB from basicfile to securefile storage. Trying to do this via dbms_redefinition however getting


  DBMS_REDEFINITION.START_REDEF_TABLE('<>', '<>' <>', NULL,null,dbms_redefinition.cons_use_pk);



ORA-42012: error occurred while completing the redefinition

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

ORA-06512: at "SYS.DBMS_REDEFINITION", line 185

ORA-06512: at "SYS.DBMS_REDEFINITION", line 4399

ORA-06512: at "SYS.DBMS_REDEFINITION", line 5721

ORA-06512: at line 140

ORA-06512: at line 140

The table has 850M records.

Checking the columns in both the tables -

There are 132 columns and the interim table which I have created from the DDL fetched from DBMS_METADATA.get_DDL has 131 columns. The extra column in the original table is:

0 SYS_NC00124$

I have tried following things but still the same error

  1. Alter table tabname drop unused columns, but it doesn't drop the above column
  2. Added a column in interim table and set it unused

Any suggestions would be appreciated.