[20200904]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.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1150.html
標籤：Oracle
上一篇：Oracle RAC與DG
下一篇：做一名合格的DBA