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' );
