import problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: import problem

  1. #1
    Join Date
    Nov 2000
    Posts
    173
    Help, Here is the scenario. I am trying to import a schema (for days now) from a database on another machine to a new machine. I have created all the tablespaces, roles, users, etc. Some tables import ok others are getting an error.

    Below is the tablespaces from the old database.

    TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA MIN_EXTLEN STATUS CONTENTS LOGGING
    -------------------------------------- ---------- ---------- ---------- ---------- ---------- --------- --------- ---------
    SYSTEM 12288 12288 1 249 50 0 ONLINE PERMANENT LOGGING
    RBS 1048576 1048576 10 249 0 0 ONLINE PERMANENT LOGGING
    TEMP 1048576 1048576 1 2147483645 0 0 ONLINE TEMPORARY LOGGING
    TOOLS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
    USERS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
    MY 16384 8192 1 2147483645 0 0 ONLINE PERMANENT LOGGING
    SPA_DATA 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
    BUS_DATA 20480 40960 1 500 0 0 ONLINE PERMANENT LOGGING
    MY_INDEX 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
    9 rows selected.


    Tablespaces from the new database.

    TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA MIN_EXTLEN STATUS CONTENTS LOGGING
    ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- --------- --------- ---------
    SYSTEM 12288 12288 1 249 50 0 ONLINE PERMANENT LOGGING
    RBS 1048576 1048576 10 249 0 0 ONLINE PERMANENT LOGGING
    TEMP 1048576 1048576 1 2147483645 0 0 ONLINE TEMPORARY LOGGING
    TOOLS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
    USERS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
    MY 16384 8192 1 2147483645 0 0 ONLINE PERMANENT LOGGING
    MY_INDEX 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
    BUS_DATA 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
    SPA_DATA 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
    9 rows selected.

    Error from Import:

    ORA-01658: unable to create INITIAL extent for segment in tablespace MY_INDEX
    IMP-00017: following statement failed with ORACLE error 1658:
    "CREATE INDEX "S30_IX2" ON "S30" ("SP_FID" ) PCTFREE 5 INITRANS 2 MAXTRANS "
    "255 STORAGE (INITIAL 409600 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 500 PCTINCR"
    "EASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SDE_I"
    "NDEX" LOGGING"
    IMP-00003: ORACLE error 1658 encountered
    ORA-01658: unable to create INITIAL extent for segment in tablespace MY_INDEX
    . . importing table "S31" 7855 rows imported
    IMP-00017: following statement failed with ORACLE error 1658:
    "CREATE UNIQUE INDEX "S31_IX1" ON "S31" ("GX" , "GY" , "EMINX" , "EMINY" , ""
    "EMAXX" , "EMAXY" , "SP_FID" ) PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE (I"
    "NITIAL 1024000 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 0 FREELI"

    questions:

    *** I am assumming that the import is trying to create an initial extent for index s30_IX2 of 409600,
    and an initial extent for index s31_IX1 of 1024000. the old database had an initial extent of 20480. How did these extents get larger than 20480?

    ***same thing with the next extent was to be 40960 but both of these next extents are looking for 102400. How did they grow bigger than the original storage.

    ***Is this the reason why my import fails?

    ***What do you sugget I do, increasing the initial and next extent seems very high. Is this ok to do?

    Thanks,
    Kathy

  2. #2
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    exp - imp eliminate the segment extent, so if you have a segment made on 10 extnets...you'll have e segment made on 1 extent (as sum of old extents). So increase the tablespace size and retry the import (only of the table failed...after droping the new one). i don't sugest you change the initial extent of the index because in creation it will take all the extents necessary for the creation ...(and prob. you'll take error)

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I guess while taking export you mentioned compress=y so its trying to compress the exisiting data to 1 extent and placing into 1 single extent while importing.

    couple of options to deal with.

    choose compress=n option for export.
    else try first importing with no data rows=n and then data with rows=y
    create tablespace with enough space.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width