Friday, August 1, 2008

IMP/EXP Issue

I have one user. I need to export and import that user. While exporting it is very fast. The dmp file size about 1GB. For some reasons I need to drop that user, recreate the same user and import the same data from that dmpfile. The user contains two tablespaces one for data and another for Indexes. Previously I had a datafile size is around 2GB. While importing I faced two problems,
1) Insufficient tablespce
2) It takes more than 7 hours.I used the following scripts to import in Sun 250 server( os ver 5.7)imp user/pwd file = /data1/data.dmp FULL=Y GRANTS=Y ROWS= Y COMMIT=Y LOG=/data1/i_data.log Can you help me ?

1) insufficient tablespace - that means you didn't have enough room. this typically
happens if you do the default export which uses "compress=y" which generates a create
table statement that has an INITIAL extent equal to the sum of all allocated extents.
Recommend you always export with compress=N.
2) Import is a serial process. Your DMP file was 1gig. That was just the data. The
indexes, constraints, etc were all in there as DDL. When you imported, it imported the
data, then it created all of the indexes, then it verified all of the constraints, then
it compiled all of your code, and so on.....
Its a big sequential process.
So, to "speed that up", you have to do things to make those processes faster
o make sure your alert log doesn't have any "checkpoint not complete" or "archival
required" messages. If it does, you have insufficient log configured, add more.
o check your sort_area_size for your index builds, consider increasing it during the
import.
o don't use a simple exp owner=, imp owner=. Export table by table (in parallel, just
fire off export more then once). Import table by table (in parallel, just fire off
import more then once).
o don't use import to create the indexes -- use imp ... indexfile=temp.sql to generate a
script with the create indexes. then modify them to have nologging and parallel. Run
them in sqlplus faster.
Just some ideas. I'm sure others will come along and put their ideas here as well --
things they've done. Me -- I find doing more then one export and more then one import
the faster/easiest (when forced to use exp/imp for this)
If the "some reason" is just to rebuild objects -- I mean after you are done you end up
with exactly what you started with -- I would just use "alter table T move tablespace
foo" to rebuild the tables and "alter index I rebuild" to rebuild the indexes. No need
to ever take the data out of the database anymor.

1 comment:

Yaser said...

kya beeedu google ka sahi use kiya hai....