CNDBA社区
概念
The property of whether a column is visible can be controlled by the user. Invisible columns are not seen unless specified explicitly in the SELECT list. Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) does not show invisible columns.
The notion of invisible columns enables easier online application migrations as provided by Oracle's edition-based redefinition.
一个列是否可见的属性可以由用户来控制。除非在选择列表中显式指定，否则看不到Invisible columns。表的任何一般访问(such as a SELECT * FROM table or a DESCRIBE)都不显示invisible columns.。
invisible columns的概念使在线应用程序迁移更加容易，这是由Oracle的基于编辑的重新定义提供的。
例如，以下操作不显示输出中的不可见列:
• SELECT * FROM statements in SQL
• DESCRIBE commands in SQL*Plus
• %ROWTYPE attribute declarations in PL/SQL
• Describes in Oracle Call Interface (OCI)
•
关于无形列的一些杂项事实包括以下内容。
•虚拟列可以被忽略。
•在表创建期间或之后，表可以被一个看不见的列分区。
•External, cluster and temporary表不能有不可见的列。
•用户定义的类型不能包含不可见的属性。
•你不能让系统生成隐藏的列实验
1) 创建 带有Invisible Columns的 表，创建的字段description 是不可见的，但是可以对这个字段继续操作。
[oracle@dg1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 19 13:46:36 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> alter session set container=pdbcndba;
Session altered.
SQL> DROP TABLE tab1 PURGE;
DROP TABLE tab1 PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE tab1 (
id NUMBER,
description VARCHAR2(50) INVISIBLE
);
Table created.
SQL> desc tab1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> INSERT INTO tab1 VALUES (1);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT * FROM tab1;
ID
----------
1
--不可见列仍然适用于所有操作，只要它们被显式命名。
SQL> INSERT INTO tab1 (id, description) VALUES (2, 'TWO');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT id, description
FROM tab1; 2
ID DESCRIPTION
---------- --------------------------------------------------
1
2 TWO
2） 我们可以通过设置COLINVISIBLE 选项来显示使用描述命令的不可见列。
SQL> DESC tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> SET COLINVISIBLE ON
SQL> DESC tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DESCRIPTION (INVISIBLE) VARCHAR2(50)
3）Invisible Columns and Column Ordering
看不见的列并没有分配一个列顺序，所以如果一个看不见的列被显示出来，它就会被列成表的最后一列。
SQL> DROP TABLE tab1 PURGE;
Table dropped.
SQL> CREATE TABLE tab1 (
a NUMBER,
b NUMBER,
c NUMBER INVISIBLE
);
2 3 4 5
Table created.
SQL> COLUMN column_name FORMAT A15
SQL> SELECT column_id,
column_name,
hidden_column
FROM user_tab_cols
WHERE table_name = 'TAB1'
ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- --------------- ---
1 A NO
2 B NO
C YES
SQL> ALTER TABLE tab1 MODIFY b INVISIBLE;
Table altered.
SQL> ALTER TABLE tab1 MODIFY c VISIBLE;
Table altered.
SQL>
SELECT column_id,
column_name,
hidden_column
FROM user_tab_cols
WHERE table_name = 'TAB1'
ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- --------------- ---
1 A NO
2 C NO
B YES
SQL> ALTER TABLE tab1 MODIFY b VISIBLE;
Table altered.
SQL> SELECT column_id,
column_name,
hidden_column
FROM user_tab_cols
WHERE table_name = 'TAB1'
ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- --------------- ---
1 A NO
2 C NO
3 B NO
4） Mandatory Invisible Columns
使列不可见不会影响其强制/可选状态，如下面的示例所示。
SQL> DROP TABLE tab1 PURGE;
Table dropped.
SQL> CREATE TABLE tab1 (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL,
created_date DATE INVISIBLE NOT NULL
); 2 3 4 5
Table created.
SQL> COLUMN column_name FORMAT A20
SQL> SELECT column_id,
column_name,
nullable,
hidden_column
FROM user_tab_cols
WHERE table_name = 'TAB1'
ORDER BY column_id; 2 3 4 5 6 7
COLUMN_ID COLUMN_NAME N HID
---------- -------------------- - ---
1 ID N NO
2 DESCRIPTION N NO
CREATED_DATE N YES
SQL>
INSERT INTO tab1 VALUES (1, 'ONE');SQL>
INSERT INTO tab1 VALUES (1, 'ONE')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."TAB1"."CREATED_DATE")
SQL>
ALTER TABLE tab1 MODIFY created_date NULL;SQL>
Table altered.
SQL> INSERT INTO tab1 VALUES (1, 'ONE');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT * FROM tab1;
ID DESCRIPTION
---------- --------------------------------------------------
1 ONE
参考文档
http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT002
http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN-GUID-38DBFC2E-FC6C-46E2-A7A1-6FA703F9DC01
版权声明：本文为博主原创文章，未经博主允许不得转载。
