[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