SEO

#1 (**permalink**) 02-24-2008, 05:51 PM

hello Gurus, i have a clarification. is there any scripts or tool to
extract only the structure and schemas without the data of an existing
database, and use the same for creating a new one. thanks in advance.

#2 (**permalink**) 02-24-2008, 05:51 PM

Read up up the Oracle "export" utility.

exp system/<password> file=<output_file> full=y rows=n

HTH

--g

#3 (**permalink**) 02-24-2008, 05:51 PM

You can use exp with the switch rows=n
This will dump the definitions only.

Hth

--
Sybrand Bakker
Senior Oracle DBA

#4 (**permalink**) 02-24-2008, 05:52 PM

lakshmananl wrote:
> hello Gurus, i have a clarification. is there any scripts or tool to
> extract only the structure and schemas without the data of an existing
> database, and use the same for creating a new one. thanks in advance.

Try

exp schema_name/pwd@connect_string full=y indexfile=create_script.sql
rows=n

This will create the create_script.sql

with all the ddl. You should be able to modify this script (for
tablespaces etc) and use it to create an identical schema without the
data.

Hth

Ravi

#5 (**permalink**) 02-24-2008, 05:52 PM

"RaviAlluru" <ravi.alluru@gmail.com> wrote in message
news:1132338452.693976.80080@f14g2000cwb.googlegro ups.com...
>
> lakshmananl wrote:
>> hello Gurus, i have a clarification. is there any scripts or tool to
>> extract only the structure and schemas without the data of an existing
>> database, and use the same for creating a new one. thanks in advance.
>
>
> Try
>
> exp schema_name/pwd@connect_string full=y indexfile=create_script.sql
> rows=n
>
> This will create the create_script.sql
>
> with all the ddl. You should be able to modify this script (for
> tablespaces etc) and use it to create an identical schema without the
> data.
>
> Hth
>
> Ravi
>

more like:

exp scott/tiger file=scott rows=N
imp scott/tiger file=scott full=Y indexfile=scott_dml.sql

but the all table DML is REM'd out (and not formatted very nicely)

you may want to look into the DBMS_METADATA package

++mcs

#6 (**permalink**) 02-24-2008, 05:53 PM

you could use pretoria to clean up the indexfile generated by exp/imp
http://sourceforge.net/projects/pretoria

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:LLmdnWRzTczauePeRVn-uw@comcast.com...
>
> "RaviAlluru" <ravi.alluru@gmail.com> wrote in message
> news:1132338452.693976.80080@f14g2000cwb.googlegro ups.com...
> >
> > lakshmananl wrote:
> >> hello Gurus, i have a clarification. is there any scripts or tool to
> >> extract only the structure and schemas without the data of an existing
> >> database, and use the same for creating a new one. thanks in advance.
> >
> >
> > Try
> >
> > exp schema_name/pwd@connect_string full=y indexfile=create_script.sql
> > rows=n
> >
> > This will create the create_script.sql
> >
> > with all the ddl. You should be able to modify this script (for
> > tablespaces etc) and use it to create an identical schema without the
> > data.
> >
> > Hth
> >
> > Ravi
> >
>
> more like:
>
> exp scott/tiger file=scott rows=N
> imp scott/tiger file=scott full=Y indexfile=scott_dml.sql
>
> but the all table DML is REM'd out (and not formatted very nicely)
>
> you may want to look into the DBMS_METADATA package
>
> ++mcs
>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

database oracle9i structure/schemas