Problem while importing

Hi,
 When I import a dump, which is of size 28 GB I get the following error. I have imported it thrice. I'm getting either in itial extent or minextent or temp segment error. This is really urgent.

(1)

IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE TABLE "LXRO_DC96E183" ("LXOID" NUMBER(*,0), "LXFLAGS" NUMBER(*,0), ""
 "LXTYPE" NUMBER(*,0), "LXFROMLAT" NUMBER(*,0), "LXFROMID" NUMBER(*,0), "LXTO"
 "LAT" NUMBER(*,0), "LXTOID" NUMBER(*,0), "LXCRDATE" DATE, "LXRELRUL" NUMBER("
 "*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIA"
 "L 21012480 FREELISTS 1 FREELIST GROUPS 1)                                "
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 7 in tablespace METSO_TBS
. . importing table                "LXRO_DD0E3175"          1 rows imported
. . importing table                "LXRO_DE93EC06"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table METSO.LXRO_DE93EC06 by 128 in tablespace METSO_TBS
IMP-00028: partial import of previous table rolled back: 80063 rows rolled back
. . importing table                "LXRO_DF038E1C"          0 rows imported
. . importing table                "LXRO_DF20A77E"       2339 rows imported
. . importing table                "LXRO_DF3C99AE"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table METSO.LXRO_DF3C99AE by 8 in tablespace METSO_TBS
IMP-00028: partial import of previous table rolled back: 6239 rows rolled back
IMP-00017: following statement failed with ORACLE error 1658:
 "CREATE INDEX "LXRO_DF3C99AE_LXTOLAT_INDEX" ON "LXRO_DF3C99AE" ("LXTOLAT" , "
 ""LXTOID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELI"
 "STS 1 FREELIST GROUPS 1)                                  LOGGING"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DF3C99AE_LXTOLAT_INDEX"',NULL"
 ",NULL,NULL,60693.1666666667,223,15740,1,2,42295,1,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DF3C99AE_LXTOLAT_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1658:
 "CREATE INDEX "LXRO_DF3C99AE_LXOID_INDEX" ON "LXRO_DF3C99AE" ("LXOID" )  PCT"
 "FREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST "
 "GROUPS 1)                                  LOGGING"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DF3C99AE_LXOID_INDEX"',NULL,N"
 "ULL,NULL,60837.5,157,60837,1,1,60680,1,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DF3C99AE_LXOID_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1658:
 "CREATE INDEX "LXRO_DF3C99AE_LXTYPE_INDEX" ON "LXRO_DF3C99AE" ("LXTYPE" )  P"
 "CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIS"
 "T GROUPS 1)                                  LOGGING"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DF3C99AE_LXTYPE_INDEX"',NULL,"
 "NULL,NULL,55180,155,4,38,372,1491,1,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DF3C99AE_LXTYPE_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1658:
 "CREATE INDEX "LXRO_DF3C99AE_LXFROMLAT_INDEX" ON "LXRO_DF3C99AE" ("LXFROMLAT"
 "" , "LXFROMID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "
 "FREELISTS 1 FREELIST GROUPS 1)                                  LOGGING"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:


(2)

IMP-00017: following statement failed with ORACLE error 1652:
 "CREATE INDEX "LXRO_DB077BFB_LXFROMLAT_INDEX" ON "LXRO_DB077BFB" ("LXFROMLAT"
 "" , "LXFROMID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "
 "FREELISTS 1 FREELIST GROUPS 1)                                       LOGGIN"
 "G"
IMP-00003: ORACLE error 1652 encountered
ORA-01652: unable to extend temp segment by 128 in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DB077BFB_LXFROMLAT_INDEX"',NU"
 "LL,NULL,NULL,610724.833333333,2246,59275,1,1,91711,2,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DB077BFB_LXFROMLAT_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
. . importing table                "LXRO_DBF29DE3"          0 rows imported
IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE TABLE "LXRO_DC96E183" ("LXOID" NUMBER(*,0), "LXFLAGS" NUMBER(*,0), ""
 "LXTYPE" NUMBER(*,0), "LXFROMLAT" NUMBER(*,0), "LXFROMID" NUMBER(*,0), "LXTO"
 "LAT" NUMBER(*,0), "LXTOID" NUMBER(*,0), "LXCRDATE" DATE, "LXRELRUL" NUMBER("
 "*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIA"
 "L 21012480 FREELISTS 1 FREELIST GROUPS 1)                                "
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 17 in tablespace METSO_TBS
. . importing table                "LXRO_DD0E3175"          1 rows imported
. . importing table                "LXRO_DE93EC06"     192174 rows imported
IMP-00017: following statement failed with ORACLE error 1652:
 "CREATE INDEX "LXRO_DE93EC06_LXTOLAT_INDEX" ON "LXRO_DE93EC06" ("LXTOLAT" , "
 ""LXTOID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELI"
 "STS 1 FREELIST GROUPS 1)                                        LOGGING"
IMP-00003: ORACLE error 1652 encountered
ORA-01652: unable to extend temp segment by 128 in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DE93EC06_LXTOLAT_INDEX"',NULL"
 ",NULL,NULL,194323.055555556,710,38911,1,3,144228,2,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DE93EC06_LXTOLAT_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1652:
 "CREATE INDEX "LXRO_DE93EC06_LXOID_INDEX" ON "LXRO_DE93EC06" ("LXOID" )  PCT"
 "FREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST "
 "GROUPS 1)                                        LOGGING"
IMP-00003: ORACLE error 1652 encountered
ORA-01652: unable to extend temp segment by 128 in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DE93EC06_LXOID_INDEX"',NULL,N"
 "ULL,NULL,192060,495,192060,1,1,191961,1,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DE93EC06_LXOID_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1652:
 "CREATE INDEX "LXRO_DE93EC06_LXTYPE_INDEX" ON "LXRO_DE93EC06" ("LXTYPE" )  P"
 "CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIS"
 "T GROUPS 1)                                        LOGGING"
IMP-00003: ORACLE error 1652 encountered
ORA-01652: unable to extend temp segment by 128 in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DE93EC06_LXTYPE_INDEX"',NULL,"
 "NULL,NULL,193437.115384615,489,6,81,736,4419,2,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DE93EC06_LXTYPE_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1652:
 "CREATE INDEX "LXRO_DE93EC06_LXFROMLAT_INDEX" ON "LXRO_DE93EC06" ("LXFROMLAT"
 "" , "LXFROMID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "
 "FREELISTS 1 FREELIST GROUPS 1)                                        LOGGI"
 "NG"
IMP-00003: ORACLE error 1652 encountered
ORA-01652: unable to extend temp segment by 128 in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DE93EC06_LXFROMLAT_INDEX"',NU"
 "LL,NULL,NULL,189055.777777778,712,31960,1,1,41493,2,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index LXRO_DE93EC06_LXFROMLAT_INDEX: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3678
ORA-06512: at "SYS.DBMS_STATS", line 3783
ORA-06512: at line 1
. . importing table                "LXRO_DF038E1C"          0 rows imported
. . importing table                "LXRO_DF20A77E"       2339 rows imported
. . importing table                "LXRO_DF3C99AE"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table METSO.LXRO_DF3C99AE by 128 in tablespace METSO_TBS
IMP-00028: partial import of previous table rolled back: 12630 rows rolled back
. . importing table                "LXRO_DF9B7015"        621 rows imported
. . importing table                "LXRO_DFC688A9"          0 rows imported
IMP-00017: following statement failed with ORACLE error 1658:
 "CREATE INDEX "LXRO_DFC688A9_LXOID_INDEX" ON "LXRO_DFC688A9" ("LXOID" )  PCT"
 "FREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST "
 "GROUPS 1)                                    LOGGING"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace METSO_TBS
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"LXRO_DFC688A9_LXOID_INDEX"',NULL,N"
 "ULL,NULL,0,0,0,0,0,0,0,0); END;"
IMP-00003: ORACLE error 20000 encountered


I have given a extract of the errors which had come when I tried on different days.

I'm getting these errors after 18 hours of import.

Your help is greatly appreciated.

Thanks & regards,
Sathya
LVL 3
sathya_sAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MetanilCommented:
Any information on your system.. like your Oracle Version, OS Version??

In most of the situation the above error occurs when oracle can't allocate space, which means try check your tablespace size(data file will be more specific). Check the maximum file size that your OS can support. If your OS doesn't support big files, then try creating multiple data files for a tablespace.

Make sure you have 'AUTOEXTEND ON' option while creating tablespace.

Metanil

sathya_sAuthor Commented:
My Oracle Version is 9.2.0.4 on RHEL 3 (Red Hat Enterprise Linux).
I have set 'AUTOEXTEND ON' for Datafile. How to find the maximum file zise my OS supports?

Thanks,
Sathya
schwertnerCommented:
Linux/Unix in their 32 bit versions do not support filis greater then 2 GB.
So if your tablespaces are big the workaround is to split the tablespace into several files. You can create these datafiles in advance and they will be filled when other are full. Also clean alert.log file and the listener.log file that grow very fast and can prevent Oracle to work fine.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sathya_sAuthor Commented:
My file is around 32 GB, when it failed. Till that it was fine. My dump file itself is 28 GB after unzipping.
If it doesn't support more than that it should have failed while unzipping itself. It is 32 bit version.

MetanilCommented:
OK here is the clue

1. First create multiple data files for tablespace METSO_TBS.

2. extract the ddl from the dmp file (use indexfile=foo.sql
and foo.sql will have it) and precreate the objects WITHOUT storage clause.
( imp user/oracle file=dd.dmp indexfile=foo.sql)

ex: "CREATE TABLE "LXRO_DC96E183" ("LXOID" NUMBER(*,0), "LXFLAGS" NUMBER(*,0), ""
 "LXTYPE" NUMBER(*,0), "LXFROMLAT" NUMBER(*,0), "LXFROMID" NUMBER(*,0), "LXTO"
 "LAT" NUMBER(*,0), "LXTOID" NUMBER(*,0), "LXCRDATE" DATE, "LXRELRUL" NUMBER("
 "*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIA"
 "L 21012480 FREELISTS 1 FREELIST GROUPS 1)      

into

"CREATE TABLE "LXRO_DC96E183" ("LXOID" NUMBER(*,0), "LXFLAGS" NUMBER(*,0), ""
 "LXTYPE" NUMBER(*,0), "LXFROMLAT" NUMBER(*,0), "LXFROMID" NUMBER(*,0), "LXTO"
 "LAT" NUMBER(*,0), "LXTOID" NUMBER(*,0), "LXCRDATE" DATE, "LXRELRUL" NUMBER("
 "*,0))

4. Execute the script file(foo.sql) in the user u want to have data.
3. Execute imp again with ignore=y option


Metanil

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sathya_sAuthor Commented:
I will check it and let you know.
Thanks.

Sathya
sathya_sAuthor Commented:
Hi Metanil,
 Creating multiple datafiles worked...Maxsize for a single datafile is 32GB.
 Sorry for teh late response.
Thanks,
 Sathya
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.