[20200904]12c invisible column impdp segment_column_id.txt

Time:2020-11-2

[20200904]12c invisible column impdp segment_column_id.txt

–//12C provides a new feature to set hidden columns. This may cause some problems with select * from.. and even change the display order
–//Therefore, select *. Is prohibited in general code in the program. If hidden columns are set, what happens to export and import?

1. Environment:
[email protected]> @ 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. Establish test environment
[email protected]> create table empx as select * from emp ;
Table created.

[email protected]> 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.

[email protected]>  alter table empx modify hiredate invisible;
Table altered.

[email protected]> 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.

–//Note: International_ COLUMN_ ID can be understood as the definition order of table creation_ column_ ID is the order in which the segment is stored. The two can be different
–//   COLUMN_ ID my understanding is the display order of select *
–//Column under normal conditions_ id=internal_ column_ Or, unless column. Used is set
–//These can be referred to: http://www.laoxiong.net/dict_ col_ segcol_ intcol.html

3. Import and export test:
d:\tmp> expdp scott/[email protected] 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

[email protected]> alter table empx rename to empy;
Table altered.

d:\tmp> impdp scott/[email protected] 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

[email protected]> 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.

–//It can be found that the order of column definition and storage will change due to such import. Maybe this is not really needed, and the hirodate is placed in the first field

4. Continue:
[email protected]> alter table empx modify hiredate visible;
Table altered.

[email protected]> 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.
–//In this way, the hirodate is displayed at the end of select * and in the first position of segment storage

[email protected]> 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

[email protected]> @ 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 was used to observe
BBED> set dba 11,1060
        DBA             0x02c00424 (46138404 11,1060)
–//windows bbed block+1.

BBED> x /rtnccnnnn *kdbr[0]
rowdata[0]                                  @7621
———-
[email protected]: 0x2c (KDRHFL, KDRHFF, KDRHFH)
[email protected]: 0x00
[email protected]:    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
–//You can see that actually hiredete is in the first field

[email protected]> select rowid,empy.* from empy where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR        SAL       COMM     DEPTNO
—————— ———- ———- ——— ———- ———- ———- ———-
AAAHCFAALAAAACrAAA       7369 SMITH      CLERK           7902        800                    20

[email protected]> @ 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
————
[email protected]: 0x2c (KDRHFL, KDRHFF, KDRHFH)
[email protected]: 0x00
[email protected]:    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
–//The original empy table hriedate is no longer the top

5. Look at the definition of table

[email protected]> @ 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” ;

[email protected]> @ 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” ;

–//It can be found that after setting invisible, the output of table definition is put in the first place, resulting in “exception” during import
–//If the export follows the international_ COLUMN_ ID sequence definition, should not appear such a problem, do not know whether this is an Oracle bug