Re: Generate DDL from a Schema

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Mon, 24 Sep 2007 09:30:55 -0500
Message-ID: <46f7be2e$0$26379$88260bb3@free.teranews.com>

nickli2000_at_gmail.com wrote:
> Hi,
>
> I am trying to generate DDL scripts for each table, view and other
> indexes under a schema. I tested using DBMS_METADATA package and its
> get_ddl procedure. However, I can only output the create table (view,
> indexes) statements for all the tables in a schema in one script, not
> in each separation script, such as create table table_1.script, create
> table table_2.script, etc.
> I have hundreds of tables, views, indexes, types and other objects
> under one schema. I would appreciate your advice on the issue.
>
> Thanks in advance.
>
> Nick Li
>

If I understood what you are asking, you will have to create a script which creates your scripts. Something like the following:

set heading off
set pagesize 0
set feedback off
spool create_ddl.sql
SELECT 'spool create_'||object_type||'_'||object_name||'.sql'||chr(10)||

'SELECT dbms_metadata.get_ddl('''||object_type||''','''||object_name||
''','''||owner||''') from dual;'||chr(10)||
'spool off'

FROM dba_objects WHERE owner='SCOTT';
spool off
@create_ddl.sql

The above creates the commands to start spooling to a specific file, then then run the GET_DDL function.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Mon Sep 24 2007 - 09:30:55 CDT