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

Thread: import failed

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I have a oracle 816 dmp file, which is huge and had big initial and next extent so my import failed even though I had enough space on that tablespace. So I decided to do it in 3 steps like..
    1. imp system/manager file=xyz.dmp indexfile=xyz.sql show=y
    2. edit xyz.sql and take out all the storage clauses (you will need to edit that file to take out REM)
    3. run xyz.sql to pre-create your tables and indexes
    4. imp system/manager file=xyz.dmp ignore=y


    But as this file is huge I could not manually take out all storage clauses so I changed INITIAL and NEXT extent values to something smaller with same initial and next extent.

    But did not change max extent !@#

    Now I am in trouble... everthing went smooth except for one index.
    Error: Reached Max # of extents for this index and it rolled back all the rows it imported for that perticular table where that unique index import failed.


    I was thinking of increasing the max number of extent for this index, but how do I do that ?

    Then after I do that, how do I just import that table ?
    what will be the import command ?

    thanks for help
    Sonali


    Sonali

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You can drop that table or recreate the table and then import the table


    import sys/passwd file=imp.dmp tables=(schema.table1, schema.table2,...) indexes=N

    Then you can create the indices for that table manually.


    Hope this would help you,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sonaliak
    ....SNIP...
    Error: Reached Max # of extents for this index and it rolled back all the rows it imported for that perticular table where that unique index import failed.
    I don't belive this is true! The table data should stay there, only that failed index should be nonexistant! There is no way index failure could "rollback" table's imported rows. Oracle allways perform the import of a table in the following sequence:

    1. Create the table if necessary
    2. Insert all rows into the table *and perform an implicit COMMIT*.
    3. Create all the indexes for that table
    4. Create all the needed constraints on the table
    5. Other table-related tasks....

    If index creation failed in step 3, this could not influence steps 1 and 2, it could only influence steps 4 and 5.

    Anyway, for seting/changing MAXEXTENTS for the index is no different as the way you do it for a table:

    CREATE/ALTER INDEX my_index .... STORAGE (maxextents 500);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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