ORACLE 12C Invisible Columns and Column Ordering

联系:手机(13429648788)  QQ(107644445)

链接:https://www.orasos.com/oracle-12c-invisible-columns-and-column-ordering.html

标题:ORACLE 12C Invisible Columns and Column Ordering

作者:惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

在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

3 thoughts on “ORACLE 12C Invisible Columns and Column Ordering

  1. 非常感谢你的分享.
    冒昧的请教下, 在 Oracle 12c 的 Pluggable Database 里面, 对AWR进行创建快照操作, 会提示:
    ORA-13516: AWR Operation failed: database is pluggable.
    实际上其他修改 AWR的任何语句都会提示这个信息.

    这说明 Oracle 12c 不支持在Pluggable Database 里面进行AWR操作了吗?

    你好,我测试了12.1.0.2版本的pdb中awr使用情况,证明还是无法正常使用,因为是beta,不好深入分析

    SQL> select * from v$version;
    
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta                    0
    PL/SQL Release 12.1.0.0.2 - Beta                                                          0
    CORE    12.1.0.0.2      Beta                                                              0
    TNS for Linux: Version 12.1.0.0.2 - Beta                                                  0
    NLSRTL Version 12.1.0.0.2 - Beta                                                          0
    
    SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    
        CON_ID       DBID NAME                           OPEN_MODE
    ---------- ---------- ------------------------------ ----------
             2 4043918109 PDB$SEED                       READ ONLY
             3 2346805300 LX1                            READ WRITE
             4 2385557792 LX2                            READ WRITE
             5 1565384817 FF                             READ WRITE
    
    SQL> alter session set container = ff;
    
    Session altered.
    
    SQL> @?/rdbms/admin/awrrpt.sql
    
    Current Instance
    ~~~~~~~~~~~~~~~~
    
       DB Id    DB Name      Inst Num Instance
    ----------- ------------ -------- ------------
     2412861330 xifenfei               1 xff
    
    
    Specify the Report Type
    ~~~~~~~~~~~~~~~~~~~~~~~
    Would you like an HTML report, or a plain text report?
    Enter 'html' for an HTML report, or 'text' for plain text
    Defaults to 'html'
    Enter value for report_type: text
    
    Type Specified:  text
    
    
    Instances in this Workload Repository schema
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
       DB Id     Inst Num DB Name      Instance     Host
    ------------ -------- ------------ ------------ ------------
    * 2412861330        1 xifenfei        xff          xifenfei
    
    Using 2412861330 for database Id
    Using          1 for instance number
    
    
    Specify the number of days of snapshots to choose from
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Entering the number of days (n) will result in the most recent
    (n) days of snapshots being listed.  Pressing <return> without
    specifying a number lists all completed snapshots.
    
    
    Enter value for num_days: 2
    
    Listing the last 2 days of Completed Snapshots
    
                                                            Snap
    Instance     DB Name        Snap Id    Snap Started    Level
    ------------ ------------ --------- ------------------ -----
    xff          xifenfei               61 14 Dec 2012 00:00      1
                                     62 14 Dec 2012 01:00      1
    
                                     63 15 Dec 2012 16:21      1
                                     64 15 Dec 2012 17:00      1
                                     65 15 Dec 2012 18:00      1
                                     66 15 Dec 2012 19:00      1
                                     67 15 Dec 2012 20:00      1
    
    
    
    Specify the Begin and End Snapshot Ids
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Enter value for begin_snap: 63
    Begin Snapshot Id specified: 63
    
    Enter value for end_snap: 64
    End   Snapshot Id specified: 64
    
    
    
    Specify the Report Name
    ~~~~~~~~~~~~~~~~~~~~~~~
    The default report file name is awrrpt_1_63_64.txt.  To use this name,
    press <return> to continue, otherwise enter an alternative.
    
    Enter value for report_name: 
    
    Using the report name awrrpt_1_63_64.txt
    select output from table(dbms_workload_repository.awr_report_text( :dbid,
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    Process ID: 6540
    Session ID: 22 Serial number: 173
    
    
    ERROR:
    ORA-03114: not connected to ORACLE
    
    
    Report written to awrrpt_1_63_64.txt
    
  2. 非常感谢你的分享.
    冒昧的请教下, 在 Oracle 12c 的 Pluggable Database 里面, 对AWR进行创建快照操作, 会提示:
    ORA-13516: AWR Operation failed: database is pluggable.
    实际上其他修改 AWR的任何语句都会提示这个信息.

    这说明 Oracle 12c 不支持在Pluggable Database 里面进行AWR操作了吗?

  3. 测试证明invisible会修改col$.col#为0,visible会修改col$.col#为最大值(这样就实现了该方法修改col$.col#来实现Column Ordering)

    SQL> select name,col# from sys.col$ where obj#=90612;
    
    NAME             COL#
    ---------- ----------
    A                   3
    B                   2
    C                   1
    
    SQL> ALTER TABLE t_xifenfei MODIFY (c invisible);
    
    Table altered.
    
    SQL> select name,col# from sys.col$ where obj#=90612;
    
    NAME             COL#
    ---------- ----------
    A                   2
    B                   1
    C                   0
    
    SQL> ALTER TABLE t_xifenfei MODIFY (c visible);
    
    Table altered.
    
    SQL> select name,col# from sys.col$ where obj#=90612;
    
    NAME             COL#
    ---------- ----------
    A                   2
    B                   1
    C                   3
    
    SQL> desc t_xifenfei
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     B                                                  NUMBER
     A                                                  NUMBER
     C                                                  NUMBER
    

Comments are closed.