DDL Extraction Script needed

[Thargy]

I am under time pressure to deliver a script which can:-

Extract the DDL for all the tables in a given schema, extract the DDL for constraints, views and materilaized views.

As I have crunch time scales, I'm blatantly asking for any scripts which you may already have, that I can modify for my own use.

I'm posting from home, as I have to deliver by close of play tomorrow.

Regards

Tharg

Grinding away at things Oracular

 

[carp]

Tharg -
Unfortunately, I don't have a script made up already and I only have about five minutes before I have to be back in a meeting. However, there is a function called dbms_metadata.get_ddl that allows you to provide an object type, schema, and name; it returns the DDL for the designated object.
There is a companion function called GET_DEPENDENT_DDL that will return the DDL for dependent objects (e.g., indexes).
Hopefully this will cut down your development time if nobody has a script (SantaMufasa doubtlessly has a beauty already coded up for you!).

 

[Thargy]

carp,

thanks. I'm firing up my one disk wonder at home, so that I can get on with it. I'm hoping the U.K./U.S. time zones can work in my favour, and let me stretch time, since for you guys it's still the working day.

cheers.

T

Grinding away at things Oracular

 

[LKBrwnDBA]

Do you have to "write" script(s)?

Why not use DDL Wizard?

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

John (Tharg),

(Geez, I hate it when Tek-Tips goes down and I can't get a response out as quickly as I'm used to. Sorry for the delay.)

Even if it wasn't "working hours" for us, we'd be happy to "stay up" and help. Just let us know. I'm in front of this silly monitor from 8:00 a.m. to 1:00 a.m. (GMT +7) daily.

As far as your DDL question is concerned, another possibility is to "exp" your schema, then do an "imp ...indexfile=<some_name.sql>...", which generates all of the DDL, in plain text, for the contents of your schema. The only downside is that, although fully present, the DDL for your tables is "REM'd" out...I just do a global replace (in Word, for example) of all the "REM " strings.

Let us know if this idea is helpful, in addition to Carp's always-great suggestions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Santa,

isn't it just typical, right when you need it most, the damn thing goes and quits.

I'm swotting up on dbms_metadata (which I knew existed, but carp's suggestions helped tremendously).

It's 10 now, and I'm so tired, that I've called it a day, and will resume battle tomorrow at work. I just can't think straight any more.

thanks for being there, the moral support is vital.

My thanks to you all for being so helpful.

John

Grinding away at things Oracular

 

[SantaMufasa]

Not a prob...and I goofed...I'm GMT-7...I'm not in Bangkok (GMT +7). But you knew that already, huh.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Santa,

posting from work now. Yes, I know that the sun rises in the East and sets in the West.....

Maybe this should be a separate thread at the water cooler?

T

Grinding away at things Oracular

 

[dbomrrsm]

spool c:\index_ddl.sql
set long 32000
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,'SCHEMA')
FROM all_indexes u;
/
spool c:\table_ddl.sql
set long 32000
Select dbms_metadata.get_ddl('TABLE',a.TABLE_NAME,'SCHEMA')
from
all_tables a;

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Thargy]

dbo,

thanks for the post.

Unfortunately, whenever I try to get some table metadata, sql plus starts running, and never stops.

I've had one successful trivial run in TOAD, and none is SQL plus.

Has anyone else experienced such difficulties with DBMS_METADATA?

Regards

T

Grinding away at things Oracular

 

[Thargy]

dbo,

thanks for the post.

Unfortunately, whenever I try to get some table metadata, sql plus starts running, and never stops.

I've had one successful trivial run in TOAD, and none is SQL plus.

Has anyone else experienced such difficulties with DBMS_METADATA?

Regards

T

Grinding away at things Oracular

 

[dbomrrsm]

it may be that you are slecting all tables when maybe you dont need to ??

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,'SCHEMA')
FROM all_indexes u
where table_name in (
YOUR LIST OF TABLES);

I have never had trouble but I have only ever used it on smallish schemas


[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Thargy]

Guys,

thanks for all your help.

I haven't made the deadline, but my boss now realises that it was impossible anyway - phew!

I was truly grateful for everyone's willingness to help, even when I was asking for a crib. I am working at a slightly more sane pace now, and our dev team is living more nearly in the real world, where software isn't written in a day.

I shall do my best to help others, once this peak of activity is passed.

Regards

T

Grinding away at things Oracular

 

[dbtoo2001]

Did you try downloading a trial copy of TOAD? It has facilities to extract DDL.

 

[Thargy]

dbtoo,

yes, I used TOAD to produce tons of DDL. The problem is due to the complete lack of documentation of the legacy system. I was largely flying blind, so I needed analysis + DDL + domain knowledge all at once.

Needless to say, this wasn't readily available.

Regards

T

Grinding away at things Oracular