RE: Using dbms_metadata to extract a full schema

From: Eberhard, Jeff <Jeff.Eberhard_at_TriumphGS.com>
Date: Fri, 30 Apr 2004 12:46:00 -0600
Message-ID: <86B94CDA99C77A408129A378D2DA8DB8AFC9BD@compqsrv.rolls-roycegs.com>

Quick and dirty, please correct if needed:

/**********************************************************************


 * File:        gen_schema_ddl.sql

 * Type:        SQL*Plus script

 * Author:      Jeff Eberhard 

 * Script Layout by Tim Gorman, Thanks Tim

 * Date:        30-Apr-04

*

• Description:
• SQL*Plus script to use the technique of "SQL-generating-SQL"
• to generate another SQL*Plus script to show the DDL of a given
• schems. Then, the generated script is run...
  *
  
• Modifications:
  *********************************************************************/
  undefine schemaname set echo off feedb off time off timi off pages 0 lines 120 pau off verify off

select 'select
dbms_metadata.get_ddl('''||object_type||''','''||object_name||''',''&&schema name'') from dual;' from dba_objects where owner = '&&SCHEMANAME';

spool run_ddl_display.sql
/
spool off

spool run_ddl_display
start run_ddl_display
spool off

REM host /bin/rm -f run_recompile.*

-----Original Message-----
From: Daniel Fink [mailto:Daniel.Fink_at_Sun.COM] Sent: Friday, April 30, 2004 11:41 AM
To: oracle-l_at_freelists.org
Subject: Using dbms_metadata to extract a full schema

Okay, I've rtfmed, ctnomed, googled and I'm still not finding anything close.

I want to use dbms_metadata to extract all the objects associated with a specific schema. Every article I find that says "Here's how you extract a schema" shows how to extract multiple tables and, sometimes, indexes. Unfortunately, the schema I'm looking at has triggers, sequences, etc. Even the Oracle documentation examples cover multiple tables though they say that they are extracting a schema.

Yes, I know I can use exp w/rows=N to do the same thing, but I am trying to learn something new.

Any examples, docs, etc. are greatly appreciated.

Daniel

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Apr 30 2004 - 13:43:47 CDT