ORA-31603: object of type TYPE not found in schema and DBMS_METADATA.GET_DDL Privilige SELECT_CATALOG_ROLE in Oracle

Hi,

Sometimes developers need any table DDL using DBMS_METADATA.GET_DDL, but they don’t have related priviliges.

Details of error are as follows.

 

SQL> select dbms_metadata.get_ddl( 'TABLE', 'TEST_TABLE','MSDBA' ) from dual;


ERROR at line 1:

ORA-31603: object "TEST_TABLE" of type TYPE not found in schema "MSDBA"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

ORA-06512: at "SYS.DBMS_METADATA", line 628

ORA-06512: at "SYS.DBMS_METADATA", line 1221

ORA-06512: at "USER2.TEST", line 4

ORA-06512: at line 1

 

 

 

To solve this error, grant SELECT_CATALOG_ROLE  to user as follows.

GRANT SELECT_CATALOG_ROLE TO <schema>;

SQL> grant SELECT_CATALOG_ROLE to MSDBA;

Grant succeeded.

 

 

Run again, it works fine.

SQL> select dbms_metadata.get_ddl( 'TABLE', 'TEST_TABLE','MSDBA' ) from dual;




DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE','MSDBA')

--------------------------------------------------------------------------------

CREATE TABLE "MSDBA"."TEST_TABLE"

( "OFFER_ID" VARCHAR2(30 CHAR)

 

 

 

Do you want to learn the DBMS_METADATA.GET_DDL detailed, then read the following articles.

https://ittutorial.org/how-to-get-ddl-create-script-of-any-object-in-oracle-using-dbms_metadata-get_ddl/