ORA-01658: unable to create INITIAL extent

[raf]

hi,
I've exported my instance:

EXP SYSTEM/MANAGER@INSTANCE FULL=Y FILE=C:\EXP_FILE.DMP LOG=C:\ERROR_EXP.LOG

after I've created new tablespace and new user:
CREATE TABLESPACE MAX DATAFILE 'c:\MAX01.dbf' SIZE 3800M REUSE DEFAULT STORAGE
(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0) ONLINE;

CREATE USER MAX IDENTIFIED BY MAX DEFAULT TABLESPACE MAX TEMPORARY TABLESPACE TEMP;

finally I've imported tom's objects in MAX:

IMP SYSTEM/MANAGER@INSTANCE FROMUSER=TOM TOUSER=MAX FILE=C:\EXP_FILE.DMP LOG=C:\ERROR_IMP.log

but in some tables I get these errors:
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "AREA" ("OFL" VARCHAR2(4), "OBL" VARCHAR2(32), "OSITE" V"
"ARCHAR2(32), "OLS" VARCHAR2(32), "OAREA" NUMBER, "DV_ID" VARCHAR2(32), "DP_"
"ID" VARCHAR2(32), "ODISMESSO" VARCHAR2(2), "OTYPE" VARCHAR2(64), "OCAT" VAR"
"CHAR2(64), "OOCCUP" NUMBER, "SFL" VARCHAR2(32), "SBL" VARCHAR2(32), "SSITE""
" VARCHAR2(32), "PRORATE" VARCHAR2(8), "STYPE" VARCHAR2(64), "SCAT" VARCHAR2"
"(64), "SAREA" NUMBER, "SOCCUP" NUMBER, "AREA_SERV" NUMBER) PCTFREE 10 PCTU"
"SED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 314572800 NEXT 52428"
"800 MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS "
"1 BUFFER_POOL DEFAULT) TABLESPACE "TOM""
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace TOM


I tried to increase tablespace MAX but I get always these errors:

How can I import without errors?

Thanks
Raf

[jovery]

Even though you have created a seperate tablespace for MAX the object will still be created in it's original tablespace if it exists, in this case it will try and create the object in the TOM tablespace.

This is why increasing the size of the MAX tablespace has no effect.

Look at the error

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

Remove all quotas on TOM tablespace from user MAX or take the tablespace offline

Regards

[raf]

I've increase tablespace TOM from 2.7Gb to 3.8Gb and after I've again FULL exported.
When I import again fromuser=tom touser=max...........

table AREA import correctly but I get same error on others tables:

IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace TOM
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "Z_C" ("CLIENTE" VARCHAR2(50)) PCTFREE 10 PCTUSED 40 IN"
"ITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 52428800 NEXT 52428800 MINEXT"
"ENTS 1 MAXEXTENTS 1000 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
"OOL DEFAULT) TABLESPACE "TOM""
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace TOM IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "Z_IG" ("RM_FOCA_PM" VARCHAR2(4) NOT NULL ENABLE, "DESCR" "
"VARCHAR2(80), "IMP_EL_MECC_ANTIN_ID" VARCHAR2(4), "IMP_ELEV_ID" VARCHAR2(4)"
", "TUT_AZ_ID" VARCHAR2(4), "OP_CIV_ID" VARCHAR2(4), "AREE_VERDI_ID" VARCHAR"
"2(4)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIA"
"L 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0 FREELIS"
"TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TOM""
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace TOM


Jovery, what tablespace I must take offline? Tablespace TOM?

Remember that Tablespace TOM is in another instance.
I exported the dmp from instance 'FIRE' and imported to instance 'LUKE' touser=MAX

Raf

[raf]

sorry,
Could I resolve my problem if I create a LOCALLY MANAGED TABLESPACE??


Raf

[jovery]

Do you have a TOM tablespace in the new instance?

[anandkl]

You can do an alternate

do an import where in the all the DDL would go into a file, edit the file and run the same into the new schema.I know this would be a tedious task, i am just giving you an option...

imp username/password INDEXFILE=

where file in indexfile parameter would contain all the DDL's, edit it to include the new tablespace and execute the same in the new schema

regards
anandkl

[raf]

Originally posted by jovery
Do you have a TOM tablespace in the new instance?

yes in new instance there are 2 tablespaces (TOM, MAX)

may be I must increase tablespace TOM of new instance?

Now I am trying to export with parameter COMPRESS=N

Raf

[jovery]

The import is complaining about the TOM tablespace in the new instance, as I said before if the original tablespace exists the import utility will attempt to create the objects there.

Simply remove MAX's quota on the TOM tablespace in the new instance or take the tablespace offline in the new instance.

[raf]

I tried 2 solution:

1) I've taken the tablespace TOM offline in the new instance, but when I import fromuser=TOM touser=MAX.............
I get error because not exist tablespace TOM

2)I removed MAX's quota on the TOM tablespace in the new instance.

ALTER USER MAX QUOTA unlimited ON TOM;

but when I import I get alway the same error:

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

I know That I must encrease tablespace TOM for avoid this error, but I must create 6 new users in 6 different tablespaces and I'd to increase tablespace TOM too much (how many GB?).

how can I import schema to a different tablespace?

Thanks
Raf