Log Miner

These are steps to use log miner:

1-) Execute following PL/SQL block as sys user (note that first dbms_logmnr.add_logfile procedure is called with dbms_logmnr.new argument while others are called with dbms_logmnr.addfile argument):

begin
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.new);
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.addfile);
...
sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
end;

2-) You can select log content using following query:

select * from V$LOGMNR_CONTENTS;

3-) Because content is read from file sequencely, it is very slow. If you are planning to query it multiple times, i strongly suggest you to insert log miner data to a local table and create an index on timestamp column. Requested storage for local table and its index is usually at around 4*(log size)*(number of log files).

create table LOGMNR TABLESPACE TOOLS as select * from V$LOGMNR_CONTENTS;
CREATE INDEX LOGMNR_IX1 ON LOGMNR ("TIMESTAMP") TABLESPACE TOOLS ;

4-) After you finish with log miner, execute following query:

begin
sys.dbms_logmnr.end_logmnr;
end;

How to find remote session executing over a database link

Follow this procedure to find remote session created by local session executing query over database link:

1-) Find local session id from v$session

2-) Execute following script on both local and remote databases:

Select /*+ ORDERED */
s.sid,substr(s.username,1,15),substr(s.ksusemnm,1,10)'-' substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)'.' substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(w.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w ,v$session s
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx
and w.sid=s.sid
/

3-) From the script output on local database find GTXID value for local session
4-) From the script output on remote database, search for a GTXID value matches the one we find on step 3. When you find it, sid column of the same row is the session we are looking for on remote database.

Another method is using lsof (assuming it is a unix server)

1-) Find process id of the local session

select spid from v$session s ,v$process p where s.paddr=p.addr and s.sid={local_sid}

2-) On the server (running local database) find out TCP connections of the session

lsof -p {process id}

3-) Search for a line containing remote server name (or IP) in output of step 2. When you find it, take destination port number
4-) On remote server run following command to find oracle process listening on this port

lsof -i tcp:{port_number}

5-) Now you can find remote session by running following script on remote database:

select s.sid from v$session s ,v$process p where s.paddr=p.addr and spid={oracle_process_id}