Re: Import Question

Kirti Deshpande once published here a rather exhuastive list of import speedups.

Here's a list of what I have used, though I think Kirti had more.

Turn off archiving. This is a big timesaver. Turn it on and make a backup when finished.

If your database is using rollback segments, shrink them all and disable all but 1 of them. This will usually avoid any failure to extend errors. Be sure to shrink and re-enable when finished.

Tweak the import parameters:

commit=n
analyze=n
indexes=n
constraints=n
buffer=67108864 - max size on OS ( windows max I think ) recordlength=65535 - max size of buffer filled before writing to db - 64k is max

Extract the DDL from the export file and create the indexes after the import.
Split into several DDL files and run in parallel. Dependent on your IO bandwidth.

HTH Jared

On 8/18/05, Fred Smith <fred_fred_1_at_hotmail.com> wrote:
>
> Hello,
> I'm going schema by schema by schema importing data from an export file.
> Version: 9.2.0.3 <http://9.2.0.3>
> OS: Solaris 5.8
>
> The export file is approx ~ 14 GB.
>
> I'm finding that even when I import the schemas (fromuser = x, touser =x)
> that only own a few packages etc., the import takes forever.
> It just sits there with "Importing X's objects into X" for 20-30 minutes,
> and then finally comes back with , import terminated successfully without
> warnings.
>
> So my question is, is it simply taking so long just because it has to
> search
> through the entire export.dmp file?? Is there anyway to tell what it is
> doing?
>
> Thank you.
> -Fred S.
>
> _________________________________________________________________
> Express yourself instantly with MSN Messenger! Download today - it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l