It's just a matter of knowing where a connection is coming from. For
instance, I have 3 databases connecting to our accounting database using
database links. If I need to bring down this db, I like to contact the
users in the other databases. I have a hard time determining who they are.
--
Terry Dykstra
Canadian Forest Oil Ltd.
"Sybrand Bakker" <oradba_at_sybrandb.demon.nl> wrote in message
news:7bbp3uck5m4o730m8ffdkdd5skrpl7h4hd_at_4ax.com...
> On 9 Jan 2002 18:49:53 GMT, n.a.ekern_at_usit.uio.no wrote:
>
> >Hi all.
> >
> >We are using Oracle 8.1.7 databases.
> >
> >I am wondering, if a database DB1 is accessed over a database link, is
> >there any way to find out the following:
> >
> >The database from which the database link connection is coming from.
> >
> >If there is, I am planning on having a logon trigger logging this, and
> >maybe deny the logon.
> >
> >I have looked in v$session but could not find it there.
> >I also tested the following:
> >
> >Created a function in the DB1 database under username test:
> >create or replace function testcallstack
> >return varchar2
> >is
> >begin
> >return dbms_utility.format_call_stack;
> >end;
> >/
> >
> >Executing it from inside DB1 gives the following result:
> >variable callstack varchar2(4000)
> >exec :callstack:=testcallstack
> >print :callstack
> > ----- PL/SQL Call Stack -----
> > object line object
> > handle number name
> >d8736a68 5 function TEST.TESTCALLSTACK
> >d86790e8 1 anonymous block
> >
> >But executing from another database like this:
> >
> >Create database link db1 connect to test identified by testpw using'db1';
> >variable callstack varchar2(4000)
> >exec :callstack:=testcallstack_at_db1
> >print :callstack
> >
> >gives NULL as result.
> >
> >I would appreciate very much any tip on how to find the identity of the
> >foreign database.
> >
> >Tia,
> >Njål Ekern
> >n.a.ekern_at_usit.uio.no
> >naekern_at_hotmail.com
>
>
> You could try v$session_connect_info.
> However I have the feeling this is a more or less absurd situation: in
> a properly designed distributed database you would know where the link
> was coming from, wouldn't you?
> Doesn't this imply you can't exercise any control over your
> distributed databases, if you need to resort to such measures and even
> potentially deny the logon?
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Wed Jan 09 2002 - 16:49:22 CST
