12CR2 “alter table move”命令导致数据损坏?

在对具有以下特征的表发出“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

Your email is never published nor shared. Required fields are marked *