联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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
测试证明invisible会修改col$.col#为0，visible会修改col$.col#为最大值(这样就实现了该方法修改col$.col#来实现Column Ordering)
非常感谢你的分享.
冒昧的请教下, 在 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