The purpose of this blog post is to warn of a possible data corruption in 12.2. In particular, it occurs when “alter table move” command is issued against a table having the following characteristics:

• The table contains a user defined type which is declared as NOT INSTANTIABLE and NOT FINAL.
• There are at least three classes which inherit from this user defined type.

Just in case that such boundary conditions seem non-realistic to you, simply think of how many software vendors out there are using all kinds of OR-mappers and tools for automated data model creation. More often than not, this approach results in way to complex schemes. For instance, you might be left with something like this:

CREATE OR REPLACE TYPE ty_parent AS OBJECT(
   n1_parent   NUMBER
)
NOT INSTANTIABLE NOT FINAL ; 

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 ;

create table t1 ( ty1 ty_parent , n1 number ) ;

As a matter of fact, that’s just an oversimplified model of something I indeed saw in one of our databases.

Anyway, Oracle database stores the object attributes in invisible columns. As you can see below, there is one invisible column for each attribute:

select segment_column_id-1 col, data_type,qualified_col_name 
  from dba_tab_cols where table_name='T1' order by segment_column_id ;

COL DATA_TYPE    QUALIFIED_COL_NAME
0   TY_PARENT    TY1
1   RAW          SYS_TYPEID("TY1")
2   NUMBER       "TY1"."N1_PARENT"
3   VARCHAR2     TREAT("TY1" AS "TY_CHILD_1")."VALUE"
4   TIMESTAMP(0) TREAT("TY1" AS "TY_CHILD_2")."VALUE"
5   NUMBER       TREAT("TY1" AS "TY_CHILD_3")."VALUE"
6   NUMBER       N1

Next, I’ll create a ty_child_1 object and insert it into the table:

insert into t1 values ( ty_child_1( 1, 'AAAAAAAAAAAAAAAA' ) , 1 ) ;
commit ;

As expected, only the ty_child_1 attribute is initialized:

select 
    dump(TREAT(ty1 AS ty_child_1).VALUE,16),
    dump(TREAT(ty1 AS ty_child_2).VALUE,16), 
    dump(TREAT(ty1 AS ty_child_3).VALUE,16)
  from t1 ;
  
DUMP(TREAT(TY1ASTY_CHILD_1).VALUE)
----------------------------------
DUMP(TREAT(TY1ASTY_CHILD_2).VALUE)
----------------------------------
DUMP(TREAT(TY1ASTY_CHILD_3).VALUE)
----------------------------------
Typ=1 Len=16: 41,41,41,41,41,41,41,41,41,41,41,41,41,41,41,41
NULL
NULL

At this point, I’ll introduce some turmoil into the database:

alter table t1 move ;

Shockingly, the bytes representing the ‘AAAAAAAAAAAAAAAA’ string simply wandered from the invisible column ty_child_1 to ty_child_2, even though both of the columns have different data types. (The data type of ty_child_2 is timestamp.)

select 
    dump(TREAT(ty1 AS ty_child_1).VALUE,16),
    dump(TREAT(ty1 AS ty_child_2).VALUE,16), 
    dump(TREAT(ty1 AS ty_child_3).VALUE,16)
  from t1 ;
  
DUMP(TREAT(TY1ASTY_CHILD_1).VALUE)
----------------------------------
DUMP(TREAT(TY1ASTY_CHILD_2).VALUE)
----------------------------------
DUMP(TREAT(TY1ASTY_CHILD_3).VALUE)
----------------------------------
NULL
Typ=180 Len=16: 41,41,41,41,41,41,41,41,41,41,41,41,41,41,41,41
NULL

By the way, symbolic block dump also confirms the difference before and after the move:

Before:

col  3: [16]  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
col  4: *NULL*
col  5: *NULL*

After:

col  3: *NULL*
col  4: [16]  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
col  5: *NULL*

From now on, all kind of weird staff can start happening in the database, like the following ORA-00600 error while performing a hash join:

create table t2 (n1 number) ;
insert into t2 values (1) ;
commit ;

select  t1.* from t1,t2 where t1.n1 = t2.n1 ;
ERROR at line 1:
ORA-00600: internal error code, arguments: [rworupo.1], [16], [11], [], [], [], [], [], [], [], [], []

Or “ORA-12899: value too large for column”, like in this case:

create table t3 as select * from t1
*
ERROR at line 1:
ORA-12899: value too large for column ??? (actual: 16, maximum: 11)

In conclusion, this corruption is really nasty. To begin with, it is extremely difficult to associate a symptom with its root cause. And then, database validate wouldn’t notice anything.

So, better check for any user defined objects and invisible columns in the database before upgrading to 12.2.

Update August 21st, 2018 – Affected Tables

The following query shows the tables that could be corrupted with the “alter table move” command:

select c.owner, c.table_name, 
  substr(qualified_col_name,1,instr(qualified_col_name,'"',1,2)) parent_type
  from dba_tab_cols c, dba_tables t 
  where hidden_column = 'YES' and virtual_column='NO'
    and t.owner = c.owner and t.table_name = c.table_name 
    and tablespace_name is not null
    and qualified_col_name like 'TREAT(%' 
  group by c.owner,c.table_name,
    substr(qualified_col_name,1,instr(qualified_col_name,'"',1,2))
  having count(*) > 2 ;
  
OWNER  TABLE_NAME           PARENT_TYPE
------ -------------------- --------------------
SYS    KUPC$DATAPUMP_QUETAB TREAT("USER_DATA"
U      T1                   TREAT("TY1"

I recommend running the query above not only before the upgrades but also including it in your routine database checks.

Update August 28th, 2018 – Move Online

Online move causes the corruption too:

alter table t1 move online ;