Skip to content

ORA-42016: shape of interim table does not match specified column mapping

ORA-42016: shape of interim table does not match specified column mapping published on 1 комментарий к записи ORA-42016: shape of interim table does not match specified column mapping

try to make table partitioned:
create dummy table:

  CREATE TABLE USER."PERS_DATA_OPERATION_LOG_REDEF" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"OBJECT_OID" NUMBER NOT NULL ENABLE, 
	"OBJECT_SNILS" VARCHAR2(256), 
	"OBJECT_FULL_NAME" VARCHAR2(4000), 
	"SUBJECT_OID" NUMBER, 
	"SUBJECT_SNILS" VARCHAR2(256), 
	"SUBJECT_FULL_NAME" VARCHAR2(4000), 
	"CRT_ON" TIMESTAMP (6) , 
	"OPERATION_TYPE" VARCHAR2(256), 
	 CHECK (OPERATION_TYPE IN ('VIEW','PASS_RESET','REMOVE','BGIR_RESTART')) ENABLE, 
	 CONSTRAINT "PERS_DATA_OPERATION_LOG_PK1" PRIMARY KEY ("ID")
   ) 
     PARTITION BY RANGE ("CRT_ON")    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
     (partition p0 VALUES LESS THAN (TIMESTAMP' 2011-01-01 00:00:00') ) ;

start redef and get error:

exec dbms_redefinition.can_redef_table ('USER','PERS_DATA_OPERATION_LOG',dbms_redefinition.cons_use_pk);
   
exec  DBMS_REDEFINITION.start_redef_table ('USER','PERS_DATA_OPERATION_LOG','PERS_DATA_OPERATION_LOG_REDEF',NULL,DBMS_REDEFINITION.cons_use_pk);

ORA-42016: shape of interim table does not match specified column mapping

this is because original table have a timestamp with local timezone format
how to fix it:

select listagg(column_name,',') within  group  ( order by column_id) from dba_tab_columns where table_name='PERS_DATA_OPERATION_LOG';

begin 
DBMS_REDEFINITION.start_redef_table(
    uname        => 'USER',
    orig_table   => 'PERS_DATA_OPERATION_LOG',
    int_table    => 'PERS_DATA_OPERATION_LOG_REDEF',
    col_mapping =>'ID,OBJECT_OID,OBJECT_SNILS,OBJECT_FULL_NAME,SUBJECT_OID,SUBJECT_SNILS,SUBJECT_FULL_NAME,to_timestamp(crt_on) crt_on,OPERATION_TYPE',
    options_flag => DBMS_REDEFINITION.cons_use_pk);
end;



SET SERVEROUTPUT ON
DECLARE
 l_num_errors PLS_INTEGER;
BEGIN
 DBMS_REDEFINITION.copy_table_dependents(
   uname             => 'USER',
   orig_table        => 'PERS_DATA_OPERATION_LOG',
   int_table         => 'PERS_DATA_OPERATION_LOG_REDEF',
   copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
   copy_triggers     => TRUE,  -- Default
   copy_constraints  => TRUE,  -- Default
   copy_privileges   => TRUE,  -- Default
   ignore_errors     => true,
   num_errors        => l_num_errors);
 DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('USER','PERS_DATA_OPERATION_LOG','PERS_DATA_OPERATION_LOG_REDEF');

EXEC DBMS_REDEFINITION.finish_redef_table('USER', 'PERS_DATA_OPERATION_LOG', 'PERS_DATA_OPERATION_LOG_REDEF' );

1 комментарий

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Primary Sidebar