rollback segment question

[sonaliak]

I am trying to do a big import. I have 10 RB segments.
But I keep getting error max # of extents reached in RB5 then I will adjust storage (increase) for this I do the import I get error for RB4 then RB6 and again RB5 Looks like there is no way out for me.
Why is it not using all 10 RB and complain at the RB10 and not something in between ?

How do I know which one to increase ?

I read in Metalink to drop and recreate a RB segment for resizing it. But when you can do that using storage manager or alter rollback segment using SQL why would you want to do that ?

thanks

[rsuri]

create a really BIG rollback segment and bring it ONLINE. Bring ALL other rollback segments OFFLINE and then do that IMPORT.

- Rajeev

[marist89]

or, you could use the commit=y and the buffer= flags.

[uma]

Is it necessary to bring all the small rollback segments offline.Can't we just allot the newly created big rollback segment to that particular transaction by SET TRANSACTION command.

[MJP]

SET TRANSACTION cannot be used for Exp/Imp.

[jmodic]

How would you use SET TRANSACTION for an imp job? Besides, imp performs commits during the import, how would you isue SET TRANSACTION *inside* imp session after each commit?

You have to set all other RBs offline if you want imp to use one particular RB.

[sonaliak]

Thanks all,
I tried to make a big rollback(online) and rest of them were set offline. But still my import failed so then I did what martist had suggested, "you could use the commit=y and the buffer= 640000"
The import failed after getting in 900000 records out of more than million. It did not fail due to rollback but this time..due to some other problem..

IMP-00058: ORACLE error 1654 encountered
ORA-01654: unable to extend index MELX.FK_INT_AUTHATCAT_IDX by 4374 in tablespace MELX

I think I ran out of tablespace this time. I can increase the tablespace but now the problem is how do I import only the rest of the rows as these 900000 rows are already commited.

Please help on this, as it is a pain to do this all over again !

thanks

[marist89]

Yes, you probably ran out of space in the MELX tablespace.

I am not aware of any option that will allow you to continue with imp...

[sonaliak]

Thanks marist for your help..
I will truncate that table drop all the constraint and indexes and reimport it.
I have one more question...
Whats the difference between IGNORE= N and IGNORE= Y.
When do you use it .. with show=y .or rows=y..flags

thanks

[marist89]

IGNORE=Y tells oracle to ignore "this object already exists" error messages and then dumps the data into the table anyway. The reason you can't use this is that it will dump duplicate rows into your table if you don't have a PK associated with it. If you do have a PK, 900K rows will error out before it starts importing the rest of your rows. Although it will work, I think a truncate and import is much cleaner...