[20260818]建立完善dc_objects.sql脚本.txt

--//原先写的脚本有点问题user_id=1,dba_users以及cdb_users视图没有public用户，实际上public是特殊角色。
--//使用CONTAINERS(sys.user$)代替user$.

--//另外即使使用CONTAINERS(sys.user$)，查询也不包括CON_ID=2，CON_NAME=PDB$SEED的信息。
--//v.con_id=2,username显示SYS。

SYS@book> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 BOOK01P                        READ WRITE NO
--//缺省CON_NAME='PDB$SEED',打开模式READ ONLY，CONTAINERS(sys.user$)一样也不包括CON_ID=2，CON_NAME=PDB$SEED的信息。

$ cat dc_objects.sql
column DC_PROP_NAME format a28
column CACHE_NAME   format a20
column EXISTENT     format a8
column KEY_OID$     format a32
column dc_obj_name  format a32
column dc_obj_name1 format a32
column username     format a18
column con_id       format 99999
column indx         format 99999
column hash         format 99999
column key          noprint
column txn          noprint

 SELECT *
    FROM (SELECT --TO_NUMBER ( (SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2)), 'XXXXXXXX') Schema_User_ID ,
                 DECODE( v.con_id,2,'SYS',
                 (
                   SELECT name FROM CONTAINERS(sys.user$) u
                   WHERE u.con_id= v.con_id AND u.user# =
                            TO_NUMBER (
                               (SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2))
                              ,'XXXXXXXX')))
                    username
                --,RTRIM (UTL_RAW.cast_to_varchar2 (SUBSTR (v.key, 13)), CHR (0)) dc_obj_name
                --,TO_NUMBER (TRIM (BOTH '0' FROM SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2)), 'XXXX') key_str_len
                ,UTL_RAW.cast_to_varchar2 (SUBSTR (v.key, 13,2*TO_NUMBER ( SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX'))) dc_obj_name
                ,TO_NUMBER ( SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX') key_str_len
                ,'0x'||TO_CHAR(v.hash,'FMxxxxx') hash_hex
                ,v.*
            FROM v$rowcache_parent v
           WHERE cache# IN (8,11)
    )
   WHERE (&1)
ORDER BY key;

column key print
column hash clear

--//测试例子：

SYS@book> @ dc/dc_objects "DC_OBJ_NAME='V$MYSTAT' and con_id=1"

USERNAME           DC_OBJ_NAME                      KEY_STR_LEN HASH_HEX   INDX   HASH ADDRESS              CACHE# CACHE_NAME           EXISTENT  LOCK_MODE LOCK_REQUEST SADDR            INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
------------------ -------------------------------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ----------------- ----------------- -- -------- -------- ------
PUBLIC             V$MYSTAT                                   8 0x5448     5719  21576 0000000066C2C220          8 dc_objects           Y                 0            0 00                               0                 0    00       00            1
PUBLIC             V$MYSTAT                                   8 0x11fc     1896   4604 0000000066C2C220          8 dc_objects           Y                 0            0 00                               0                 0    00       00            1

SYS@book> @ dc/dc_objects "con_id=2 and rownum<=3 "

USERNAME           DC_OBJ_NAME                      KEY_STR_LEN HASH_HEX   INDX   HASH ADDRESS              CACHE# CACHE_NAME           EXISTENT  LOCK_MODE LOCK_REQUEST SADDR            INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
------------------ -------------------------------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ----------------- ----------------- -- -------- -------- ------
SYS                I_OBJ3                                     6 0x501      1694   1281 000000006FD1B990          8 dc_objects           Y                 0            0 00                               0                 0    00       00            2
SYS                I_FILE#_BLOCK#                            14 0x26e      1656    622 000000006FD58C60          8 dc_objects           Y                 0            0 00                               0                 0    00       00            2
SYS                I_HH_OBJ#_INTCOL#                         17 0x4b       1617     75 000000006FC26FF0          8 dc_objects           Y                 0            0 00                               0                 0    00       00            2

--//顺便改写dc_objects11g.sql脚本：

$ cat  dc_objects11g.sql
column DC_PROP_NAME format a28
column CACHE_NAME   format a20
column EXISTENT     format a8
column KEY_OID$     format a32
column dc_obj_name  format a32
column dc_obj_name1 format a32
column username     format a20
column con_id       format 99999
column indx         format 99999
column hash         format 99999
column key          noprint

  SELECT *
    FROM (SELECT --TO_NUMBER ( (SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2)) ,'XXXXXXXX') Schema_User_ID,
                (SELECT name username
                     FROM user$
                    WHERE user# =
                             TO_NUMBER (
                                (SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2))
                               ,'XXXXXXXX'))
                    username
                --,RTRIM (UTL_RAW.cast_to_varchar2 (SUBSTR (v.key, 13)), CHR (0)) dc_obj_name
                --,TO_NUMBER (TRIM (BOTH '0' FROM SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2)), 'XXXX') key_str_len
                ,UTL_RAW.cast_to_varchar2 (
                    SUBSTR (v.key, 13, 2 * TO_NUMBER (SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX')))
                    dc_obj_name
                ,TO_NUMBER (SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX') key_str_len
                ,'0x' || TO_CHAR (v.hash, 'FMxxxxx') hash_hex
                ,v.*
            FROM v$rowcache_parent v
           WHERE cache# IN (8, 11))
   WHERE (&1)
ORDER BY key;

column key print
column hash clear