Oracle21c中为ALL_OBJECTS.OBJECT_ID和ALL_ARGUMENTS.OBJECT_ID报告的不同值

Question

我注意到，对于SYS模式中的某些对象，以下两列报告了不同的值：

• ALL_OBJECTS.OBJECT_ID
• ALL_PROCEDURES.OBJECT_ID
• ALL_ARGUMENTS.OBJECT_ID

例如：

select object_id
from all_objects
where object_name = 'DBMS_STATS'
and owner = 'SYS';

select distinct object_id
from all_procedures
where object_name = 'DBMS_STATS'
and owner = 'SYS';

select distinct object_id
from all_arguments
where package_name = 'DBMS_STATS'
and owner = 'SYS';

产

OBJECT_ID
---------
14813

OBJECT_ID
---------
14812

OBJECT_ID
---------
14812

这个dbfiddle复制它

• Oracle21c
• 甲骨文18c
• 但在甲骨文11g上却没有

似乎ALL_OBJECTS中包含的数据是错误的？我在ALL_PROCEDURES中找不到OBJECT_ID = 14813的任何条目，相反，OBJECT_ID = 14812在ALL_OBJECTS中生成这个对象

select owner, object_name, object_type
from all_objects
where object_id = 14812;

结果：

|OWNER |OBJECT_NAME       |OBJECT_TYPE|
|------|------------------|-----------|
|PUBLIC|XS$ROLE_GRANT_LIST|SYNONYM    |

完全不相关。这是字典视图中已知的错误吗？还是我误解了OBJECT_ID的语义，我认为它是字典中唯一的对象标识符？

我正在使用OracleDatabase21c速成版21.0.0.0.0 -从这里生产：https://hub.docker.com/r/gvenzl/oracle-xe，尽管我们的客户也可以在19c企业版19.5.0.0.0中复制它

Answer 1

在数据库是可插入数据库的情况下尝试它，如

SQL> conn / as sysdba
Connected.
SQL> select object_id, object_type
  2  from all_objects
  3  where object_name = 'DBMS_STATS'
  4  and owner = 'SYS';

 OBJECT_ID OBJECT_TYPE
---------- -----------------------
     13795 PACKAGE
     19194 PACKAGE BODY

SQL>
SQL> select distinct object_id
  2  from all_procedures
  3  where object_name = 'DBMS_STATS'
  4  and owner = 'SYS';

 OBJECT_ID
----------
     13795

SQL> alter session set container = pdb1;

Session altered.

SQL> select object_id, object_type
  2  from all_objects
  3  where object_name = 'DBMS_STATS'
  4  and owner = 'SYS';

 OBJECT_ID OBJECT_TYPE
---------- -----------------------
     13796 PACKAGE
     19191 PACKAGE BODY

SQL>
SQL> select distinct object_id
  2  from all_procedures
  3  where object_name = 'DBMS_STATS'
  4  and owner = 'SYS';

 OBJECT_ID
----------
     13795
    127365

我的假设是，ALL_ARGUMENTS等人指的是“真”拥有对象，即根容器中的对象。

这里有很多奇怪的小指点和东西来支持多租户，如

SQL> conn / as sysdba
Connected.
SQL> select dbms_metadata.get_ddl('VIEW','DBA_ARGUMENTS') from dual;

DBMS_METADATA.GET_DDL('VIEW','DBA_ARGUMENTS')
------------------------------------------------------------------------------------------------
---

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_ARGUMENTS" ("OWNER", "OBJECT_NAME", "PA
LOA
D", "SUBPROGRAM_ID", "ARGUMENT_NAME", "POSITION", "SEQUENCE", "DATA_LEVEL", "DATA_TYPE", "DEFAUL
_LE
NGTH", "IN_OUT", "DATA_LENGTH", "DATA_PRECISION", "DATA_SCALE", "RADIX", "CHARACTER_SET_NAME", "
_SU
BNAME", "TYPE_LINK", "TYPE_OBJECT_TYPE", "PLS_TYPE", "CHAR_LENGTH", "CHAR_USED", "ORIGIN_CON_ID"
  select
   OWNER, OBJECT_NAME, PACKAGE_NAME, OBJECT_ID, OVERLOAD,
   SUBPROGRAM_ID, ARGUMENT_NAME, POSITION, SEQUENCE,
   DATA_LEVEL, DATA_TYPE, DEFAULTED, DEFAULT_VALUE, DEFAULT_LENGTH,
   IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, RADIX,
   CHARACTER_SET_NAME, TYPE_OWNER, TYPE_NAME, TYPE_SUBNAME,
   TYPE_LINK, TYPE_OBJECT_TYPE, PLS_TYPE, CHAR_LENGTH, CHAR_USED, ORIGIN_CON_ID
from INT$DBA_ARGUMENTS


SQL> alter session set container = pdb1;

Session altered.

SQL> select dbms_metadata.get_ddl('VIEW','DBA_ARGUMENTS') from dual;
ERROR:
ORA-31603: object "DBA_ARGUMENTS" of type VIEW not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6718
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at line 1

SQL> select count(*)
  2  from dba_objects
  3  where object_name = 'DBA_ARGUMENTS'
  4  and object_type = 'VIEW';

  COUNT(*)
----------
         1

Answer 2

