topic Re: Oracle Export in Operating System - HP-UX

Oracle Export

Nobody's Hero — Tue, 13 May 2003 14:02:28 GMT

We have an oracle database that is about 300 Gbytes. Our export runs a fairly long time and the import time is about 24 hours. Has anyone ever heard of splitting the export into 2 or 3 different processes to speed things up? If so, do you know how this could be accomplished?

10x
RPM

Re: Oracle Export

Ian Lochray — Tue, 13 May 2003 14:37:35 GMT

The only way I could think of to do this would be to have several seperate export processes. The first one would export the whole schema but with rows=n so that no data is exported. This will give you an export of the views, synonyms etc. The other exports would each export part of the schema by specifying a pre-determined list of tables with the "tables=" option on the exp command.
To restore you could import the schema export file and then import the others with "ignore=y" to ignore the fact that the tables already exist.

Re: Oracle Export

R. Allan Hicks — Wed, 14 May 2003 01:23:33 GMT

Are you using imp and export for backup? If so you might want to look at Tom Kyte's book Oracle Expert One on One. Imp and exp are good for migrating databases, but they leave a lot to be desired as backup utilities.

Re: Oracle Export

Yogeeraj_1 — Wed, 14 May 2003 04:58:30 GMT

hi,

in general large exports rules are:
use the filesize parameter
export smaller pieces (using tables= or owner=)
export to a device that does not support seeking

Hopefully, this does not form part of you main backup strategy!

Regards
Yogeeraj

Re: Oracle Export

Wim Korten — Wed, 14 May 2003 09:00:47 GMT

Hi,

Our settings for export are:
# For conventional path exports:
Direct = N
Buffer = 10485760

# For direct path exports:
Direct = Y
Recordlength = 65535

Direct export is VERY fast. If you don't use it now, you could try it. For ins and outs, check the manual.

For import, set your buffer large:
Buffer = 10485760
There is no 'direct' import.

Re: Oracle Export

Massimo Bianchi — Thu, 15 May 2003 12:54:17 GMT

Hi,
just a couple of notes.

You can use direct=y only if you have no chaset conversion.

If you will import the data on another db or if you have set some env variables, like NLS_LANG, you can have some surprises.

Another important issue is the import: if you use parallel import (many imp) you will have to pay attentio to temp tablespace (for creating indexes) and to rollback segments.

If you dare, you can also try in import commit=y, that can improve performance.

With this parameter, you will have a commit at the end of the table, not after each row.
DRAWBACK: you must have a rollback segment large enough to store the ENTIRE TABLE. And this might be space consumig...

HTH
Massimo