Re: select statement to remote database use rollback seg.

"Robert Yeh" <robert.yeh_at_qwest.com> wrote in message news:<9fom2a$3f5$1_at_bob.news.rcn.net>...
> Thank you for the info. I am learning this in a hard way also.
>
> Do you know if the used rb segment continue to grow for the same session
> that continue issuing remote queries?
>
> Bob
> Mark D Powell wrote in message
> <178d2795.0106070502.cb4a2c8_at_posting.google.com>...
> >"Robert Yeh" <robert.yeh_at_qwest.com> wrote in message
 news:<9fm61d$3dd$1_at_bob.news.rcn.net>...
> >> Does anyone know why does a select statement that involve a remote
 database
> >> use rollback segment? What is stored in the rollback segment? Does the
> >> rollback segment keep growing after the creation?
> >>
> >> Thanks
> >
> >Yes, if you read between the lines in the Distributed manual Oracle
> >tells you that all remote SQL takes an entry in rbs for two-phase
> >commit processsing. You should issue a commit after the select to a
> >remote instance in query only applications to release it, otherwise,
> >when it comes time for the rbs segment to wrap around and reuse the
> >extent it will be busy and the segment will extend. It is very
> >possible, if you system has several permanently present jobs that
> >issue remote queries, to end up consuming all available rbs segments
> >extents up to maxextents and for the ever expanding segments to
> >consume all available space in the tablespace. You might guess I
> >learned this fact the hard way.
> >
> >-- Mark D Powell --

I belive that if the same session issues another distributed query then the previous query two-phase commit entry is released. You can look at the rollback segment entries in v$transaction and check the block number being pointed to. If it progresses then the applicaion should not cause you a problem and Oracle should be able reuse the rbs extents when it needs to.
You can tell you have a problem when the transaction start time, start_time, is old.

• Mark D Powell --