Tags

,

This is a case we faced just a couple of days ago and somehow I cannot believe what I see.

We have a table containing a invisible column which was added to the table and therefor was placed at the end of the segment.

Testcase

drop table myemp;

create table myemp (empno    number(4)     not null
                   ,ename    varchar2(10)
                   ,job      varchar2(10)
                   ,mgr      number(4)
                   ,hiredate date
                   ,sal      number(6)
                   ,comm     number(4)
                   ,deptno   number(2));

alter table myemp add constraint myemp_pk primary key (empno);

Insert into MYEMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7369,'SMITH','CLERK',7902,date '1980-12-17',800,null,20);
...
commit;

alter table myemp add birthday date invisible;

update myemp set birthday = add_months(hiredate,-18);

commit;

col column_name format a15

select column_name
      ,hidden_column
      ,column_id
      ,segment_column_id
      ,internal_column_id
  from user_tab_cols
 where table_name = 'MYEMP';

COLUMN_NAME HID COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----------- --- --------- ----------------- ------------------
EMPNO       NO          1                 1                  1
ENAME       NO          2                 2                  2
JOB         NO          3                 3                  3
MGR         NO          4                 4                  4
HIREDATE    NO          5                 5                  5
SAL         NO          6                 6                  6
COMM        NO          7                 7                  7
DEPTNO      NO          8                 8                  8
BIRTHDAY    YES                           9                  9

9 rows selected.


expdp usr/pwd tables=MYEMP directory=EXP dumpfile=myemp.dmp logfile=myemp.log reuse_dumpfiles=yes

drop table myemp;

impdp usr/pwd tables=MYEMP directory=EXP dumpfile=myemp.dmp logfile=myemp.log

select column_name
      ,hidden_column
      ,column_id 
      ,segment_column_id 
      ,internal_column_id
  from user_tab_cols
 where table_name = 'MYEMP';

COLUMN_NAME HID COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----------- --- --------- ----------------- ------------------
BIRTHDAY    YES                           1                  1
EMPNO       NO          1                 2                  2
ENAME       NO          2                 3                  3
JOB         NO          3                 4                  4
MGR         NO          4                 5                  5
HIREDATE    NO          5                 6                  6
SAL         NO          6                 7                  7
COMM        NO          7                 8                  8
DEPTNO      NO          8                 9                  9

9 rows selected.

Notes to this behaviour:

  • Oracle 19 does it correct, segment_column_id, internal_column_id remains unchanged
  • It is a problem of impdp
    • I tried to import an Oracle 12 export using the import utility of Oracle 19 and it turned out ok
    • I tried to import an Oracle 19 export using the import utility of Oracle 12 and it turned out wrong
  • The data is correct
  • I have not tried with Oracle 18c

Is it a bug?

Yes it is in my opinion – I just have to convince Oracle Support of this. They were able to reproduce but offered a workaround … which is not really an option for us. We will move to Oracle 19c in the next couple of months and we have this problem in our development environment only but still I was really surprised facing something like this…

Update (May 27, 2020)

The problematic piece of code is not datapump but DBMS_METADATA which returns the columns in the wrong order.

Oracle has opened a new SR for this – I am keeping an eye on it.