Re: Very URGENT! Tablespace Extent Problem w/ Import

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 - 18:59:42 CST