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

最新推荐文章于 2020-12-07 15:41:42 发布

原创最新推荐文章于 2020-12-07 15:41:42 发布 · 2.7k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#table #sql #null #include #insert

oracle 专栏收录该内容

53 篇文章

订阅专栏

本文详细描述了如何在数据库中将一个VARCHAR2类型的主键表转换为NUMBER类型，并涉及了表重定义过程中遇到的问题及解决方法。

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