Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBMS_METADATA to get dependent DDL

Re: DBMS_METADATA to get dependent DDL

From: Paul Baumgartel <paul.baumgartel_at_gmail.com>
Date: Thu, 15 Dec 2005 17:11:47 -0500
Message-ID: <f8c47710512151411n6505125cxb15bb2a265b944c4@mail.gmail.com>


Thanks. The reason I'm using the fetch_ddl interface is that this is going to be part of a package--I'm recreating some non-partitioned tables as partitioned, and I want to grab the constraint and trigger DDL so I can apply it later.

On 12/15/05, Ethan Post <post.ethan_at_gmail.com> wrote:
>
> Don't have time to look too close but perhaps this script will work
> for you, this is what I required to generate some PK/FK related DDL.
> It created some scripts which I could use to drop, rebuild some
> constraints for a particular issue I was working on.
>
> Call it like this...
>
> @script.sql TABLE_NAME
>
> set echo off feed off pages 0 trims on term off trim on
> set long 500000
> set heading off
> set linesize 255
> set ver off
> set term on
>
> column ddl format a200 word_wrapped
>
> spool ~metadata.sql
>
> select 'select
> dbms_metadata.get_ddl(''REF_CONSTRAINT'','''||a.constraint_name||''')||'';''
> ddl from dual;'
> from
> user_constraints a,
> user_constraints b
> where a.constraint_type='R'
> and a.r_constraint_name=b.constraint_name
> and b.constraint_type='P'
> and b.table_name='&1';
>
> spool off
>
> spool make_fk.sql
> @~metadata.sql
> spool off
>
> spool drop_fk.sql
>
> select 'alter table '||a.table_name||' drop constraint
> '||a.constraint_name||';' ddl
> from
> user_constraints a,
> user_constraints b
> where a.constraint_type='R'
> and a.r_constraint_name=b.constraint_name
> and b.constraint_type='P'
> and b.table_name='&1';
>
> spool off
>
> spool ~metadata.sql
>
> select 'select
> dbms_metadata.get_ddl(''CONSTRAINT'','''||a.constraint_name||''')||'';''
> ddl from dual;'
> from
> user_constraints a
> where a.constraint_type='P'
> and a.table_name='&1';
>
> spool off
>
> spool pk.sql
>
> select 'alter table &1 drop primary key;' ddl from dual;
>
> @~metadata.sql
>
> spool off
>
> !rm ~metadata.sql
>
>
> On 12/15/05, Paul Baumgartel <paul.baumgartel_at_gmail.com> wrote:
> > I've been struggling with this for a day. I am trying to get dependent
> DDL
> > (constraints) for a table, and have written the following function to do
> it
> > (using literals for testing):
> >
>

--
Paul Baumgartel
paul.baumgartel_at_aya.yale.edu

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 15 2005 - 16:12:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US