ALL_OBJECTS报告的值不同.OBJECT_ID和ALL_ARGUMENTS.OBJECT_ID在Oracle 2

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复制了它:

• Oracle 21c

似乎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    |

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

我正在使用Oracle数据库21c快速版发布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等引用回"true";所属对象，即根容器中的对象。

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

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 34293726 - ALL_OJBECTS中的错误object_id导致错误的结果。请不要被bug本身的标题误导，all_objects中的object_id是错的还是在其他视图中，这还有待确定。

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

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

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中，all_objects中的object_id是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 (con_id = 3)中的object_id16335如何显示为包本身，而在CDB (con_id = 1)中，相同的object_id被报告为公共同义词。同时，object_id16334指的是CDB中存在的实际对象，该对象在多个pdb之间共享。

缺失的链接是与PDB内的其他ALL_*视图，它们引用CDB中的object_id16334中的对象，恰好是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 |       |       |
---------------------------------------------------------------------------------------------------------------