Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, zaibi.

Asked: September 29, 2000 - 3:55 am UTC

Last updated: September 08, 2003 - 12:43 pm UTC

Version: oracle 7.3

Viewed 10K+ times! This question is

You Asked

hi,

I am facing following error while import.
please solve,.

With the distributed option
PL/SQL Release 2.3.4.0.0 - Production

Export file created by EXPORT:V07.01.04
. importing OMS's objects into OMS
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "OMS_BILL_BACK" ("CNSG_NO" VARCHAR2(12), "BILL_REF#" VARCHAR2("
"10), "BKG_DAT" DATE, "CUS_NO" VARCHAR2(10), "SHP_TYP_NO" VARCHAR2(3), "PROD"
"_NO" VARCHAR2(2), "SERVICE_NO" VARCHAR2(3), "SEC_NO" VARCHAR2(3), "ORGN" VA"
"RCHAR2(3), "DSTN" VARCHAR2(3), "WTT_BKG" NUMBER(9, 2), "WTT_ACT" NUMBER(9, "
"2), "PAY_MODE_NO" VARCHAR2(3), "BKG_STAF_NO" VARCHAR2(5), "OT_SRVS_AMT" NUM"
"BER(12, 2), "HNDLG_CHRG" NUMBER(12, 2), "OTHER_CHRG" NUMBER(12, 2), "AMOUNT"
"" NUMBER(12, 2), "PARTNER_AMT" NUMBER(12, 2), "PART_COMM" NUMBER(12, 2), "A"
"MT_CAL" NUMBER(12, 2), "HNDLG_INST" VARCHAR2(3), "EXCISE" NUMBER(11, 2), "C"
"NSG_VAL" NUMBER(12, 2), "INSUR_AMT" NUMBER(10, 2), "ROUTE_NO" VARCHAR2(5), "
""USERID" VARCHAR2(20), "SYSID" VARCHAR2(10), "DISCOUNT_SHIP" NUMBER(12, 2),"
" "DISCOUNT_BOTT" NUMBER(12, 2), "DISCOUNT_FREQ" NUMBER(12, 2), "INCEN_AMT" "
"NUMBER(12, 2), "CREATE_DATIME" DATE, "SCAN_STAT2" VARCHAR2(1), "SCAN_DATE" "
"DATE, "RPT_DATE" DATE, "EOD_STATUS" VARCHAR2(1), "BILL_STATUS" VARCHAR2(1),"
" "INC_STATUS" VARCHAR2(1), "CUT_OF_DATE" DATE, "PED" NUMBER(11, 2), "OCTROI"
"" NUMBER(11, 2), "CMD_CODE" VARCHAR2(3), "PHASE" VARCHAR2(2), "STAFF_NO" VA"
"RCHAR2(8), "DISCOUNT" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
"NS 255 STORAGE(INITIAL 14735360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 121 PC"
"TINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "OMS""
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace OMS
Import terminated successfully with warnings.


and Tom said...

That seems to be somewhat self explainatory. You are getting

ORA-1658: unable to create INITIAL extent for segment in
tablespace OMS.

That means the tablespace OMS you have setup does not 14m of contigous free space in it. Your table is initial 14m next 1m -- it needs 14m of contigous space to create. You do not have that.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:112012348062 <code>for a script that will show you how much space is in a tablespace as well as the largest contigous free chunk of space.

Your options are:

o create the table before you import -- use a smaller INITIAL extent. when you import, use IGNORE=Y to ignore object creation errors (so import will just load the table, not create it)

o add more space to the tablespace or, if there are lots of free space chunks that are adjacent, try issuing "alter tablespace OMS coalesce" and then running the FREE.SQL script again to see if you have 14m or better as the largest chunk.



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Import table's Indexes

Irfan, July 02, 2002 - 6:15 am UTC

Hi Tom,
I have got enough space in tablespace and when trying to import tables, I get the same error during import of index of that table.

IMP-00017: following statement failed with ORACLE error 1658:
"CREATE INDEX "HISTFILEIX" ON "HISTFILE" ("DAT" , "TRA" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 74618880 NEXT 37285888 MINEXTENTS 1 MAXE "XTENTS 121 PCTINCREASE 50 FREELISTS 1) TABLESPACE "HISTDATA""
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace TRADING

Every time I manually create this index without initial extent again. I have nearyl 143 MB of free space in that tablespace whereas the initial extent requires only 74 MB.
Thanks...

Tom Kyte
July 02, 2002 - 8:26 am UTC

You do not have enough free contigous space then.

re-read the answer here -- it describes exactly your issue.

....
That means the tablespace OMS you have setup does not 14m of contigous free
space in it. Your table is initial 14m next 1m -- it needs 14m of contigous
space to create. You do not have that.
......


you may well have 143mb of free space but the largest contigous chunk of free space may well be something like 1m (you might have 143 - 1mb free chunks. you therefore don't have a 74mb free chunk, hence the allocation will fail)

a script (link to a script) is provided here.

SUN CHEN

SUN CEHN, July 29, 2002 - 7:40 am UTC

HI, Tom
I run the following sql scripts in oracle8i and export the results to the file.txt. But I find that the output of the file have space between the records. and I don't want this. How can I solve it and why it happened? thanks........


REM (1) Control the output format

set serveroutput on size 50000
set lin 500
set echo off
set feedback off
set pagesize 50000
set arraysize 100
set verify off
set underline =
set colsep |
REM (2) Accepting input parameters

accept v_outdir prompt 'Please input directory for outputing file (e.g. C:\sis): '

accept v_ac_yr_mth prompt 'Please input the extract A/C Year Month (YYYYMM): '

define p_str_dt = '01'

set head off
select 'Please refer to &&v_outdir\Motor_Claims_Enquiry'||'.txt for the output file' from dual
/
set head on

spool &v_outdir\Motor_Claims_Enquiry..txt

select b.Channel Channel,
b.Policy_No,
b.Name_of_Insured,
b.NCD,
b.Vehicle_No,
b.Make,
b.Model,
b.Year,
b.Date_of_Accident,
km.key_desc Cause_of_loss,
TO_CHAR(b.Paid, '999,999,990.00') Paid,
TO_CHAR(b.os, '999,999,990.00') OS
from
(select ltrim(substr(km.key_desc, instr(km.key_desc,' '))) Channel,
a.Policy_No,
a.Name_of_Insured,
a.NCD,
a.Vehicle_No,
a.Make,
a.Model,
a.Year,
a.Date_of_Accident,
a.cs_of_lss,
a.Paid,
a.os
from (select am.sbu_main_cd,
cm.pcy_no Policy_No,
p.isrd_nm Name_of_Insured,
m.ncd_prctg NCD,
m.rgtn_no Vehicle_No,
m.cmpy_mk Make,
m.mdl_of_car Model,
m.rgtn_yr Year,
cm.acdt_dt Date_of_Accident,
cm.cs_of_lss,
cm.clm_pymnt Paid,
cm.grs_rsrv os
from ac_m am,
clm_m cm,
pcy_info p,
mtr m
where cm.comp_cd = am.comp_cd
and cm.ac_no = am.ac_no
and substr(cm.pcy_no, 4,7) = p.pcy_srl
and cm.pkge_typ_cd = p.pkge_typ_cd
and cm.prcp_cd = p.prcp_cd
and cm.endt_yr = p.endt_yr
and cm.endt_srl = p.endt_srl
and cm.itm_ky = m.itm_ky
and trunc(rgtn_dt) >= (select fm_tx_dt
from gl_intrfc_prd_m
where ac_yr_mth = trunc(to_date('&&v_ac_yr_mth&&p_str_dt', 'YYYYMMDD'), 'YYYY')
and comp_grp_cd = 'SW')
and trunc(rgtn_dt) <= (select to_tx_dt
from gl_intrfc_prd_m
where ac_yr_mth = to_date('&&v_ac_yr_mth&&p_str_dt', 'YYYYMMDD')
and comp_grp_cd = 'SW')) a,
key_mast km
where a.sbu_main_cd = km.key_value
and km.key_id = 'sbu_main_cd') b,
key_mast km
where b.cs_of_lss = km.key_value
and km.key_id = 'cs_of_lss'
order by b.policy_no
/

spool off

set verify on
set feedback on
set echo on


Tom Kyte
July 29, 2002 - 7:48 am UTC

how nice -- a query I cannot possibly run. Hmmm.


I'll guess (after running your script with "select * from emp")

you have set linesize 500
you do not have trimspool on

each line is therefore 500 bytes wide, you really don't have space between the records rather your editor is wrapping the text and it LOOKS like space between the records. You just have a bunch of 500 byte records is all.

set trimspool on


and try again. I gather it "used to work" in 7 or 8 and you are saying "it doesn't work" in 8i. I suspect if that is the case then in 7/8 you had a login.sql that does the set trimspool and you forgot to move that to 8i.



Not importing table's indexes

Ishaque Hussain, September 08, 2003 - 12:27 pm UTC

We are using Oracle version 8.1.7.4.5. We are trying to import data into an empty table. The table doesn't have any indexes on it. We have in the import clause indexes=n. However, the import is still trying to create indexes and the import is failing because there is not enough space for the index. Is there some kind of order in which to write the import statement or is there another parameter that we could be missing?

Tom Kyte
September 08, 2003 - 12:43 pm UTC

constraints=no as well ? perhaps you are seeing the primary key/unique constraint indexes?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.