Re: Set Transaction - Reports (Problem)

In article <IKSdnY0jZ9BYbMjeRVn_vQ_at_giganews.com>, yBayM says...
>
>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'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