Re: How to create a DDL for a schema

When I run under system everythings work fine with dbms_metadata.get_ddl.

But under user A I want to see metadata from user B, there is always the error ORA_31603. Cannot find the object in schema B. Even gave user dba roles ...
Eample: select dbms_metadata.get_ddl('INDEX','DIM_ACTION_IDX01',B) from dual;

When I run this under B, there is no problem. I am using dbms_metadata.get_ddl in a package, and want different users to use this.

Is system the only option?

gareth_at_jamms.org wrote:
> Don't top-post please, people don't like it :)
>
> Anyway, a bit of lateral thinking produces:
>
> select dbms_metadata.get_ddl('TABLE',table_name,'<schema_name>')
> from all_tables
> where owner='<schema_name>';
>
> And:
>
> select dbms_metadata.get_ddl('INDEX',index_name,'<schema_name>')
> from all_indexes
> where owner='<schema_name>';
>
> Run as SYSTEM rather than SYS.
>
> HTH
>
> -- g
Received on Thu Nov 10 2005 - 05:51:14 CST