Re: Very URGENT! Tablespace Extent Problem w/ Import

From: Howard J. Rogers <howardjr_at_www.com>
Date: Tue, 23 Jan 2001 13:17:21 +1100
Message-ID: <3a6ce98a_at_news.iprimus.com.au>


Whilst in aggregate your tablespaces are identical between the two platforms, you'd best remember that extents cannot cross a physical file boundary (ie, you can't start an extent in one file and finish it another).

So you're on a hiding to nothing if your files are 2Gb big, and you ask for an initial extent of 3Gb, even though the tablespace may have 12Gb of free space in aggregate.

You are, indeed, getting error messages to this effect: in the first bunch of errors, you've got an initial extent size of 2147483646, which my poor maths suggests is 2Gb. Now, you may have 2Gb datafiles, but a few bytes from each file is taken up by house-keeping and header block information. So I bet a 2Gb file doesn't *quite* have 2Gb of useable space.

Hence the "unable to create initial extent" error: Oracle can't create a 1.99Gb extent in one file, and a 0.01Gb extent in another.

Cure: hand-create the relevant tables with slightly smaller initial extents, then re-run import with ignore=y (ie, ignore the error that results from realising the table already exists, and just get on with the insert part of the job).

Regards
HJR "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 Tue Jan 23 2001 - 03:17:21 CET

Original text of this message