在线重定义更改主键字段类型/ORA-23539

SQL> create table T1 (x varchar2(20) primary key);
----主键类型是char型

Table created.


SQL> insert into t1 select object_id from all_objects;


71032 rows created.


SQL> commit;


Commit complete.


SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NOT NULL VARCHAR2(20)

-----要转换为number型
SQL> create table T2 (x number(20) primary key);


Table created.


SQL> desc t2;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NOT NULL NUMBER(20)

----使用pk来检查，通过的
SQL> exec dbms_redefinition.can_redef_table('clm','t1',DBMS_REDEFINITION.CONS_USE_PK);


PL/SQL procedure successfully completed.

但是进行star重定义的时候报错
SQL> exec dbms_redefinition.start_redef_table('clm','T1','T2','to_number(x) x', dbms_redefinition.cons_use_pk);
BEGIN dbms_redefinition.start_redef_table('clm','T1','T2','to_number(x) x', dbms_redefinition.cons_use_pk); END;


*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12016: materialized view does not include all primary key columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1



----然后想直接使用rowid来重定义报错
SQL> exec dbms_redefinition.start_redef_table('clm','T1','T2','to_number(x) x', dbms_redefinition.cons_use_rowid);
BEGIN dbms_redefinition.start_redef_table('clm','T1','T2','to_number(x) x', dbms_redefinition.cons_use_rowid); END;


*
ERROR at line 1:
ORA-23539: table "CLM"."T1" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1



然后在使用rowi检查时候可以重定义----结果也报错
SQL> exec dbms_redefinition.can_redef_table('clm','t1',DBMS_REDEFINITION.CONS_USE_rowid);
BEGIN dbms_redefinition.can_redef_table('clm','t1',DBMS_REDEFINITION.CONS_USE_rowid); END;


*
ERROR at line 1:
ORA-23539: table "CLM"."T1" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1




SQL> show user;
USER is "CLM"
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NOT NULL VARCHAR2(20)

SQL> execute dbms_redefinition.abort_redef_table('clm','t1','t2');
----需要停掉改过程重新来才行

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.can_redef_table('clm','t1',DBMS_REDEFINITION.CONS_USE_rowid);


PL/SQL procedure successfully completed.


SQL> exec dbms_redefinition.start_redef_table('clm','T1','T2','to_number(x) x', dbms_redefinition.cons_use_rowid);


PL/SQL procedure successfully completed.


SQL> select count(*) from t1;


COUNT(*)
----------
71032


SQL> select count(*) from t2;


COUNT(*)
----------
71032


SQL> delete from t1 where rownum<20;


19 rows deleted.


SQL> commit;


Commit complete.


SQL> select count(*) from t1;


COUNT(*)
----------
71013


SQL> select count(*) from t2;


COUNT(*)
----------
71032


SQL> exec dbms_redefinition.sync_interim_table('clm', 'T1', 'T2');


PL/SQL procedure successfully completed.


SQL> select count(*) from t2;


COUNT(*)
----------
71013


SQL> exec dbms_redefinition.finish_redef_table('clm','T1','T2');


PL/SQL procedure successfully completed.


SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NOT NULL NUMBER(20)


SQL> desc t2;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NOT NULL VARCHAR2(20)

SQL>
成功交换