Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Process Deadlock
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