Monday, June 25, 2007

Restoring from Oracle Dump Without any Data

I was trying to restore an Oracle backup dump the other day. The only catch was that I didn't want any data in the restore. However, large amount of data in the dump would have made the whole process last for a whole day ... no kidding. I started Googling around but unfortunately there was little to no information available. Once I found the solution I decided to share it for everybody's benefit.

Command for restoring from a dump file is age old imp command. So I am not going to go into that any further. What will do the trick for you is rows flag. If you want to import all the table structures with no data, use the flag as below

rows=n

Default for this flag is Y, so if you don't use this flag it will import all the data.

2 comments:

scoprion_4000 said...

can u write the command for import..meaning where to use the rows tag...???

Sachin Sinha said...

Here is the sample command. Use the options as you might need them.

imp username/***** FILE=xyzexp.dmp ANALYZE=N
BUFFER&H1000000 COMMIT=N COMPILE=Y CONSTRAINTS=Y FEEDBACK&H100000
FROMUSER=SIEBEL TOUSER=SIEBEL FULL=N GRANTS=Y IGNORE=Y INDEXES=N
LOG=c:\downloads\imp_data.log RECORDLENGTH&H32768 ROWS=Y
STATISTICS=NONE

Loading...