在ORACLE 12C中有了Invisible Columns的概念,就是在表中真实的存在该列,但是通过设置Invisible导致该列不可显示.官方说明:

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) 
  will not show invisible columns.

数据库版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建含INVISIBLE列表

SQL> CREATE TABLE t_xifenfei (a number, b number INVISIBLE, c number);

Table created.

SQL> desc t_xifenfei
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------------
 A                                                              NUMBER
 C                                                              NUMBER

SQL>  select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          YES
C          NO           2

通过观察可以发现INVISIBLE列在一般的查询中不显示,在USER_TAB_COLS的视图中显示:HIDDEN_COLUMN为YES而且COLUMN_ID为空

设置INVISIBLE列为VISIBLE

SQL> ALTER TABLE t_xifenfei MODIFY (b VISIBLE);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 C                                                  NUMBER
 B                                                  NUMBER

SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          NO           3
C          NO           2

当设置为VISIBLE时HIDDEN_COLUMN为YES而且COLUMN_ID为递增值

修改列展示顺序(Column Ordering)

SQL> ALTER TABLE t_xifenfei MODIFY (a invisible);

Table altered.

SQL> ALTER TABLE t_xifenfei MODIFY (a visible);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                                                  NUMBER
 B                                                  NUMBER
 A                                                  NUMBER

SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           3
B          NO           2
C          NO           1

通过INVISIBLE和VISIBLE相关操作,实现A列从头移尾,实现列的Column Ordering效果.
补充说明:在以前的blog中,提供了修改col$基表的方法(通过修改col$.col#改变列展示顺序)来实现列的顺序修改,相对于这种方法来说,修改数据字典的方法风险太大,需要非常谨慎,而且不被ORACLE SUPPORT