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: ORA-01658: unable to create INITIAL extent during import of a db

Re: ORA-01658: unable to create INITIAL extent during import of a db

From: <fitzjarrell_at_cox.net>
Date: 19 Jun 2005 05:22:59 -0700
Message-ID: <1119183779.343944.241170@g14g2000cwa.googlegroups.com>

Alessandro Lagostena wrote:
> Hello gents a have a proble when i try to export a dump from one db to a new
>
> More in detail i have 2 istance of Oracle 8.1.6 on solarsi 2.7 (both on
> the same server)
>
> Now i whant migrate the db from istance A) to istance B)
>
>
> istance B) have sufficient space for contain all object of DB A) ( table
> index blobs)
>
> I like export from A) with compress=y (as default) just to import data
> in compress and remove extents and chian.
>
> In this condition when i try to import the DB on B) i obtain the follow
> errors :
>
>
> IMP-00017: following statement failed with ORACLE error 1658:
> "CREATE TABLE "SELECTORLIST" ("CLASSNAMEKEYA4" VARCHAR2(200), "IDA3A4"
> NUMBE"
> "R, "POLICYTYPE" VARCHAR2(30) NOT NULL ENABLE, "SELECTORLISTTYPE"
> VARCHAR2(3"
> "0) NOT NULL ENABLE, "SELECTORS" BLOB, "CREATESTAMPA2" DATE,
> "MODIFYSTAMPA2""
> " DATE, "CLASSNAMEA2A2" VARCHAR2(200), "IDA2A2" NUMBER,
> "UPDATECOUNTA2" NUMB"
> "ER, "UPDATESTAMPA2" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
> 255 LO"
> "GGING STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 4096
> PCTIN"
> "CREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> TABLESPACE "USE"
> "RS" LOB ("SELECTORS") STORE AS (TABLESPACE "BLOBS" ENABLE STORAGE IN
> ROW C"
> "HUNK 32768 PCTVERSION 10 NOCACHE STORAGE(INITIAL 1048576 NEXT 212992
> MINEX"
> "TENTS 1 MAXEXTENTS 1017 PCTINCREASE 1 FREELISTS 1 FREELIST GROUPS 1
> BUFFER_"
> "POOL DEFAULT))"
> IMP-00003: ORACLE error 1658 encountered
>
>
>
> Do you have any sudgestion for solve this problem or the only way is to
> export with compress=n than to create table before import ?
>
>
> Thanks for the support
>
> Regards LAGO

The compress=y option does nothing to alleviate chained rows, period. A chained row is a row larger than the database block size. Nothing can correct that short of recreating your database with a larger block size or using a tablespace (in 9i and later releases) with a larger block size. and, compress=y does nothing, really, for migrated rows, as it doesn't change the conditions which created them in the first place, incorrect PCTFREE and PCTUSED values on the table or tablespace.  However it may appear to correct the issue as no migrated rows are present after an import. Update one of those rows and you'l likely migrate that rows to another block, and your problem returns.

Chained rows NEVER fit into a datablock, hence they are chained. And exp/imp will NEVER fix them without intervention (recreating the database, importing into a tablespace with a larger block size by pre-creating the tables and using the ignore=y option).

Migrated rows CAN fit into a data block provided the PCTFREE and PCTUSED storage parameters are set properly. And, exp/imp does nothing with these settings; you need to change those manually.

David Fitzjarrell Received on Sun Jun 19 2005 - 07:22:59 CDT

Original text of this message

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