[20250812]一些cdb开头的视图存在隐藏列.txt

[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,字段名没有双引号。


来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！