Re: Generate DDL from a Schema
- From: Brian Peasland <dba@xxxxxxxxxxxxxxxxxxx>
- Date: Mon, 24 Sep 2007 09:30:55 -0500
nickli2000@xxxxxxxxx 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@xxxxxxxxxxxxxxxxxxx
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
.
- References:
- Generate DDL from a Schema
- From: nickli2000
- Generate DDL from a Schema
- Prev by Date: Re: DBLink, Oracle 10 to Oracle 7...
- Next by Date: Re: DBLink, Oracle 10 to Oracle 7...
- Previous by thread: Re: Generate DDL from a Schema
- Next by thread: Merge involving a collection
- Index(es):
Relevant Pages
|
