Re: IMPORT error, initial extents cannot be created (HELP!!!)

Others have told you correctly that your current setup will not allow the creation of a 2g initial extent.

My advice would be

1. Reexport using compress=N.
2. Re import. You should find it 'magically' works with your current datafiles.

In addition I have some further observations that I would hope help you a bit.

Never ever ever export a database with the default clause compress=y. It is this that stores the initial extent <some bloody ridiculous number> caluse in the dump file. This is probably one of the top 5 terrible defaults in Oracle. (at least up to 8i don't know about 9.)

Use Locally managed tablespaces with a uniform extent size (not autoallocate). This will mean you never have to defrag again. Also if you do forget to use compress=n you shouldn't be stuffed in this way because oracle will just silently rewrite the storage clause.

Use uniform datafile sizes and make them no more than 2gb in size. This means that you can rearrange datafiles across disks easily and logically. 3 datafiles all of different sizes for one tablespace seems wrong.

--
Niall Litchfield
Oracle DBA
Audit Commission UK


"crumedgeon" <zimsbait_at_hotmail.com> wrote in message
news:U4tg7.89888$ai2.6321225_at_bin2.nnrp.aus1.giganews.com...


> Oracle wants a 2G initial extent (as shown in your import snippet). Your

> largest possible extent is 1.5G (as per your create tablespace command

> at the bottom). If there is something else in that tablespace, export it

also,


> drop the tablespace, delete the OS files (via os commands) and recreate

> the tablespace as a single 3.5G file (versus 3 smaller files).  Than

import


> all your tablespace objects.

>

>

> "JK Yao" <jkyf0131_at_hotmail.com> wrote in message

> news:93f65984.0108210018.6e4ed7ea_at_posting.google.com...

> > dear all,

> >

> >

> > Oracle8.1.6

> > OS: Solaris5.7

> > Import Utility

> >

> > full=y rows=n constraints=n indexes=n

> >

> > When I imported the object, there is a error showing that I am not be

> > able to create a table.  What caouse this?  The tablespace is big

> > enough.  Any though?  Following is the error mesg and my script of

> > creation of the tablespace.

> >

> > Thanks in advance,

> > JK

> > ~~~~~~~~~~~~~~~

> >

> > ****** error codes *********

> > IMP-00017: following statement failed with ORACLE error 1658:

> >  "CREATE TABLE "PART_COST_BUCKET_HISTORY_TAB" ("PART_NO" VARCHAR2(25)

> > NOT NUL" ...)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

> > STORAGE(INITIAL 2044334080 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS

> > 2147483645 PCTINCREASE"

> >  " 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "MPC_DATA""

> > IMP-00003: ORACLE error 1658 encountered

> > ORA-01658: unable to create INITIAL extent for segment in tablespace

> > MPC_DATA

> >

> > ****************

> >

> > ************* tablespace setting *************

> > CREATE TABLESPACE mpc_data DATAFILE

> >   '/data2/P8/mpc_data.dbf' size 1500M,

> >   '/data2/P8/mpc_data1.dbf' size 1450M,

> >   '/data2/P8/mpc_data2.dbf' size 500M

> > DEFAULT STORAGE

> > ( INITIAL 2M NEXT 2M MINEXTENTS 1

> >   MAXEXTENTS 249 PCTINCREASE 0)

> > ONLINE;

> > ****************

> >

>

>