Re: Create a SQL script used to create a new copy of a user's schema.

Gerard H. Pille <gerard_h_pille_at_hotmail.com> wrote in article <01bc75de$a9c23d80$3e0d4ac1_at_pcghp>...
> An export without data should do the trick, no?
>

No it will not.

Bill needs to edit the storage parameters on the create table etc statements, and you cant do that with an dataless export. There are parameters to imp which will produce craete index statements etc, but not table definitions, ISTR.

> Bill Hockridge <beh_inc_at_ix.netcom.com> schreef in artikel
> <339D7D2D.26FCAABE_at_ix.netcom.com>...
> > Does anyone have a script, executable, process, etc. that will read an
> > existing user's schema and create a SQL script that, if run, would
> > create a new copy of the schema.
> >
> > I need to downsize an existing schema (storage parameters are gigantic)
> > into a baseline schema that will be used for regression testing.
> >
> > Thanks in advance for any help provided,
> >
> > Bill Hockridge - Independent Oracle DBA.
> > beh_inc_at_ix.netcom.com
> >
> >
>

Theres a chap [John S. Fletcher (jfletche_at_know.demon.co.uk)] who has produced a shareware tool which should almost do what you want. He has a web page, sorry I don't have the URL. Its called schema.exe, written in Pro*C; alas its windows NT only at the moment, and also exports the data (as insert statements) with the schema, but perhaps you could convince him to sell you the source?

Alternatively, you could build sql scripts which read the data dictionary, and produce a suitable sql scripts (which is what schema.exe doubtless does).

Finally, some versions of export produce files which are readable with a text editor. These contain SQL statements to create database objects, and you can cut and paste these.

-- 
Miles Thomas
Logica UK Ltd
thomasm "at" logica "dot" com
The above are personal opinions, and are
not necessarily the opinions of my employer.