在线重定义更改主键字段类型/ORA-23539
来源:互联网 发布:mac使用netkeeper上网 编辑:程序博客网 时间:2024/06/05 02:22
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>
成功交换
- 在线重定义更改主键字段类型/ORA-23539
- Sql语句更改字段类型及主键等实用整理
- Sql语句更改字段类型及主键等实用整理
- 【tool】基于主键的表在线重定义
- Oracle更改字段类型Blob错误ora-22858
- mysql 更改字段类型
- Postgresql更改字段类型
- 在线重定义表
- 在线重定义
- oracle 在线重定义
- 在线重定义表
- Oracle在线重定义
- ORACLE在线重定义
- Oracle 在线重定义
- 在线重定义
- oracle 在线重定义
- oracle在线重定义
- 在线重定义
- 物化视图的快速刷新(一)
- C++构造函数调用顺序
- 在RHEL6中用BASH脚本搭建DNS域名解析服务器
- 当乐网JAVA工程师笔试经历
- 物化视图的快速刷新(二)
- 在线重定义更改主键字段类型/ORA-23539
- Spring CommonsMultipartResolver 上传文件
- ssh2概要
- u-boot源码之start.S分析
- 物化视图的快速刷新(三)
- 可延迟函数、内核微线程以及工作队列
- 《深入理解linux内核》笔记二--内存寻址
- C++转换成C的为什么在VC++6.0会运行错误
- 菜鸟