Re: How do I script object creation?

Weasel wrote:
> Hello all,
>
> I have a frustrating problem: how do I replicate the effects of
> SQLserver's "Generate SQL script" function in Oracle?
>
> For those unfamiliar with the functionality, you can designate any
> objects, across any schema, and SQLserver will automatically generate
> all DDL for creating the object (in the case of tables and indexes,
> they will be created as empty objects).
>
> I know how to create dynamic sql, but I'm looking for a
> mostly-automated means to do the same thing.
>
> I've been playing with the Change Management pack in OEM, and it seems
> that if I create a baseline, that will get me 90% of the way to
> re-creating objects (just have to clean up some comments, etc). Or, I
> can use the Synchronization Wizard (after having created my baseline)
> to put 2 dbs in sync. But it seems to be a very closely-guarded secret
> how to do this, as the baseline file seems to be stored in the DB
> itself, and not externally.
>
> Any advice you can give me what be vastly appreciated.
>
>
>
> Regards,
> Julius

Rene and Daniel gave a good reply to use dbms_metadata.

Another option (from the days of pre 9i release) is to export the schema tables with rows=n and tables=(....,....) options. Then use any text search tool like findstr, grep etc to look for CREATE and ALTER statements in the dump file. For stored procedures and views, you can easily construct a sql statement around user_source and user_views and spool the result to a file.

It's doable but cumbersome ;)

Regards
/Rauf Received on Wed Nov 09 2005 - 05:45:03 CST