Script to recreate database schema?

[sukimac]

We are developing a 'core technology' for website development. Essentially, our CTO wants to be able to "push a button" and have a website up and running. My job is to make the database schema portable. I suggested using export/import. However, our CTO wants to be able to create a script that will load up all the tables, objects. Anyone have a suggested way to generate a script that will load up a database schema?

[marist89]

Use export to export the schema. Then, use import with the indexfile option to create the DDL that creates the objects in the schema.

[sukimac]

yes, that's what I suggested. My CTO is wary of Oracle (he's open source all the way), he insists there should be a way to create a script from an existing db - like MySQL.

[sukimac]

oops I didn't read your reply carefully marist89 - indexfile option will generate a script ?

[sreddy]

I don't think that an option. If at all the logical/physical structure changes recreating DDL is real pain.

That doesn't create script but spits out all DDL for objects creation like a script. with comments on the tables creation.

[Edited by sreddy on 02-21-2001 at 01:14 PM]

[marist89]

[QUOTE][i]Originally posted by sukimac [/i]
[B]indexfile option will generate a script ? [/B][/QUOTE]
Yes, it will dump an SQL script with the DDL for table and index creation. I'm not sure if it will dump the FK constraints, though; you'll have to check it out...

[Prasy]

hi,

If u need only the structure, exp it with option rows=n, constraints=y full=y...

Suppose if u need only the script and not to import actually, u can use
imp show=y log=logfilename

So u will have the script in the logfile. Set constraints if u need constraints too.

[LND]

best thing is to use exp of course (provided DB version support it).

just be aware about schema level issues:
public grants and synonyms, tablespaces, etc. Always create tablespaces with enough space to keep initial extents for table or use 8i new option - locally managed tablepsaces. But still consider tablespace sizing.

The best thing is to write a script to import db and test-upgrade it untill no warning are left.

The issue is that some failed import commands can cause problems and next imports will not fix them (like indexes and related primary keys).

Consider correct import of statistics if CBO optimizer is used.
Remember, exp has a variety of options:
rows=no
constraints=no
indexes=no
grants=no

show=yes - to get kind of DDL listing, but quite messy.
indexfile

It can exp/imp full database as well.