questions: imp/exp large dot dmp/performance

I've google'd this on a couple of occassions and now i need to really
find a complete answer.

maybe i haven't devoted enough time or i'm not using the right key
words, but i can't seem to find a clear example on the best way to do
imp/exp.

part of this may aid others as they search and/or try to improve the
performace of exp/imp.

in my situation, a straight exp creates a 1-2Gb dot DMP file.

I found these tips:

EXPORT:

    * Set the BUFFER parameter to a high value (e.g. 2M)
    * Set the RECORDLENGTH parameter to a high value (e.g. 64K)
    * Stop unnecessary applications to free-up resources for your job.
    * If you run multiple export sessions, ensure they write to
different physical disks.
    * DO NOT export to an NFS mounted filesystem. It will take forever.


IMPORT:

    * Create an indexfile so that you can create indexes AFTER you have
imported data. Do this by setting INDEXFILE to a filename and then
import. No data will be imported but a file containing index
definitions will be created. You must edit this file afterwards and
supply the passwords for the schemas on all CONNECT statements.
    * Place the file to be imported on a separate physical disk from
the oracle data files
    * Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i)
considerably in the init$SID.ora file
    * Set the LOG_BUFFER to a big value and restart oracle.
    * Stop redo log archiving if it is running (ALTER DATABASE
NOARCHIVELOG;)
    * Create a BIG tablespace with a BIG rollback segment inside. Set
all other rollback segments offline (except the SYSTEM rollback segment
of course). The rollback segment must be as big as your biggest table
(I think?)
    * Use COMMIT=N in the import parameter file if you can afford it
    * Use ANALYZE=N in the import parameter file to avoid time
consuming ANALYZE statements
    * Remember to run the indexfile previously created

I have a 1-2Gb file that would take 3 - 4 hours to import and about an
hour to export, adding the BUFFER=2097152 improved the exp.

Another tip i found said IMP is sequential and to parallelize it, but
it didn't give an example.

i'm an occasionl dba, so getting this done is a task.

i work with oracle on windows, but some of this will be common across
the board.


  /* original export commands. */

exp <username>/<password>@[EMAIL PROTECTED]
> file=e:\dump\<dump_file>.dmp
log=e:\dump\<dump_file>.log owner=<schema_owner>


first, i did a query that would parallelize the exp statements:

  /* parallelized table export commands. */

select 'exp <username>/<password>@[EMAIL PROTECTED]
> BUFFER=2097152
FILE=e:\dump\'
 ||owner||'.'||table_name||
 '.DMP LOG=e:\dump\'
 ||owner||'.'||table_name||
 '.LOG TABLES=('
 ||owner||'.'||table_name||
 ') INDEXES=N STATISTICS=NONE'
from dba_tables
where owner = '<schema_owner>'

  /* indexfile imp statement. */


imp <username>/<password>@[EMAIL PROTECTED]
> file=q:\dump\<dump_file>.DMP
log=e:\dump\<dump_file>.log indexfile=e:\dump\indexes.sql
owner=<schema_owner>


/* create an index-DDL-statements-ONLY file */


1. create a batch file(NDEXDDL.BAT), contents are:

echo "Indexes" > e:\dump\Ndex.SQL
echo off
for /f "tokens=1,2,*" %%i in (indexes.sql) do if NOT "%%i"=="REM" call
e:\dump\idx.bat "%%i %%j %%k"

:EXIT
echo on


2. create a batch file(IDX.BAT), contents are:

echo off
set clof=%1
if /I "%clof:~1,12%"=="CREATE INDEX" echo. >> e:\dump\Ndex.SQL
if /I "%clof:~1,13%"=="CREATE UNIQUE" echo. >> e:\dump\Ndex.SQL
echo %clof% >> e:\dump\Ndex.SQL
:PAUSE

:echo %clof% >>
:if %clof:~1,3%=="REM" "echo %clof% >> e:\dump\Ndex.SQL


Now, NDEX.SQL has only "CREATE INDEX" and "CREATE UNIQUE" statements.




  /* parallelized table import commands. */

select 'imp <username>/<password>@[EMAIL PROTECTED]
> INDEXES=N
FILE=e:\dump\'
 ||owner||'.'||table_name||
 '.DMP LOG=e:\dump\'
 ||owner||'.'||table_name||
 '.LOG TABLES=('
 ||owner||'.'||table_name||
 ') FROMUSER=<username> TOUSER=<destination_schema_owner> ANALYZE=N
FEEDBACK=100000 COMMIT=N'
from dba_tables
where owner = '<schema_owner>'



Now...  to make a short story long, i'm guessing...


    * run exports...
    * run imports...
    * execute the "CREATE INDEX" and "CREATE UNIQUE" statements...

some how that doesn't seem right.

each instance of imp/exp is going to consume resources.

there are a few hundred tables, maybe 12 or 15 big slow one.

the 300 instances of exp is going to bog me down, though it may be a
burst as some instances will finish in seconds.

the 300 instance so imp will do the same.