Check history session run by DB Link oracle

Question

I have created a database link from DB1 to table2 in DB2

Create database link mylink
Connect to user identified by passwd
Using DB2

Then in DB1 I run query:

Select * from table2@mylink

In DB2, can I check the sql text above and find out what machine, OS user running this?

Answer 1

Yes, of course you can. But be aware that Oracle translates your SQL over a remote link and it won't be exactly the same, but still basically recognizable. One thing you will notice is all identifiers are in all-caps and surrounded by double-quotes. The second thing is that the osuser will be oracle (assuming that default software owner is used for the source database), and the machine will be the host of the source database. It will have no identifying information tying you directly to your client session, because your oracle-owned shadow process is what is establishing the connection to the remote. In other words the v$process.spid value of your local session will be the v$session.process of the remote session.

If you know your local session, you can find it that way:

local:

select spid from gv$process where (inst_id,addr) in (select inst_id,paddr from gv$session where sid = ? and session# = ? and inst_id = ?)

remote:

select * from gv$session where process = {spid from above} and machine = {source db host}

Otherwise, you can try searching the shared pool for the SQL. In your target database, query gv$sqlarea filtering on the table name, in all caps and surrounded by double-quotes:

select * from gv$sqlarea where sql_fulltext like '%"TABLE2"%' and see if you can locate it that way. If it's currently running you can join on sql_id to gv$session to get the session information. If it ran in the recent past, you may be able to find it in gv$active_session_history.