oracle ORA-31603 even when sysdba

Question

I am connected by sqlplus with sys as sysdba to an oracle database 11.2.0.4 Enterprise edition. If I run the query :

   select DBMS_METADATA.GET_DDL ( 'TYPE' , 'SYS_PLSQL_9131_DUMMY_1' , 'SYS' ) from dual ; 

I get error the error

     *ORA-31603: object "SYS_PLSQL_9131_DUMMY_1" of type TYPE not found in  schema "SYS"
      ORA-06512: at "SYS.DBMS_METADATA", line 5805
      ORA-06512: at "SYS.DBMS_METADATA", line 8344*

I get similar errors too if I run

      select DBMS_METADATA.GET_DDL ( 'TYPE_BODY' , 'SYS_PLSQL_9131_DUMMY_1' , 'SYS' ) from dual ;
      select DBMS_METADATA.GET_DDL ( 'TYPE_SPEC' , 'SYS_PLSQL_9131_DUMMY_1' , 'SYS' ) from dual ;

If I query DBA_OBJECTS or DBA_SOURCE, the object of type TYPE is there.

I get these errors even if I query other TYPE type objects in the SYS schema, but not all the TYPE type objects give me this problem.

Answer 1

Those types should be implicitly created by a package. Query DBA_OBJECTS for object_id 9131 (the number in the type name).

It probably refers to a package that has a function which returns a value with a datatype defined in the package specification rather than a simple VARCHAR2 / NUMBER / DATE.

Answer 2

Why is this throwing an error? The problem is not with permissions or the code. The problem is that sometimes an Oracle object isn't really the object type is claims to be. Not all "tables" are real tables, and not all "types" are real types. Oracle may create objects to support other objects, such as nested tables and system-generated types for collections.

How can I tell in advance what will throw an error? As far as I know there's no official documentation or data dictionary view that will tell you when an object is not a real object. The best guide is probably the conditions in this answer. Specifically this part:

    ...
   --These objects are included with other object types.
    and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
       'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
    --Ignore system-generated types that support collection processing.
    and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
    --Exclude nested tables, their DDL is part of their parent table.
    and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
    --Exlclude overflow segments, their DDL is part of their parent table.
    and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')

Some objects cannot be exported and need to be ignored.