“Holy Remote Session, DBAtman!”
Who is this in the remote DB?
Important, right? What if you identify a lock or other unwanted operation by a DBLink session, how to identify the original session in remote database (origin dabatase)?
The one million answer is simple: by process of v$session. By the way, looks like is easier than find the local process (spid)… Take a look in my example (scripts in the end of post):
dest> @sid Sid:10035 Inst:1 SEQ# EVENT MODULE STATUS SID SERIAL# INST_ID ----- --------- ---------- ---------- ---------- ---------- ---------- 29912 SQL*Net message from client oracle@origin2(TNS V1-V3) INACTIVE 10035 35 1 dest> @spid SPID SID PID PROCESS_FOR_DB_LINK MACHINE LOGON_TIME ------ ---------- ---------- ----------- ----------- ----------- 16188960 10035 882 17302472 origin2 24/08/2015 07:43:40
Now I know the sid 10035 refers to local process 16188960 and the process on origin database is 17302472. What I do what I want if this process:
root@origin2:/oracle/diag/rdbms/origin/origin2/trace>ps -ef |grep 17302472 grid 17302472 1 97 07:42:42 - 5:58 oracleorigin2 (LOCAL=NO) root 24445782 36700580 0 08:05:45 pts/3 0:00 grep 17302472
What include to locae the session in the database by spid, see the sql, and etecetera:
origin> @spid2 Enter value for process: 17302472 SID SERIAL# USERNAME OSUSER PROGRAM STATUS ------- ---------- ----------- ----------- --------------- ---------- 7951 41323 USER_XPTO scheduler_user sqlplus@scheduler_app.domain.net (TNS V1-V3) ACTIVE database2> @sid Sid:7951 Inst: 2 SQL_ID SEQ# EVENT MODULE STATUS SID SERIAL# INST_ID ---------- ----- --------- ------- --------- ----- ------ ---------- 1w1wz2mdunya1 56778 db file sequential read REMOTE_LOAD ACTIVE 7951 41323 2
That’s OK?
Simple isn’t?
The used Scripts (except the “sid”, that is a simple SQL on gv$session):
Get SPID and PROCESS FOR DBLINK from a SID:
# spid: col machine format a30 col process format 999999 select p.spid,b.sid, p.pid, b.process as process_for_db_link, machine, logon_time from v$session b, v$process p where b.paddr=p.addr and sid=&sid /
Get SID from SPID:
#spid2: SELECT s.sid, s.serial#, s.username, s.osuser, s.program, s.status, FROM v$session s, v$process p WHERE s.paddr = p.addr AND p.spid IN (&process); /
Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!
dbatman.blog 