[20250812]一些cdb开头的视图存在隐藏列.txt
–//昨天看了链接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,字段名没有双引号。
京ICP备14020293号-4