Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17895
  • Last Modified:

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
0
sathya_s
Asked:
sathya_s
  • 4
  • 2
1 Solution
 
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

0
 
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
0
 
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.

0
 
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
0
 
sathya_sAuthor Commented:
I will check it and let you know.
Thanks.

Sathya
0
 
sathya_sAuthor Commented:
Hi Metanil,
 Creating multiple datafiles worked...Maxsize for a single datafile is 32GB.
 Sorry for teh late response.
Thanks,
 Sathya
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now