Re: Slow Import

"Cameron Abbott" <cvabbott_at_altavista.com> wrote in message news:9ru96j$5sl$1_at_bcarh8ab.ca.nortel.com...
> Hi All,
>
> Is there any way to spped up a database import? Here is the situation.
>
> I did an export of a Oracle 7.3.4 database, it took 49 minutes using the
> direct option. The database contained 274 tables and the export file is
1.5
> gigabytes. I am trying to import this data into a new database, Oracle
> 8.1.5. After 18 hours of importing it has only imported 116 tables. The
> 116th table has 8.5 million records which seems to be taking alot of time
to
> import.
>
> Is there an option like direct that I can use to speed up the import?
>
> My import command is
>
> imp user/password fromuser=user_id touser=user_id file=exp.dmp
>
>
> Thanks
>
> Cameron
>
>

The first thing you should do is add
buffer=8192000 /* the default is a measly 32k */ and commit=y /* this commits once per 8192000 bytes, the default is to commit per table*/

I doubt whether that will resolve it completely. The usual strategy is
imp user/password fromuser=user_id touser=user_id file=exp.dmp indexfile=<anyfile>.sql
imp user/password fromuser=user_id touser=user_id file=exp.dmp indexes=n sqlplus to_user/to_passwd
@<anyfile>.sql

If you are smart you edit anyfile.sql and add the nologging clause to all create index statements.
Note: the PKs and UKs will still not be part of <anyfile>.sql, they will still come with the table.

As to the keywords I have been using you can see them using imp help=y and have them explained in the Oracle Server Utilities Manual.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address