If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Rollback Segments

I am running a query selecting well over 150 columns of data out of our database. Hence it takes forever to run. I am getting the 'unable to extend rollback segment'...... and therefore it never completes. I have set the rollback space to unlimited and even added datafiles and still nothing. I know the suggestion has been to do commits (ie when inserting or updating) but what about on a single select statement?

You are performing SELECT and you are getting "unable to extend rollback segment"? That sounds quite impossible! Can you give us some more detail about your problem? Does your select use some user created functions that might be writting something to the database?

And BTW, the number of columns you are selecting has practicaly no influence on the execution time of your query. You select 1 or 10 or 150 columns - the execution time will be (roughly) the same.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them. These are bank accounts so I am wondering if that could be causing the problem, since they are updated as transactions are performed. But I am not sure of a way around them. Since we are online 24 x 7 I could be running into this problem constantly so there isn't a time when I can run this where there might not be a conflict. Any ideas how to get around this?

Originally posted by bfunky Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them.

Belive it or not, this is simply impossible. Can you post exact ORA-n error messsage you are getting, with exact error text?

I belive you are hitting the infamous ORA-1555 "snapshot too old, rollback segment to small" error, but not the one that says "unable to extend rollback segment" as you specified in your first post. Those are two different errors that have two fundamentaly different causes. The first one happens only to selects but never to inserts/updates/deletes, while the other one happens only to inserts/updates/deletes, but never to selects.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

Originally posted by bfunky Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them. These are bank accounts so I am wondering if that could be causing the problem, since they are updated as transactions are performed. But I am not sure of a way around them. Since we are online 24 x 7 I could be running into this problem constantly so there isn't a time when I can run this where there might not be a conflict. Any ideas how to get around this?

Do u have (Manuel) auditing (For SELECT statement) enabled. For more details read "Oracle One-on-One". But this is not an Oracle generaic behavior. Can be a site specific implementation of auditing on SELECT statements and the auditing table may hitting the error. Are you selecting from a 'View'..? Check for any "INSTEAD OF" triggers on the view. Also check the alert log, on which table is getting the error. The query table and the error hitting table must be different.

Yes it was about the 'unable to extend rollback segments' on a select statement. But I think that once the database file had grown to an unimagineable size that my extents maxed out on that file and caused the error to appear. All is good now.