Re: Who's calling who??

"Jake" <me_at_heyjay.com> wrote in message news:<apnoao$gks$1_at_bob.news.rcn.net>...
> I need a pointer
> I have the following setup:
> Box1 Box2
> ---- ----
> c1-- db1 --dblink-- db3
> c2-- db2 --dblink --/
>
> that is box1 has 2 databases, each having dblinks to db3. Clients can
> connect to either db on box1. In db1 and db2 there is a view which points
> to db3 (create view myview as select * from remotetable_at_db3). On
> remotetable there is a before insert trigger. I'd like to capture which db
> the insert originated at and stick that value in the remotetable.
>
> I can't change the code on db1 or db2!
>
> How can I determine the dbname (or anything else indicative) for the calling
> db. Using SYS_CONTEXT('USERENV','IP_ADDRESS') I can determine the IP
> address of the caller, but that doesn't help me because there are more than
> one database at the IP. All the other sys_context attributes give me info
> from db3.
>
> Are there some other sql functions, pl/sql function I can use?
> Help?

Hi, Jake,

I can't think of a better solution than configuring two IP addresses on box1 (doesn't need two NICs; two virtual IPs are good enough). Set up one more listener and for HOST in listener.ora, specify the new IP. Use a different listener name.

Alternatively, you can try this. Create a dblink from db3 to db1. In your trigger, check v$lock_at_todb1 where type='DX' and possibly do some matching between this view and v$session_at_todb1 based on sid. If the query returns a row, the insert must be from db1, otherwise it's from db2. You don't have to rely on 'DX' lock in v$lock. You can also look at v$transaction_at_todb1. But I think it's easier to use v$lock.

BTW, I think dbms_application_info can only insert data into the local v$session (or v$session_longops), not the remote one. So accessing db1 or db2 is still necessary.

Yong Huang Received on Thu Oct 31 2002 - 16:37:56 CST