Re: Oracle 10g imp woes

Comments embedded.

Andrew Tyson wrote:
> Hi,
>
> I am having some difficulties doing a full import of a database from a
> 10.1.0.2.0 instance running on Win XP to a 10.1.0.2.0 instance running
> on Solaris 10.
>
> The tablespaces have been precreated using the same scripts in each
> case, however during the import I get the following error;
>
> . . importing table "NBI_PRT_FACTS" 3964046 rows imported
> IMP-00017: following statement failed with ORACLE error 1652:
> "CREATE INDEX "NBI_PRT_FACTS_N3" ON "NBI_PRT_FACTS" ("PRJ_OBJECT_ID" ,
> "FACT"
> "_DATE" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 262144
> FREELIS"
> "TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_SMALL"
> LOGGIN"
> "G"
>
> Thereafter all subsequent table/entity creation fails with the following;
>
> IMP-00003: ORACLE error 1658 encountered
> ORA-01658: unable to create INITIAL extent for segment
>
> This import works fine on Win XP, so clearly there is something that is
> platform specific wrt the import. For what it's worth the export command
> was issued thus;
>
> > exp system/password full=y file=dump.dmp log=dump.log
>

Which is the source of your problems. By default compress=y for exp, and, as such, each object is now set to load into a single, possibly huge, extent rather than the many extents it occupies in the source database. I'm not surprised you're seeing that error, as the extent Oracle is attempting to create is far larger than any contiguous block of free space in your tablespace. To eliminate this default behaviour add 'compress=n' to your exp command:

exp system/password full=y file=dump.dmp log=dump.log compress=n

> The corresponding import was ;
>
> > imp system/password full=y file=dump.dmp statistics=safe log=dump.log

This is irrelevant, as the damage was done with the exp parameters you've used to create the problematic .dmp file.

>
> The statistics option was set because of warnings that were generated
> during the export.
>
> I would appreciate any pointers that anyone might have.
>
> Thanks and regards,
> Andrew

Correct your exp command-line parameters to include, as I've stated above and illustrated, 'compress=n'. You should find such errors disappear, provided you have enough contiguous space to create the initial extents declared when the tables were originally created. And provided you haven't dropped the source tables and replaced them from this extent-compressed import. If you've done that you have all of your tables with one extent each, and you'll need to add datafiles or resize the datafiles you currently have to accept the exceptionally large extents imp is trying to create.

David Fitzjarrell Received on Sun Jun 26 2005 - 23:32:39 CDT