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.
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>
成功交换