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: Remote Select opening transaction

Re: Remote Select opening transaction

From: Marc Demlenne <marc.demlenne_at_gmail.com>
Date: Thu, 5 Apr 2007 02:34:09 +0200
Message-ID: <a25b91ba0704041734y52a815f9xf2c7bee291057884@mail.gmail.com>


Ok. Seems to be a not well-known feature anyway, and by-the-way very dangerous.

Thanks for all those replies !

On 4/3/07, Powell, Mark D <mark.powell_at_eds.com> wrote:
>
> For a demo on this topic you can see the following:
>
>
>
> Why does it seem that a select over a db link requires a commit after
> execution?
>
> * http://www.jlcomp.demon.co.uk/faq/dblink_commit.html*
>
>
> -- Mark D Powell --
> Phone (313) 592-5148
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Marc Demlenne
> *Sent:* Tuesday, April 03, 2007 8:43 AM
> *To:* Kerber, Andrew W.
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: Remote Select opening transaction
>
> Hi,
>
> It is ! You can try the following :
> - Do a select * from remoteDB.remoteTable_at_remoteHost (but remoteDB is
> enought, can be same host)
> - Don't commit, rollback or quit. Be carefull not to exit in timeout
> - And monitor the RBS. you'll notice that your sqlplus process is locking
> one of the RBS, and the number of extent will start growing up and up until
> you'll recah the ORA-01562 error.
> I use :
> select rn.name, r.curext, t.start_uext, r.extents, t.xidusn, s.sid,
> s.process, t.start_time
> from V$rollstat r, v$rollname rn, V$transaction t, v$session S
> WHERE t.addr = s.taddr (+) and t.XIDUSN=r.USN AND r.usn(+) = rn.usn
>
> Exiting your sqlplus (or commit or rollback) will unblock the situation,
> freeing your RBS. In my case, we use the "OPTIMAL" parameter and see the
> number of extents immediately reducing to it's optimal value ...
>
> Best Regards,
>
> On 4/3/07, Kerber, Andrew W. <Andrew.Kerber_at_umb.com> wrote:
> >
> > Hi. I am pretty sure your information is incorrect. I have never seen
> > a select across a db link open a transaction.
> >
> >
> >
> > -----Original Message-----
> > *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> > *On Behalf Of *Marc Demlenne
> > *Sent:* Tuesday, April 03, 2007 7:08 AM
> > *To:* oracle-l_at_freelists.org
> > *Subject:* Remote Select opening transaction
> >
> >
> >
> > Hello,
> >
> > In Oracle 8, when performing a select query on another database threw DB
> > link, Oracle opens a transaction that could only be closed by commit or
> > rollback. That could be harmful when developers are not aware of this and
> > don't close transaction. (leading to ORA-01562.)
> >
> > What is the reason of this behaviour ? Is there some docs / faq
> > explaining this ?
> > Is this feature also common to later versions of Oracle ?
> >
> > Thanks a lot for any piece of information given,
> >
> > --
> > Marc
> >
> >
> > ------------------------------------------------------------------------------
> > NOTICE: This electronic mail message and any attached files are
> > confidential. The information is exclusively for the use of the individual
> > or entity intended as the recipient. If you are not the intended recipient,
> > any use, copying, printing, reviewing, retention, disclosure, distribution
> > or forwarding of the message or any attached file is not authorized and is
> > strictly prohibited. If you have received this electronic mail message in
> > error, please advise the sender by reply electronic mail immediately and
> > permanently delete the original transmission, any attachments and any copies
> > of this message from your computer system. Thank you.
> >
> >
> > ==============================================================================
> >
>
>
>
> --
> Marc Demlenne
>
>

-- 
Marc Demlenne

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 04 2007 - 19:34:09 CDT

Original text of this message

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