Re: commit after select ???

Jörn Fieg wrote:
>
> Hi Markus!
>
> It seems to be right, that oracle keeps the selected data in the
> rollback segments for ganteeing the read-consistency. This means, if
> someone alters and commits the data whilst your selection, your
> data will appear like a snapshoot from the time you started the
> selection. This is importend if you are using an order clause and the
> alteration could break this order.
> If you don't need realtime data, you migth fix this problem with a
> snapshoot - in the other case: it seems to me, that you have to commit
> your selection.
>
> CU
> Jörn
>
> Markus Albert wrote:
>
> > Hi everybody,
> >
> > I have a problem with rollback segments.
> > We use ORACLE server 7.3.3.0 and have a database link from instance A
> > to
> > instance B. The applications connected to A and reading data (with SQL
> > select statement)
> > from tables/views from B, via the database link, have to commit in
> > order not to fill
> > up the rollback segments.
> > For me this looks quite strange but I have been told that it is a
> > means of security,
> > because the server doesn't know what else is being done over this link
> > (eventually inserts, updates...).
> >
> > Is there a way of getting around this problem without including commit
> > - commands after
> > those database link queries ???
> >
> > Thanks for any help !
> >
> > Bye, Markus.
> >

The problem generally is not that of "committing" the select statement, but of closing (or re-using) the cursor it uses so that Oracle "knows" that the rollback data may be safely discarded. You do not say what mechanism is being used (Forms? VB? ODBC? Plus?), but it may be that the only way you can close the cursor is by doing a commit.
In SQL*Plus, for example, the main cursor is *never* closed after the first statement is executed, but remains open until another statement is executed in it.

HTH.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"