import failed

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

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

Quote:

---

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);