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>
成功交换
分享到:
相关推荐
CLOB字段类型报错 ORA-01704:文字字符串过长的解决
使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查。使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查 使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查
主要给大家介绍了关于数据库报:ORA-01196(ORA-10458/ORA-01110)错误的解决方法,文中通过示例代码介绍的非常详细,对大家学习或者使用数据库具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法ora-00604 错误 解决 方法
(1)ORA-00257 空间不足错误 这是由于归档日志太多,占用了全部的硬盘剩余空间导致的,通过简单删除日志或加大存储空间就能够解决。但在Oracle 10g上存储空间还有很大,却也报这个错误。原因是Oracle 10g中新的特性...
oracle数据库ora-01152和ora-01110的解决办法
Oracle 11gr2连Oracle 19c 报ORA-28040 ORA-01017解决方法
oracle网络配置(listener_ora-sqlnet_ora-tnsnames_ora).mht
Drop goldengate用户时,报ORA-00604 ORA-20782 ORA-06512错误
ORA-32001:write to spfile requested but no spfile is in use请求写入spfile,但没有使用spfile的解决方法 在输入以下语句中报了这样的错误: SQL>alter system set control_files=’/u01/app/oracle/oradata/prod/...
http://tomcat.apache.org/download-70.cgi http://tomcat.apache.org/download-70.cgi
ORACLE ORA-00132 ORA-00214
客户端进行连接的时候,系统不定期出现ora-12520,ora-12516的连接问题, 问题解决方案建议: 1、增加process和session的连接数。 2、检查连接的应用,是不是有没有释放的连接。 3、将修改参数local_listener中的vip为...
oracle启动失败,ORA-00702报错,windows,linux系统下解决办法
ORA-01036:非法的变量名/编号 oracle特有的错误
离线误删空间文件导致的ORA-01033及ORA-01145问题的解决办法,在解决ORA-01033的过程中,又出现ORA-01145 * 第 1 行出现错误: ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机 接着的解决步骤
ORA-12154: TNS: 无法解析指定的连接标识符的解决方法
创建物化视图ORA-12014错误解决方法 创建物化视图ORA-12014错误解决方法
ORA-12541 TNSno listener 的解决方案 ORA-12541 TNSno listener 的解决方案