在对具有以下特征的表发出“alter table move”命令时，会发生数据损坏的情况：
1、该表包含用户定义的类型，该类型声明为NOT INSTANTIABLE和NOT FINAL。
2、至少有三个类继承自此用户定义的类型。
示例如下：
1.创建type
首先创建基本的type。必须是NOT INSTANTIABLE NOT FINAL类型的.
CREATE OR REPLACE TYPE ty_parent AS OBJECT( n1_parent NUMBER ) NOT INSTANTIABLE NOT FINAL ;
2.三个类继承自此用户定义的类型。
CREATE OR REPLACE TYPE ty_child_1 UNDER ty_parent( VALUE varchar2(300) ) FINAL ; CREATE OR REPLACE TYPE ty_child_2 UNDER ty_parent( VALUE TIMESTAMP ) FINAL ; CREATE OR REPLACE TYPE ty_child_3 UNDER ty_parent( VALUE number ) FINAL ;
3.创建表，声明为ty_parent类型
create table t1 (ty1 ty_parent , n1 number) ;
4.插入数据
insert into t1 values ( ty_child_1( 1, 'AAAAAAAAAAAAAAAA' ) , 1 ) ; commit;
5.DUMP定义类中的数据，这里可以看到因为插入的是字符类型，存储在ty_child_1当中
SQL> select 2 dump(TREAT(ty1 AS ty_child_1).VALUE,16), 3 dump(TREAT(ty1 AS ty_child_2).VALUE,16), 4 dump(TREAT(ty1 AS ty_child_3).VALUE,16) 5 from t1 ; DUMP(TREAT(TY1ASTY_CHILD_1).VALUE,16) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DUMP(TREAT(TY1ASTY_CHILD_2).VALUE,16) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DUMP(TREAT(TY1ASTY_CHILD_3).VALUE,16) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Typ=1 Len=16: 41,41,41,41,41,41,41,41,41,41,41,41,41,41,41,41 NULL NULL
6.使用alter table move移动表
SQL> alter table t1 move; Table altered.
7.再次DUMP定义类中的数据，这里可以看到数据移动到了ty_child_2当中。即使两列都有不同的数据类型。ty_child_1是字符型，ty_child_2是时间类型。
SQL> select 2 dump(TREAT(ty1 AS ty_child_1).VALUE,16), 3 dump(TREAT(ty1 AS ty_child_2).VALUE,16), 4 dump(TREAT(ty1 AS ty_child_3).VALUE,16) 5 from t1 ; DUMP(TREAT(TY1ASTY_CHILD_1).VALUE,16) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DUMP(TREAT(TY1ASTY_CHILD_2).VALUE,16) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DUMP(TREAT(TY1ASTY_CHILD_3).VALUE,16) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL Typ=180 Len=16: 41,41,41,41,41,41,41,41,41,41,41,41,41,41,41,41 NULL
8.在此之后，执行哈希连接时出现以下ORA-00600错误：
create table t2 (n1 number) ; insert into t2 values (1) ; commit ; SQL> select t1.* from t1,t2 where t1.n1 = t2.n1 ; select t1.* from t1,t2 where t1.n1 = t2.n1 * ERROR at line 1: ORA-00600: internal error code, arguments: [rworupo.1], [16], [11], [], [], [], [], [], [], [], [], []
9.执行ctas重建表，报ORA-12899错误
SQL> create table t3 as select * from t1; create table t3 as select * from t1 * ERROR at line 1: ORA-12899: value too large for column ??? (actual: 16, maximum: 11)
建议：
12cR2在执行alter table move操作之前，执行下列sql语句检查，以下查询出来的表是不能进行move操作的。
SQL> select c.owner, c.table_name,
2 substr(qualified_col_name,1,instr(qualified_col_name,'"',1,2)) parent_type
3 from dba_tab_cols c, dba_tables t
4 where hidden_column = 'YES' and virtual_column='NO'
5 and t.owner = c.owner and t.table_name = c.table_name
6 and tablespace_name is not null
7 and qualified_col_name like 'TREAT(%'
8 group by c.owner,c.table_name,
9 substr(qualified_col_name,1,instr(qualified_col_name,'"',1,2))
10 having count(*) > 2 ;
OWNER TABLE_NAME PARENT_TYPE
------------------------------ ------------------------------ ------------------------------
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P TREAT("USER_DATA"
SYS KUPC$DATAPUMP_QUETAB_1 TREAT("USER_DATA"
SYS T1 TREAT("TY1"
SYS KUPC$DATAPUMP_QUETAB TREAT("USER_DATA"
经过测试数据使用expdp和exp是可以把数据导出来的
[oracle@ol6 ~]$ expdp system/oracle dumpfile=a.dmp DIRECTORY=ORACLE_HOME tables=t1; Export: Release 12.2.0.1.0 - Production on Wed Sep 26 17:27:11 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=a.dmp DIRECTORY=ORACLE_HOME tables=t1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SYSTEM"."T1" 9.445 KB 1 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/product/12.2.0.1/db_1/a.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Sep 26 17:27:41 2018 elapsed 0 00:00:30 [oracle@ol6 ~]$ exp system/oracle tables=t1; Export: Release 12.2.0.1.0 - Production on Wed Sep 26 17:24:30 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. EXP-00056: ORACLE error 28002 encountered ORA-28002: the password will expire within 7 days Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table T1 1 rows exported Export terminated successfully without warnings.
目前在MOS上查不到该问题相关的Bug。建议做move前先执行SQL检查。
参考文档：https://nenadnoveljic.com/blog/corruption-alter-table-move-oracle-12-2/
Post a Comment