Using dbms_redefinition

User_DUYMH

Hi

Version - Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 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

BEGIN

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

END;

/

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.

Sergei Krasnoslobodtsev

Hi.

There can be several types of online solutions.

To recommend a solution , it is also necessary to take into account the specifics of the data, the load mode on your database, etc.

Maybe helpful : SecureFile Migration

Doc ID 946427.1