Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Very URGENT! Tablespace Extent Problem w/ Import
In article <3A6CD1B0.11D1B70E_at_mytoys.de>,
Knut Talman <knut_at_mytoys.de> wrote:
> 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...
>
Get thee to the manuals.
Possible solutions:
Create the table in the new tablespace prior to importing. Overriding Storage Parameters
Before using the Import utility to import data, you may want to create large tables with different storage parameters. If so, you must specify IGNORE=Y on the command line or in the parameter file.
You may wish to only import the one offending file ( another parameter)
import parameters:
INDEXES Default: Y
Specifies whether or not to import indexes. System-generated indexes such as LOB indexes, OID indexes, or unique constraint indexes are recreated by Import regardless of the setting of this parameter. You can postpone all user-generated index creation until after Import completes, by specifying INDEXES=N.
If indexes for the target table already exist at the time of the import, Import performs index maintenance when data is inserted into the table.
INDEXFILE Default: none
Specifies a file to receive index-creation commands.
When this parameter is specified, index-creation commands for the
requested mode are extracted and written to the specified file, rather
than used to create indexes in the database. No database objects are
imported.
If the Import parameter CONSTRAINTS is set to Y, Import also writes
table constraints to the index file. The file can then be edited (for
example, to change storage parameters) and used as a SQL script to
create the indexes.
-- Michael Krolewski Rosetta Inpharmatics mkrolewski_at_rii.com Usual disclaimers Sent via Deja.com http://www.deja.com/Received on Mon Jan 22 2001 - 19:19:15 CST