Set Transaction - Reports (Problem)

[yBayM]

Good Day!

Guys, i need your advise on this, can i asked how am i going to
integrate the 'SET TRANSACTION USE ROLLBACK SEGMENT" statement with
my reports, you see im having problems big yearly reports, bec of the
snapshots problem, so i was thingking that i'll just create one big RB
segment and directs all my reports to that RB segment, but how?

Thanks in advance.


AGL
Software Engineer
MCWD - Philippines

[DA Morgan]

yBayM wrote: Good Day! Guys, i need your advise on this, can i asked how am i going to integrate the 'SET TRANSACTION USE ROLLBACK SEGMENT" statement with my reports, you see im having problems big yearly reports, bec of the snapshots problem, so i was thingking that i'll just create one big RB segment and directs all my reports to that RB segment, but how? Thanks in advance. AGL Software Engineer MCWD - Philippines

I've got what I think is a better question. Given everything Tom Kyte,
and others, have written about SET TRANSACTION ... why are you even
considering its use?

But by way of background what version of the Oracle database?
What version of reports?
Why do you think rollback segments relate, in any manner, to a report?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Thomas Kyte]

In article <IKSdnY0jZ9BYbMjeRVn_vQ@giganews.com>, yBayM says...Good Day!Guys, i need your advise on this, can i asked how am i going tointegrate the 'SET TRANSACTION USE ROLLBACK SEGMENT" statement withmy reports, you see im having problems big yearly reports, bec of thesnapshots problem, so i was thingking that i'll just create one big RBsegment and directs all my reports to that RB segment, but how?Thanks in advance.AGLSoftware EngineerMCWD - Philippines


I'll guess - your reports get an ora-1555.

Your reports are not *generating* undo, they are consumers of others undo
information. They need undo generated BY OTHER transactions -- these other
transactions are using these other rollback segments.


the probability of your getting a 1555 is in relation to the size of your
smallest rollback segment - assigning your reports (which don't generate undo
really) to a "big rbs" won't avoid the 1555.

Only by having sufficient undo permanently configured to hold all of the undo
generated during the length of time it takes to run your longest running query
can you achieve that.

Suggest you use automatic undo management if possible, setting undo_retention to
a number larger than it takes for your long running queries. Also, ensuring
that the undo tablespace is either allowed to autoextend OR has been given
sufficient disk space to hold that much undo.

If not using automatic undo management, then you have to permanently make your
existing rbs's "larger" by increasing their extent size or by increasing their
minextents. Make them all the same size - just bigger than they are now.


--
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation