What Oracle privileges do I need to use DBMS_METADATA.GET_DDL?

Question

(Sorry about my ignorance here - I'm not an experienced Oracle user.)

I am trying to use the DBMS_METADATA.GET_DDL function (along with ALL_OBJECTS or some) to get the DDL for all tables in a particular schema. When I do this (for all objects or for one specific object), I get error ORA-31603 ("FOO object" of type TABLE not found in the SCHEMA schema).

I assume this means that the user I’m logged in with does not have any privilege necessary to read the metadata needed for GET_DDL. What privilege is needed? Is there a way to log in to confirm that the current user has / does not have this privilege?

thanks! Lee

Answer 1

Read this document, but mostly you need SELECT_CATALOG_ROLE

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1016867