Changing rollback segments for an app to use

[Krutsinger]

I am trying to get a COGNOS query to work. Right now I have a view that gets all of the data fields and makes calculations, so all that the tool needs to do is to query the view and put it in the cube.

Unfortunately, I get through a few million rows when I get the error: ORA-01555: snapshot too old: rollback segment number 8 with name "RBS7" too small.

I have a huge rollback segment available that I can use for these types of transactions, but there is no way that I can think of to force the COGNOS tool to use it. Hopefully one of you can though.

Please help, this is a really irritating problem that I'm sure can be solved pretty easily.

[marist89]

In theory, you could take every RBS offline except for the one you want to use and then issue your query. If this is a production DB, this approach is not very practical. Your other alternative is to have all your RBS sized properly for your queries.

[sanju_dba]

if its a Production Server and if you cannot afford to keep all the rollback segments of the Same Big size, then OPS is the way to achieve it.


Thanx
Sanjay

[pando]

are you running reports in an OLTP database....?

Also use OPS just for this purpose would be a joke heh

[Krutsinger]

I am working off of a data warehouse, so I believe that marist's idea of turning the other segments offline would be the most feasible.

This brings up another question however. Currently I have one big rollback segment and 9 other smaller segments, which is the general setup for most of the transactional databases here on site. However, since I am running a data warehouse, and no transactions except for data loads are done, should I lean my rollback segments more towards having a couple of large rollback segments, or should I keep my configuration the way it is?

Incidentally, this is the first occurrence of it's type in the year and a half I've been running the warehouse, so it's not a normal thing.

[pando]

well in a datawarehouse you shouldnt see snapshot too old error unless you are querying when there is a batch job?

[anandkl]

when u r running this particular query make sure your query uses the large rollback segment.

execute this command before runing ur query

set transaction use rollback segment ;

also try to create another large rollback segment for ur data warehouse process

cheese
anandkl

[Edited by anandkl on 01-30-2002 at 02:49 AM]

[pando]

Originally posted by anandkl
when u r running this particular query make sure your query uses the large rollback segment.

execute this command before runing ur query

set transaction use rollback segment ;

also try to create another large rollback segment for ur data warehouse process

cheese
anandkl

[Edited by anandkl on 01-30-2002 at 02:49 AM]

You mean when running a large transaction?
A query doesnt write rollback so what is the point set a rbs to a query?

[anandkl]

i know a query does not require a rollback.what i mean was when runing this particular large transaction.I am sorry if i have confused u.

cheese
anandkl

[Krutsinger]

Hmm, I didn't think that a query would use a rollback segment, but I keep getting the snapshot too old error every time I try to create a cube (COGNOS). The process should (better be) be query only, and I do not believe there are any batch files that are being executed that should be interefering with me. Besides, the tablespace that I am working from hasn't have data moved in or out of it in months.

anandkl: I can't use the set transaction command since the tool I am using won't allow me to set any Oracle commands (otherwise this thread would have never appeared).

Hmm... I turned off all of the smaller rollback segments and started the cube again. I suppose I'll have to wait and see what happens.