Re: 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: <gareth_at_jamms.org>
Date: Thu, 19 Mar 2009 06:43:10 -0700 (PDT)
Message-ID: <16b6651e-8245-4c0f-a979-850305f60ed1_at_h20g2000yqn.googlegroups.com>
On 19 Mar, 12:49, dana_at_w..._at_yahoo.com wrote:
> 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
Date: Thu, 19 Mar 2009 06:43:10 -0700 (PDT)
Message-ID: <16b6651e-8245-4c0f-a979-850305f60ed1_at_h20g2000yqn.googlegroups.com>
On 19 Mar, 12:49, dana_at_w..._at_yahoo.com wrote:
> 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
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 Received on Thu Mar 19 2009 - 08:43:10 CDT