Move method (cons_use_pk or cons_use_rowid): cons_use_rowid begin * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "SYS.DBMS_REDEFINITION", line 75 ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459 ORA-06512: at line 2
The reason:
One of the reasons why I wanted to redefine the table, was to make it partitioned.
One of the column in the interim table was therefore defined as DATE instead of VARCHAR2:
Original table:
CREATE TABLE SCOTT.MYTABLE ( SEQ_NUM NUMBER Generated as Identity ( START WITH 9984 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) CONSTRAINT SYS_C0073650 NOT NULL, ENTRY_ID VARCHAR2(50 BYTE) NULL, ENAME VARCHAR2(100 BYTE) NULL, CREATED_DT TIMESTAMP(6) NULL, REVISION# INTEGER NULL, ACTIVE_YEAR INTEGER NULL, PERIOD VARCHAR2(10 BYTE) NULL, CONDITION VARCHAR2(50 BYTE) NULL, FN_ID VARCHAR2(11 BYTE) NULL ) TABLESPACE USERS;
Interim table:
CREATE TABLE SCOTT.MYTABLE_INTERIM ( SEQ_NUM NUMBER, ENTRY_ID VARCHAR2(50 BYTE), ENAME VARCHAR2(100 BYTE), CREATED_DT TIMESTAMP(6), REVISION# INTEGER, ACTIVE_YEAR INTEGER, PERIOD DATE, CONDITION VARCHAR2(50 BYTE), FN_ID VARCHAR2(11 BYTE) ) PARTITION BY RANGE (PERIOD) INTERVAL( NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION P_INIT VALUES LESS THAN (TO_DATE('2015-01', 'YYYY-MM')) ) TABLESPACE USERS ;
The requirement from the Developers was that the partitions should be range partitioned on dates in the format 'YYYY-MM'.
When starting the online redefintion process, I hit the error at the top in this post.
I started to search for the root cause, and executed the following:
select seq_num,TO_DATE(PERIOD,'YYYY-MM') from Scott.mytable fetch first 5 rows only;which resulted in
ORA-01858: a non-numeric character was found where a numeric was expected
It turned out that the following SQL would return the data I wanted, in the correct format, defined with the correct data type:
select seq_num,to_date(to_char(TO_DATE(PERIOD),'YYYY-MM'),'YYYY-MM') from Scott.mytable fetch first 5 rows only;
This Expression had to go into the redefinition-command, like this:
begin DBMS_REDEFINITION.start_redef_table(uname=>'EVENT', orig_table=>'MYTABLE', int_table=>'MYTABLE_INTERIM', col_mapping =>'SEQ_NUM SEQ_NUM,ENTRY_ID ENTRY_ID,ENAME ENAME,CREATED_DT CREATED_DT,REVISION# REVISION#,ACTIVE_YEAR ACTIVE_YEAR,to_date(to_char(TO_DATE(PERIOD),''YYYY-MM''),''YYYY-MM'') PERIOD,CONDITION CONDITION,FN_ID FN_ID', options_flag=>dbms_redefinition.cons_use_pk); end; /
After this change, the redefinition executed successfully.
No comments:
Post a Comment