lfree Blog
[20250812]一些cdb開頭的視圖存在隱藏列.txt - Stories Detail
--//昨天看了鏈接https://mikedietrichde.com/2025/08/11/were-you-aware-of-the-two-hidden-columns-in-cdb-views/，給出一些信
--//息一些cdb視圖存在2個隱藏列(實際上不止2個，只不過這兩個比較有意義罷了)。
--//在我的機器上測試一下，看看一些相關細節。
1.環境：
SYS@book> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.測試：
SYS@book> @ desc CDB_DIRECTORIES
Name Null? Type
------------------------------- -------- ----------------------------
1 OWNER NOT NULL VARCHAR2(128)
2 DIRECTORY_NAME NOT NULL VARCHAR2(128)
3 DIRECTORY_PATH VARCHAR2(4000)
4 ORIGIN_CON_ID NUMBER
5 CON_ID NUMBER
--//desc信息顯示5個字段。看看視圖定義。
SYS@book> @ v2 CDB_DIRECTORIES
Show SQL text of views matching "CDB_DIRECTORIES"...
V_OWNER VIEW_NAME TEXT TEXT_VC
------- --------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
SYS CDB_DIRECTORIES SELECT k."OWNER",k."DIRECTORY_NAME",k."DIRECTORY_PATH",k."ORIGIN_CON_ID",k."CON_ID", k.CON$NAME, SELECT k."OWNER",k."DIRECTORY_NAME",k."DIRECTORY_PATH",k."ORIGIN_CON_ID",k."CON_ID", k.CON$NAME,
k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG FROM CONTAINERS("SYS"."DBA_DIRECTORIES") k k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG FROM CONTAINERS("SYS"."DBA_DIRECTORIES") k
no rows selected
--//仔細看一下就是發現視圖定義還存在4個字段，分別是
--//k.CON$NAME,k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG
CDB_* views include these hidden columns:
CON$NAME: This column includes the name of the container whose data a given CDB_* row represents
CDB$NAME: This column displays the name of the CDB whose data a given CDB_* row represents
--//作者測試例子：
SYS@book> select * from CDB_DIRECTORIES where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID CON_ID
----- -------------- ------------------------------------------------------------------ ------------- ----------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/book/dpdump/ 1 1
SYS DATA_PUMP_DIR /u01/app/oracle/admin/book/dpdump/1F36F47256D41C08E0636538A8C03260 1 3
SYS@book> column CON$ERRMSG format a20
SYS@book> select con$name,cdb$name,CON$ERRNUM,CON$ERRMSG,a.* from CDB_DIRECTORIES a where DIRECTORY_NAME='DATA_PUMP_DIR';
CON$NAME CDB$NAME CON$ERRNUM CON$ERRMSG OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID CON_ID
-------- -------- ---------- ------------ ----- -------------- ------------------------------------------------------------------ ------------- ----------
CDB$ROOT book 0 SYS DATA_PUMP_DIR /u01/app/oracle/admin/book/dpdump/ 1 1
BOOK01P book 0 SYS DATA_PUMP_DIR /u01/app/oracle/admin/book/dpdump/1F36F47256D41C08E0636538A8C03260 1 3
--//這樣做的好處就是不用使用連接操作，就可以獲得CON$NAME。
--//嘗試在toad下查看定義：
CREATE OR REPLACE FORCE VIEW SYS.CDB_DIRECTORIES
(OWNER, DIRECTORY_NAME, DIRECTORY_PATH, ORIGIN_CON_ID, CON_ID)
BEQUEATH DEFINER
AS
SELECT k."OWNER",k."DIRECTORY_NAME",k."DIRECTORY_PATH",k."ORIGIN_CON_ID",k."CON_ID",
k.CON$NAME, k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG FROM CONTAINERS("SYS"."DBA_DIRECTORIES") k;
--//前面僅僅5個，select顯示多了4個字段，能這樣定義視圖嗎？自己做一個嘗試。
SCOTT@book01p> create or replace view v_emp(EMPNO,ENAME) as select EMPNO,ENAME,DEPTNO from emp;
create or replace view v_emp(EMPNO,ENAME) as select EMPNO,ENAME,DEPTNO from emp
*
ERROR at line 1:
ORA-01730: invalid number of column names specified
SCOTT@book01p> CREATE OR REPLACE FORCE VIEW v_emp (empno,ename) BEQUEATH DEFINER AS select EMPNO,ENAME,DEPTNO from emp;
Warning: View created with compilation errors.
SCOTT@book01p> show error
Errors for VIEW V_EMP:
LINE/COL ERROR
-------- ------------------------------
0/0 ORA-01730: invalid number of
column names specified
SCOTT@book01p> CREATE OR REPLACE FORCE VIEW v_emp (empno,ename) BEQUEATH DEFINER AS select EMPNO,ENAME,DEPTNO from CONTAINERS(emp);
Warning: View created with compilation errors.
--//不行!!
--//你可以查詢cdb視圖，許多類似包含 FROM CONTAINERS的視圖都存在類似的情況，在最後多定義4個字段。
--//添加的風格都與前面不同k.CON$NAME, k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG,字段名沒有雙引號。
1 users favorite the story!