12c invisible column impdp segment_column_id.txt

--//12c提供一个新特性,可以设置隐藏列.这样会导致select * from ..的一些问题,甚至改变显示顺序.
--//所以在程序中一般代码禁止使用select *.如果设置隐藏列,导出导入会出现什么情况呢?

1.环境:
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.建立测试环境:
SCOTT@test01p> create table empx as select * from emp ;
Table created.

SCOTT@test01p> select column_name ,hidden_column ,column_id ,segment_column_id ,internal_column_id from user_tab_cols where table_name = 'EMPX';
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
8 rows selected.

SCOTT@test01p>  alter table empx modify hiredate invisible;
Table altered.

SCOTT@test01p> select column_name ,hidden_column ,column_id ,segment_column_id ,internal_column_id from user_tab_cols where table_name = 'EMPX';
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             YES                            5                  5
SAL                  NO           5                 6                  6
COMM                 NO           6                 7                  7
DEPTNO               NO           7                 8                  8
8 rows selected.

--//注:INTERNAL_COLUMN_ID可以理解为建表的定义顺序.segment_column_id为保存在段内的顺序.两者可以不同.
--//   COLUMN_ID我的理解就是select * 的显示顺序.不知道是否正确.
--//正常情况下column_id=internal_column_id,除非设置隐含列或者unused column.
--//关于这些可以参考:http://www.laoxiong.net/dict_col_segcol_intcol.html

3. 导入与导出测试:
d:\tmp> expdp scott/btbtms@test01p tables=EMPX  dumpfile=empx.dp logfile=empx.log reuse_dumpfiles=yes
Export: Release 12.2.0.1.0 - Production on Sat Sep 5 10:30:43 2020
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
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*@test01p tables=EMPX dumpfile=empx.dp logfile=empx.log reuse_dumpfiles=yes
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 "SCOTT"."EMPX"                              8.781 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\APP\ORACLE\ADMIN\TEST\DPDUMP\C287357CE3D5470AA01668B945336F73\EMPX.DP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Sep 5 10:31:48 2020 elapsed 0 00:00:53

SCOTT@test01p> alter table empx rename to empy;
Table altered.

d:\tmp> impdp scott/btbtms@test01p tables=EMPX  dumpfile=empx.dp logfile=empx.log
Import: Release 12.2.0.1.0 - Production on Sat Sep 5 10:34:16 2020
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
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/a**@test01p tables=EMPX dumpfile=empx.dp logfile=empx.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMPX"                              8.781 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Sat Sep 5 10:35:28 2020 elapsed 0 00:01:07

SCOTT@test01p> select column_name ,hidden_column ,column_id ,segment_column_id ,internal_column_id from user_tab_cols where table_name = 'EMPX';
COLUMN_NAME          HID  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
-------------------- --- ---------- ----------------- ------------------
HIREDATE             YES                            1                  1
EMPNO                NO           1                 2                  2
ENAME                NO           2                 3                  3
JOB                  NO           3                 4                  4
MGR                  NO           4                 5                  5
SAL                  NO           5                 6                  6
COMM                 NO           6                 7                  7
DEPTNO               NO           7                 8                  8
8 rows selected.

--//可以发现这样导入导致列定义顺序,存储顺序都发生变化,也许这并不是实际需要的,而且hiredate被放在第一个字段.

4.继续:
SCOTT@test01p> alter table empx modify hiredate visible;
Table altered.

SCOTT@test01p> select column_name ,hidden_column ,column_id ,segment_column_id ,internal_column_id from user_tab_cols where table_name = 'EMPX';
COLUMN_NAME          HID  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
-------------------- --- ---------- ----------------- ------------------
HIREDATE             NO           8                 1                  1
EMPNO                NO           1                 2                  2
ENAME                NO           2                 3                  3
JOB                  NO           3                 4                  4
MGR                  NO           4                 5                  5
SAL                  NO           5                 6                  6
COMM                 NO           6                 7                  7
DEPTNO               NO           7                 8                  8
8 rows selected.
--//这样hiredate在select *时显示在最后,而在段存储时在第1个位置.

SCOTT@test01p> select rowid,empx.* from empx where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR        SAL       COMM     DEPTNO HIREDATE
------------------ ---------- ---------- --------- ---------- ---------- ---------- ---------- -------------------
AAAHC7AALAAAAQjAAA       7369 SMITH      CLERK           7902        800                    20 1980-12-17 00:00:00

SCOTT@test01p> @ rowid AAAHC7AALAAAAQjAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     28859         11       1059          0  0x2C00423           11,1059              alter system dump datafile 11 block 1059

--//通过bbed观察:
BBED> set dba 11,1060
        DBA             0x02c00424 (46138404 11,1060)
--//windows bbed block+1.

BBED> x /rtnccnnnn *kdbr[0]
rowdata[0]                                  @7621
----------
flag@7621: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7622: 0x00
cols@7623:    8

col    0[7] @7624: 1980-12-17 00:00:00
col    1[3] @7632: 7369
col    2[5] @7636: SMITH
col    3[5] @7642: CLERK
col    4[3] @7648: 7902
col    5[2] @7652: 800
col    6[0] @7655: *NULL*
col    7[2] @7656: 20
--//可以发现实际上hiredate在第1个字段.

SCOTT@test01p> select rowid,empy.* from empy where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR        SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- ---------- ---------- ----------
AAAHCFAALAAAACrAAA       7369 SMITH      CLERK           7902        800                    20

SCOTT@test01p> @ rowid AAAHCFAALAAAACrAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     28805         11        171          0  0x2C000AB           11,171               alter system dump datafile 11 block 171

BBED> x /rnccntnnn dba 11,172 *kdbr[0]
rowdata[529]                                @8150
------------
flag@8150: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8151: 0x00
cols@8152:    8
col    0[3] @8153: 7369
col    1[5] @8157: SMITH
col    2[5] @8163: CLERK
col    3[3] @8169: 7902
col    4[7] @8173: 1980-12-17 00:00:00
col    5[2] @8181: 800
col    6[0] @8184: *NULL*
col    7[2] @8185: 20
--//而原始的empy表hriedate不再最前面.    

5.看看表的定义:

SCOTT@test01p> @ ddl scott.empx
C100
----------------------------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."EMPX"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
        "HIREDATE" DATE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

SCOTT@test01p> @ ddl scott.empy
C100
----------------------------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."EMPY"
   (    "HIREDATE" DATE INVISIBLE,
        "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

--//可以发现设置INVISIBLE后,表定义的输出hiredate放在第一位,导致导入时出现"异常".
--//如果导出按照INTERNAL_COLUMN_ID的顺序定义,应该就不出现这样的问题,不知道这个是否算oracle的bug.