Home -> Usenet -> c.d.o.misc -> DBMS_METADATA.GET_DDL fails even though I have select privs. Can only use it with DBA privs or on a schema you have u/n and p/w for?

DBMS_METADATA.GET_DDL fails even though I have select privs. Can only use it with DBA privs or on a schema you have u/n and p/w for?

From: <dana_at_work_at_yahoo.com>
Date: Thu, 19 Mar 2009 05:49:31 -0700 (PDT)
Message-ID: <c2b352ae-23ea-45c9-b3ff-b25f39b329d5_at_l16g2000yqo.googlegroups.com> 





Logged in as SCHEMA_A, I want to produce DDL for a table in SCHEMA_B.
This fails, telling me the object doesn't exist.



   select dbms_metadata.get_ddl('TABLE', MY_TABLE', 'SCHEMA_B') from
dual



ERROR:



ORA-31603: object "MY_TABLE" of type TABLE not found in schema
"SCHEMA_B"


ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 653
ORA-06512: at "SYS.DBMS_METADATA", line 1260
ORA-06512: at line 1





However, if I do:



 select * from SCHEMA_B.MY_TABLE;



... this executes without a problem.



Is there a limitation with DBMS_METADATA such that it can only be run
on objects owned by the account one is logged in to when running
GET_DDL, or can only be run logged in as a user with SELECT ANY TABLE
and/or DBA privs?



I'm on a 9i database.



Thanks.



Dana
Received on Thu Mar 19 2009 - 07:49:31 CDT












Original text of this message