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.