Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: fetch across commit

Re: fetch across commit

From: Ganesh Raja <ganesh.raja_at_gmail.com>
Date: Tue, 30 Nov 2004 10:35:40 +0000
Message-ID: <f754edf04113002354ded9256@mail.gmail.com>


Goulet,

If you see the OP's Program he is Doing an Insert into a Different Table and commiting there .. So Why Should he Get a 1555 here or a Fetch Across Commit.

He will get 1555 Only if another Program is doing Updates to the EMP table and commiting so often that it wraps the Undo faster than this Process.

A Fetch Across commit will happen only if the Same Table is Updated and Commitied in a Cursor Loop ...

Additions and corrections welcome.

Cheers!
Ganesh

On Fri, 26 Nov 2004 21:47:03 -0500, Goulet, Dick <dgoulet_at_vicr.com> wrote:
> Seems you've asked this question twice now, which means you did not
> understand the answer the first time. OK; the short answer is yes it
> can cause an ORA-1555 error. The reason is that when you opened the
> cursor Oracle captured the current SCN, say 100. You've done several
> updates/inserts/deletes based on the logic of your program and done a
> commit, which changed the SCN to say 105. In doing so you've told
> Oracle that you are no longer interested in any rollback segments before
> SCN 105, when in fact you are. Immediately that is not a problem, but
> sooner or later part of your cursor will need to recreate a data row to
> SCN 100 with rollback data. Problem is that you've let it go &
> consequently Oracle cannot create a read consistent view as of SCN 100 &
> you get ORA-01555.
>
> One trick I've used rather successfully in the past is to put an "order
> by" clause on the cursor statement. Order By causes a sort, which means
> Oracle has to find all of the data that your cursor will need and sort
> it before handing you the first row. Now all of your return rows are
> stored in a temp table in the Temp tablespace & no more rollback or read
> consistent view activity is needed. It's a hack I'll admit, but one
> that appears to work 90% of the time.=20
>
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> -----Original Message-----
> From: ryan_gaffuri_at_comcast.net [mailto:ryan_gaffuri_at_comcast.net]=20
> Sent: Wednesday, November 24, 2004 1:53 PM
> To: zimsbait_at_gmail.com; Oracle List
> Cc: z b
> Subject: Re: fetch across commit
>
> yes, because a commit releases the lock on the rollback segments and
> orac=3D
> le can overwrite them with another process.=3D20
> -------------- Original message --------------=3D20
>
> > Listers,=3D20
> >=3D20
> > I have a question where I need a little clarification about fetching
> ac=3D
> ross=3D20
> > commits. Can this happen if the table being committed to is not the
> sam=3D
> e=3D20
> > as the tables(s) in the cursors?=3D20
> >=3D20
> > For example, if I had :=3D20
> > cursor c1 is select empname form emp where=3D20
> > dept =3D3D 100;=3D20
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 30 2004 - 04:32:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US