Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Very URGENT! Tablespace Extent Problem w/ Import

Re: Very URGENT! Tablespace Extent Problem w/ Import

From: Brian McCarthy <Brian.McCarthy_at_oracle.com>
Date: Mon, 12 Feb 2001 11:16:59 -0500
Message-ID: <3A880C7B.1700E348@oracle.com>

Just to set the record straight.

An export dump file is not just ASCII text. It has binary header information so import knows how to deal with the data. Also, depending on the character set setting, it may or may not be editable at all.

If you are transferring a dump file from one system to another using ftp, make sure you are in binary mode.

As for getting away with it (as someone has posted) it all depends on what utility is used to open it up. Some editors may put linewraps in or convert non-ascii characters behind the user's back (in a prior professional life, I worked on editors) - those are the dump files that are going to fail to work.

Officially, don't edit dump files.

Brian

Dave Haas wrote:
>
> 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 Feb 12 2001 - 10:16:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US