Rollback problem

[Alice]

Hi all,

I have a problem with rollback segment. I am a beginner and do not have
much knowledge of DBA.

Anyways, the problem is there is a transaction of around 105 MB. This would
not fit into the normal rollbacks created and gave an error of "maxextents
for rollback reached", so our DBA created a rollback of 500 MB and I used
this rollback for the transaction by using the "set transaction" command.
But even then the error continued. So I split the transaction by giving
"commits" (and then set transaction again) in the transaction. But the
error continued. The DBA then gave next extent of 50 MB and maxextents 80.
But, yes, the error continued. Now I have given a commit after *every*
insert and update and each transaction is less the 5 K, but the error still
continues.

I don't know what else I can do and the DBA is asking me to split the
transaction further. This I cannot do unless I give an insert, for say 10
columns, commit it and then update the same record for balance 10 columns. I
am at a total loss. I don't think I need to split the transaction further
as it is less than 5 K. Can someone please suggest what could be the
problem?

Thanks and regards,
Alice

[Daniel Roy]

Please post the error message and Oracle version, and then we might be able to help.

Daniel

[niranjan v sathe]

"Alice" <al***@reply-to-group.com> wrote in message news:<40********@news.tm.net.my>...

Hi all,

I have a problem with rollback segment. I am a beginner and do not have
much knowledge of DBA.

Anyways, the problem is there is a transaction of around 105 MB. This would
not fit into the normal rollbacks created and gave an error of "maxextents
for rollback reached", so our DBA created a rollback of 500 MB and I used
this rollback for the transaction by using the "set transaction" command.
But even then the error continued. So I split the transaction by giving
"commits" (and then set transaction again) in the transaction. But the
error continued. The DBA then gave next extent of 50 MB and maxextents 80.
But, yes, the error continued. Now I have given a commit after *every*
insert and update and each transaction is less the 5 K, but the error still
continues.

I don't know what else I can do and the DBA is asking me to split the
transaction further. This I cannot do unless I give an insert, for say 10
columns, commit it and then update the same record for balance 10 columns. I
am at a total loss. I don't think I need to split the transaction further
as it is less than 5 K. Can someone please suggest what could be the
problem?

Thanks and regards,
Alice

dear Alice,
with my little experience I have few suggetions.
1.try it with new installation of Oracle with minimal data and keeping
all the settings to default as possible.If it works,you get a clue.
2.You can always manualy rollback the transaction if you can handle
all the
side effects properly.e.g. delete for insert and update for update
with values maintained in temp table(you need pl-sql).It is bad
way,but something is better
than nothing