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