Different values reported for ALL_OBJECTS.OBJECT_ID and ALL_ARGUMENTS.OBJECT_ID in Oracle 21c

Question

I've noticed that for some objects in the SYS schema, the two following columns report different values:

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

For example:

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';

Produces

OBJECT_ID
---------
14813

OBJECT_ID
---------
14812

OBJECT_ID
---------
14812

This dbfiddle reproduces it:

• On Oracle 21c
• On Oracle 18c
• But not on Oracle 11g

It seems that the data contained in ALL_OBJECTS is wrong? I can't find any entries in ALL_PROCEDURES for OBJECT_ID = 14813, and conversely, OBJECT_ID = 14812 produces this object in ALL_OBJECTS:

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

Results:

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

Quite unrelated. Is this a known bug in the dictionary views? Or am I misunderstanding the semantics of the OBJECT_ID, which I believed was a unique object identifier across the dictionary?

I'm using Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production from here: https://hub.docker.com/r/gvenzl/oracle-xe, though a customer of ours can also reproduce it in 19c Enterprise Edition 19.5.0.0.0

Answer 1

Try it with and without the database being a pluggable database, eg

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

My hypothesis is that the ALL_ARGUMENTS et al are referring back to the "true" owning object, namely the one in the root container.

Plenty of weird little pointers and stuff going on here to support multi-tenant, eg

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