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?

[dana_a...@yahoo.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

[gar...@jamms.org]

From the 10gR2 documentation:

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

"Nonprivileged users can see the metadata of only their own objects."

HTH

-g

[dana_a...@yahoo.com]

On Mar 19, 9:43 am, gar...@jamms.org wrote:
> From the 10gR2 documentation:
>

> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_meta...

>
> "Nonprivileged users can see the metadata of only their own objects."
>
> HTH

Thanks. That explains it.

Dana