Re: accessing other users via database links

Kjetil.Skotheim_at_usit.uio.no (Kjetil Skotheim) wrote:
>The exec seemed to work when there was no parameters
>to brb and when brb did not contain any commit.
>With parameters and commit I get
>ORA-02088: distributed database option(?) is not installed
>ORA-02063: before line from DB1
>ORA-02054: the transaction 1.4.99142 is doubtfull
>ORA-06512: at "C.BRB", line 3
>ORA-06512: at line 1

The RDBMS "Distributed" option is required on all involved servers for distributed updates to work. Absent this option, database link access is limited to read-only. It varies by platform, but the Distributed option normally is selected (or not) during Oracle installation. I don't think this has anything to do with the particular user/schema that owns the procedure.

>...this also seems to lock any tables that are updated in
>the brb procedure. Any helpful tips or suggestions on this
>matter will be appreasiated.

The locks are held by the identified in-doubt transaction, which can be removed by a user with the FORCE ANY TRANSACTION system privilege (usually a DBA) or by the user who owns the transaction (if they have the FORCE TRANSACTION system privilege). The dictionary tables which describe in-doubt transactions and the procedures for using ROLLBACK FORCE and COMMIT FORCE to remove them are documented in the Oracle DBA guide.

The particular behavior you are seeing may seem a bit odd, but I think it stems from the fact that the first Oracle (DB1) doesn't know that the remote procedure is going to attempt an update, and the target Oracle (DB2) doesn't know of DB1's limitation until it attempts the commit.

This remote commit, by the way, is an unusual thing to try to do in a "branch" of a distributed transaction...normally the Oracle which owns the transaction (DB1 in your case) must coordinate the commit/rollback activity of all involved servers. Assuming you get the Distributed option installed on your servers, there are some other interesting issues to work out. Read the aforementioned material on distributed transactions and also check the documentation for ALTER SESSION ENABLE/DISABLE COMMIT IN PROCEDURE. Sorry if this seems overly complicated...but distributed updates are.

/b

--
Bill Manry - Mainframe & Integration Technologies - Oracle Corp. USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.