关闭

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

标签: tablesqlnullincludeinsertdelete
1808人阅读 评论(0) 收藏 举报
分类:

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:111827次
    • 积分:1750
    • 等级:
    • 排名:千里之外
    • 原创:45篇
    • 转载:29篇
    • 译文:0篇
    • 评论:6条
    文章分类
    最新评论