[20200904]12c invisible column impdp segment_column_id.txt
[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.
--//關於這些可以參考:
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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2717316/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12C Statistics on Column GroupsOracle
- oracle ocp 19c考題，科目082考試題(17)-using an invisible columnOracle
- Oracle 12c expdp和impdp匯出匯入表Oracle
- Some ideas About ‘invisible bug‘Idea
- MySQL 8 新特性之Invisible IndexesMySqlIndex
- Oracle OCP(57)：IMPDPOracle
- Go最重要的特性是invisible - JackGo
- OGG-01163 Bad column length (32) specified for column in table
- oracle invisible index與unusable index的區別OracleIndex
- Index column size too large. The maximum column size is 767 bytes.Index
- CSS column-gapCSS
- CSS column-ruleCSS
- CSS column-widthCSS
- CSS column-spanCSS
- 5.6.7. Renaming a Column
- Sorting arrays in NumPy by column
- data too long for column
- 1709 - Index column size too large. The maximum column size is 767 bytes.Index
- expdp/impdp變慢 (Doc ID 2469587.1)
- [20200620]expdp impdp exclude引數.txt
- 【Data Pump】expdp/impdp Job基本管理
- oracle資料庫的impdp，expdpOracle資料庫
- IMPDP分割槽表注意事項
- Oracle資料庫中的不可見索引 invisible indexOracle資料庫索引Index
- PostgreSQL DBA(137) - PG 13（Allow invisible PROMPT2 in psql）SQL
- [Err] 1709 - Index column size too large. The maximum column size is 767 bytes.Index
- alter table set unused column
- ExtJs的Column佈局JS
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- expdp/impdp 詳細引數解釋
- Oracle 12.1.0.2 impdp匯入慢診斷Oracle
- MySQL建立表失敗：Index column size too large. The maximum column size is 767 bytesMySqlIndex
- 錯誤：duplicate column name: picstitle
- PostgreSQL DBA(164) - pgAdmin(Drop column)SQL
- Flutter 之 Row、Column詳解Flutter
- flutter佈局-1-columnFlutter
- SAP UI5 Form 表單 Column Layout 下的 Column 個數分配問題UIORM
- MySQL 5.7 到 5.6，出現 Index column size too large. The maximum column size is 767 bytesMySqlIndex