您在这里发现的似乎是数据字典中的一个bug，并被报告为ALL_OJBECTS中的Bug 34293726错误的ALL_OJBECTS，导致错误的结果。请不要被bug本身的标题误导，object_id在all_objects中是错的还是其他视图中的错误还有待确定。

在PDB中以all_objects格式报告的object_id来自由CDB继承的子对象，而其他视图则报告来自CDB本身的object_id。

在我的数据库中，DBMS_STATS包在CDB本身中有object_id of 16334：

SQL> select object_id
from all_objects
where object_name = 'DBMS_STATS'
and owner = 'SYS';

 OBJECT_ID
----------
     16334
     22401

SQL> select distinct object_id
from all_procedures
where object_name = 'DBMS_STATS'
and owner = 'SYS';

 OBJECT_ID
----------
     16334

SQL> select distinct object_id
from all_arguments
where package_name = 'DBMS_STATS'
and owner = 'SYS';  2    3    4

 OBJECT_ID
----------
     16334

然而，在PDB中，object_id In all_objects是16335。

SQL> alter session set container=cdb1_pdb1;

Session altered.

SQL> select object_id
from all_objects
where object_name = 'DBMS_STATS'
and owner = 'SYS';

 OBJECT_ID
----------
     16335
     22398

SQL> select distinct object_id
from all_procedures
where object_name = 'DBMS_STATS'
and owner = 'SYS';

 OBJECT_ID
----------
     16334

SQL> select distinct object_id
from all_arguments
where package_name = 'DBMS_STATS'
and owner = 'SYS';

 OBJECT_ID
----------
     16334

当查看CDB中的cdb_objects时，这里发生的事情变得更清楚了( CDB中的所有对象，或者在PDB本身中执行时仅报告PDB中的所有对象)。

SQL> select con_id, owner, object_id, object_name, object_type
 from cdb_objects
 where object_name = 'DBMS_STATS';

CON_ID OWNER  OBJECT_ID OBJECT_NAME  OBJECT_TYPE
------ ------ --------- ------------ ---------------
     1 SYS        16334 DBMS_STATS   PACKAGE
     1 SYS        22401 DBMS_STATS   PACKAGE BODY
     1 PUBLIC     16335 DBMS_STATS   SYNONYM
     3 SYS        16335 DBMS_STATS   PACKAGE
     3 SYS        22398 DBMS_STATS   PACKAGE BODY
     3 PUBLIC     16336 DBMS_STATS   SYNONYM

注意PDB中的object_id 16335 (con_id = 3)是如何显示为包本身的，而在CDB (con_id = 1)中，相同的object_id被报告为公共同义词。同时，object_id 16334引用CDB中的实际对象，该对象在PDB之间共享。

缺少的链接与PDB中的其他ALL_*视图有关，这些视图指的是CDB中的对象和object_id 16334中的对象，该对象恰好是PDB中all_objects中的一个完全不同的对象：

SQL> select con_id,owner, object_id, object_name, object_type
 from cdb_objects
 where object_id = 16334;

CON_ID OWNER  OBJECT_ID OBJECT_NAME        OBJECT_TYPE
------ ------ --------- ------------------ -----------
     3 PUBLIC     16334 XS$ROLE_GRANT_LIST SYNONYM

这是字典视图中已知的错误吗？还是我误解了OBJECT_ID的语义，我认为它是字典中唯一的对象标识符？

您确实正确地理解了object_id的语义，这似乎是一个错误，并且已经报告了。

也许也值得向这个线程的其他读者指出，随着CDB体系结构的引入，有一件事发生了改变，那就是现在有多个层次字典在起作用。有最多三个字典: CDB ->(应用根)-> PDB。应用程序根容器不是强制性的，在上面的情况下，层次结构只是CDB -> PDB。

在这两种情况下，子继承父对象中的对象，即CDB中的对象可用于所有应用程序根容器，而应用程序根容器可用的对象(包括来自CDB的对象)可供PDB使用。这正是你在上面看到的不一致的地方。对象16334是CDB中的对象，实际的DBMS_STATS包和对象16335是PDB中继承到CDB的包链接。

Answer 3

ALL_OBJECT拥有可插拔数据库中的OBJECT_ID。ALL_ARGUMENTS通过扩展数据链接从CDB$ROOT读取系统元数据，OBJECT_ID来自于此：

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |       |       |     1 (100)|          |       |       |
|*  1 |  FILTER                   |                   |       |       |            |          |       |       |
|   2 |   PARTITION LIST ALL      |                   |   200 | 44800 |     1 (100)| 00:00:01 |     1 |     2 |
|*  3 |    EXTENDED DATA LINK FULL| INT$DBA_ARGUMENTS |   200 | 44800 |     1 (100)| 00:00:01 |       |       |
|*  4 |   FIXED TABLE FULL        | X$KZSPR           |     2 |    18 |     0   (0)|          |       |       |
|   5 |   NESTED LOOPS SEMI       |                   |     1 |    18 |     2   (0)| 00:00:01 |       |       |
|*  6 |    FIXED TABLE FULL       | X$KZSRO           |     2 |    12 |     0   (0)|          |       |       |
|*  7 |    INDEX RANGE SCAN       | I_OBJAUTH1        |     1 |    12 |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------