Re: A Large DB Import Problem

From: <pihlab_at_cbr.hhcs.gov.au>
Date: 10 Feb 94 13:08:52 +1100
Message-ID: <1994Feb10.130852.1_at_cbr.hhcs.gov.au>


In article <CKqs66.LzF_at_shakti.ncst.ernet.in>, hemant_at_shakti.ncst.ernet.in (STOCKHOLDING CORPORATION OF INDIA LTD.) writes:
> While recreating an Oracle 6.0.33 Database on DG/UX 5.4.2
> ================================================================================
>
>
> We tried importing a large table with the following import command :
> ------------------------------------------------------------------
>
> impst system/manager fromuser=XYZUSER touser=XYZUSER \
> buffer=40960000 file=LARGE_EXPORT_FILE grants=N \
> ignore=N tables=LARGE_TABLE indexes=N rows=Y

An important parameter you may have missed is COMMIT=Y

I have not used a buffer larger than 4 Meg so I think 40Meg is a lot of overkill given your row size.

Does your process/machine have enough memory to do this size buffer without paging. If it pages then your wasting resources and probably slowing the work down.

>
> ================================================================================
>
> The output of the above command was :
> -----------------------------------
>
>
> Import: Version 6.0.33.2.1 - Production on <date and time deleted>
>
> Copyright (c) Oracle Corporation 1979, 1989. All rights reserved.
>
>
> Connected to: ORACLE RDBMS V6.0.33.2.1, transaction processing option - Production
> PL/SQL V1.0.33.1.1 - Production
>
> Export file created by EXPORT:V06.00.33
> . importing XYZUSER's objects into XYZUSER
> . . importing table "LARGE_TABLE"
> IMP-00020: column (size 705) in export file too large for column buffer (size 22)
> IMP-00028: partial import of previous table rollbacked: 491505 rows rollbacked

From the V7 Error messages and Codes manual it says to increase your buffer size by 10000 at a time. Use this step-by-step approach because (here's the killer) a buffer size that is TOO large may cause a similar problem.

Try a smaller buffer size. I doubt anything larger than 1-4 Meg would give you any benefit. There might be a correlation between SGA buffered blocks and the maximum size for the IMP BUFFER size. Just guess work.

> Import terminated successfully.
> ================================================================================
>
>
> A Rollback Segment of initial 100M next 50M (in a tablespace with 2000M free
> was in use).
> ================================================================================
>
>
> The table has NO long columns.
> A description of the table is :
>
> column_1 char(11) not null,
> column_2 char(6) not null,
> column_3 number(7,0) not null,
> column_4 number(12,0) ,
> column_1 number(12,0)
> ) pctfree 5 pctused 95 tablespace large_tbs
> storage (initial 942080K next 40960K minextents 1 maxextents 999 pctincrease 0);
>
> ================================================================================
> The table actually has 22047804 rows
>
> The file (which includes other tables exported) is of size 1926544384 bytes.
> This table is the first in the export file. There were no errors in the export.
> ================================================================================
>
> The database data-tablespace and tablespace for rollback segment are on raw
> devices.
>
> What is the cause ? Is it a known bug ?
> What action can be taken ?

It may be a combination of your not doing a COMMIT=Y and using too large a buffer size.

Try it using COMMIT=Y and if that doesn't work then try COMMIT=Y AND reducing the size of your buffer.

Let me know how you go.

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************
Received on Thu Feb 10 1994 - 03:08:52 CET

Original text of this message