sql - 在 Oracle 21c 中为 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

This dbfiddle reproduces it :

• 在 Oracle 21c 上
• 在 Oracle 18c 上
• 但不是在 Oracle 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    |

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

我使用的是 Oracle Database 21c Express Edition 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 等指的是“真正的”拥有对象，即根容器中的对象。

这里有很多奇怪的小指针和东西来支持 Multi-Tenancy ，例如

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