[20200904]12c invisible column impdp segment_column_id.txt

来源：这里教程网时间：2026-03-03 16:10:39 作者：

[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.

编辑推荐：

返回列表

[20200904]12c invisible column impdp segment_column_id.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推