Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Linking IP address to Oracle process
Ok, i'll through in yet another solution for Oracle7.2.3 and up (that's 7.2.3 and up, not before).
Basically it works by using the audit trail, which asof 7.2.3 catches the tns string in the comment_text field of the audit trail.......
have fun.
create or replace package ip
as
pragma restrict_references( ip, wnds, wnps, rnps );
function address return varchar2;
pragma restrict_references( address, wnds, wnps );
procedure into_v$session;
end ip;
/
show errors
create or replace package body ip
as
theAddress varchar2(2000);
function address return varchar2
is
begin
return theAddress;
end;
procedure into_v$session
is
begin
dbms_application_info.set_client_info( theAddress );
end;
begin
select comment_text
into theAddress from user_audit_trail where sessionid = userenv('SESSIONID'); declare n number; begin n := instr( theAddress, '(HOST=' ); theAddress := substr( theAddress, n+6 ); n := instr( theAddress, ')' ); theAddress := substr( theAddress, 1, n-1 );end;
grant execute on ip to public
/
create public synonym ip for ip
/
On 11 Nov 1996 17:00:58 GMT, sds_at_dataplusnet.com (Strategic Data Systems) wrote:
>In article <328259C1.32E4_at_bekaert.com>, MARREEL_CHRIS/BENG_at_bekaert.com says... >> >>Andy Finkenstadt wrote: >>> >>> >> We have had a similar problem - using SQL*Net to connect to Oracle, how can >>> >> we tell which session belongs to which user (given that our app is >>> >> designed to connect to Oracle with a common Oracle userid). >>> >>> Using "lsof -i" check for all processes that have connections to >>> the local orasrv port. The remote host name will be the machine >>> connected to your local oracle database. >>> >>> Next cross-check the local process number with the process number >>> recorded in the v$session & v$process views. >>> >>> Andy >>> -- >>> Andrew Finkenstadt, The Printing House Ltd, Also a GEnie Sysop >>> >> >>This looks very interresting. Even the guys at the ORACLE-support here in Belgium >>didn't know that because we recently asked this question to the support-department. >>Even the responce center of HP-Belgium told us it was IMPOSSIBLE. I will forward them >>the information. >> >>Where can I find the above mentioned "lsof"-program ? >>I have it on my system (probably installed with other software from a third party) at themoment, but I
>>don't have any documentation (man-pages....) >>This "lsof"-program seems VERY interresting.... >>Can you tell me where I can find it ? >> > > >I modified the orginal, because the join os v$session and v$process didn't work > > >accept PID prompt 'Enter PID = ' >column username format a8 >column sid format 9999 >column serial# format 99999999 >column sql_text format a45 word_wrap >select > s.username, > s.sid, > s.serial#, > s.status, > decode(sql_text, NULL, 'NON-SQL (DDL, GRANT, etc.)', sql_text) >sql_text >from > v$sqlarea a, > v$session s, > v$process p >where > s.sql_address = a.address(+) > and s.sql_hash_value = a.hash_value(+) > and s.username is not null > /*the following was changed*/ > and s.paddr=p.addr > and p.spid=&PID >order by > username >/ >
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com