Re: Very URGENT! Tablespace Extent Problem w/ Import

Dave, have you ever edited an expdat.dmp file, and then proceeded to import successfully? I only ask because the data that is inserted by all those "insert into" statements is binary encoded, and text-editing the dump file is almost guaranteed to stuff that encoding up (and hence lose your data).

The dump file most certainly isn't a text file, and should never be treated as such. I guess you might get away with it once in a while, but I wouldn't bet on it as a long-termer.

Regards
HJR "Dave Haas" <davehaas_at_hotmail.com> wrote in message news:94ikvi$v69$1_at_news3.cadvision.com...
> Hi Knut.
>
> The problem lies in the following ...
>
> IMP-00017: following statement failed with ORACLE error 1658:
> ALTER TABLE "ATTRIBUTEDEFINITION" ADD PRIMARY KEY ("OWNERID","NAME")
 USING
> INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 2147483646
> <--------------------
>
> The INITIAL 2147483646 is trying to create an initial extent of 2 gig's.
 On
> Linux, no can do (due to header blocks and such).
>
> The FIX:
>
> Edit the dump file (it's just a text file with lots of create table, alter
> table and insert into table stmts ...) an change the number from
 2147483646
> to something smaller (probably a couple of blocks less than the 2 gig file
> size limit in your case). Try a little less than half of that.
>
> After that it'll work fine.
>
> The problem lies in the fact that it's trying to reserve a 2 gig chunk of
> space for the index for the "attributedefinition" table, but it can't find
 2
> gigs worth of contiguous space. So, tell it to try and grab less
 contiguous
> space, and you're off to the races :)
>
> HTH,
>
> Dave Haas
>
>
> "Knut Talman" <knut_at_mytoys.de> wrote in message
> news:3A6CD1B0.11D1B70E_at_mytoys.de...
> > Hi Folks,
> >
> > I have a very urgent problem. I need to import a dump from a
> > Sun Solaris machine running 8.1.6. It is a rather large database
> > havin tables with up to 30.000.000 records. The tablespace storing
> > the large tables consists of 5 datafiles, each is min 1 GB, largest
> > file has 4GB.
> > The database (8.1.6) where the dump has to be imported runs on Linux.
> > The tablespaces have the same names, except the datafiles are only
> > 2GB large, because of the file system limits, but there are 7 datafiles,
> > so the summarized size is the same. The same is true for the index
> > tablespace.
> > When I try to import the file I get the following error:
> >
> > . . importing table "ATTRIBUTEDEFINITION" 27953130 rows
> > imported
> > IMP-00017: following statement failed with ORACLE error 1658:
> > "ALTER TABLE "ATTRIBUTEDEFINITION" ADD PRIMARY KEY ("OWNERID","NAME")
> > USING"
> > " INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 2147483646
> > NEXT 2"
> > "4453120 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 1 FREELISTS 1
> > FREELI"
> > "ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ETS_INDX" ENABLE"
> > IMP-00003: ORACLE error 1658 encountered
> > ORA-01658: unable to create INITIAL extent for segment in tablespace
> > ETS_INDX
> > IMP-00017: following statement failed with ORACLE error 1658:
> > "CREATE TABLE "ATTRIBUTEVALUE" ("OWNERID" VARCHAR2(28) NOT NULL ENABLE,
> > "NAM"
> > "E" VARCHAR2(256) NOT NULL ENABLE, "LOCALEID" VARCHAR2(7) NOT NULL
> > ENABLE, ""
> > "LOCALIZEDFLAG" NUMBER(*,0), "TYPE" NUMBER(*,0) NOT NULL ENABLE,
> > "INTVALUE" "
> > "NUMBER(*,0), "DOUBLEVALUE" FLOAT(126), "STRINGVALUE" VARCHAR2(4000),
> > "OCA" "
> > "NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
> > STORAGE"
> > "(INITIAL 2147483646 NEXT 24952832 MINEXTENTS 1 MAXEXTENTS 2147483645
> > PCTINC"
> > "REASE 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
> > "ETS_"
> > "USERS""
> > IMP-00003: ORACLE error 1658 encountered
> > ORA-01658: unable to create INITIAL extent for segment in tablespace
> > ETS_USERS
> > . . importing table "BABYTABLEPARTICIPANT" 12 rows
> > imported
> >
> > After getting the error I checked the tablespaces using OEM. It shows,
> > that 2 datafiles are 99.99 % full, but the rest of the datafiles is
> > completely empty!
> > After adding some additional datafiles and always getting the same error
> > I am somewhat helpless.
> > Any idea?
> >
> > Regards,
> >
> > Knut
> >
> > PS: I would really appreciate a fast answer because the new database has
> > to be
> > running in 5 hours...
>
>
Received on Mon Jan 22 2001 - 21:20:26 CST