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

Home -> Community -> Usenet -> c.d.o.server -> Re: Process Deadlock

Re: Process Deadlock

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 27 Feb 2007 07:46:52 -0800
Message-ID: <1172591212.162952.284370@m58g2000cwm.googlegroups.com>


On Feb 27, 9:43 am, "rjgst3" <rjg..._at_rmu.edu> wrote:
> I working with Oracle 10g and consider myself as a beginner. I'm
> using dbms_job to run processes over a db link and I'm encountering
> issues with a dealock on the link. I'm trying to find some sort of
> error handling for this issue. Let me go into more detail.
>
> I am using a db link to a teradata database to pull data back into
> Oracle 10g. I am using the following
>
> CREATE DATABASE LINK "Teradata"
> CONNECT TO "TERA_USER" IDENTIFIED BY "PSWRD"
> USING 'TD.WORLD'
>
> I notice that when my teradata password expires (30 days), my
> processes that use the db link deadlock somewhere? I don't know
> where. I tried dropping the db link and re-creating it, but of course
> that does not work. Also, I contact the DBA's for teradata to kill
> all sessions for the TERA_USER id and still, no luck. There appears to
> be an open session on my side, and not being the DBA, I can find the
> session that is in deadlock. However, if I can add error handling to
> my pl/sql, I think I could solve for the issue....help.

Queries over a database link are distributed transactions and as such require a commit or rollback to terminate them and release the rollback entries taken in support of two-phase commit. Does you application commit its distributed queries.

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

How do I find distributed queries / transactions (either issued from or connecting to this instance)? http://www.jlcomp.demon.co.uk/faq/find_dist.html

HTH -- Mark D Powell -- Received on Tue Feb 27 2007 - 09:46:52 CST

Original text of this message

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