Blog Blog 齋

來個反高潮

(For delicated server only)

A transaction is locking a table. It is possible to find out client information from V$SESSION columns like PROGRAM and MACHINE. But it does not provide enough information to identify the correct client if you have 5 SQLPlus running.

Using mixed technique on Oracle and Linux, the client is found. 

First, find out the OS process ID from v$process, for all open transactions.

SQL> select p.spid from v$transaction t, v$session s, v$process p
  2  where t.ses_addr = s.saddr
  3  and s.paddr=p.addr;

SPID
————————
24295

The return value is the pid (process ID) of the corresponding Oracle server process. The detailed information is available, of course, from the /proc filesystem. Knowing that sockets are handled as files in Unices, so, let's go to the /proc/24295/fd directory to have a look.

[oracle@oracle_host fd]$ cd /proc/24295/fd
[oracle@oracle_host fd]$ ls -l
total 12
lrwx——  1 oracle oinstall 64 Sep 23 16:11 0 -> /dev/null
lrwx——  1 oracle oinstall 64 Sep 23 16:11 1 -> /dev/null
lrwx——  1 oracle oinstall 64 Sep 23 16:11 10 -> /u01/app/oracle/oradata/orcl/orcldata01.dbf
lrwx——  1 oracle oinstall 64 Sep 23 16:11 13 -> socket:[8666176]
lrwx——  1 oracle oinstall 64 Sep 23 16:11 2 -> /dev/null
lr-x——  1 oracle oinstall 64 Sep 23 16:11 3 -> /dev/null
lr-x——  1 oracle oinstall 64 Sep 23 16:11 4 -> /dev/null
lr-x——  1 oracle oinstall 64 Sep 23 16:11 5 -> /dev/null
lr-x——  1 oracle oinstall 64 Sep 23 16:11 6 -> /dev/null
lr-x——  1 oracle oinstall 64 Sep 23 16:11 7 -> /dev/zero
lr-x——  1 oracle oinstall 64 Sep 23 16:11 8 -> /u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lr-x——  1 oracle oinstall 64 Sep 23 16:11 9 -> /u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb

(How can I disable the stupid line breaks?)

The socket:[8666176] like looks interesting. As everyone knows, Linux assign an inode to each open file. This also applies to TCP sockets. Actually, the 8666176 is the inode number. So, where is the socket information stored?

The answer is, well, /proc filesystem again. cat (should I say Cat?) the /proc/net/tcp file. Look at the header, you will see that the last column is the inode.
We are only interested in the header row and the row with 8666176.

[oracle@oracle_host net]$ head -1 tcp; grep 8666176 tcp
  sl  local_address rem_address   st tx_queue rx_queue tr tm->when retrnsmt   uid  timeout inode
  28: 4E01A8C0:05F1 E201A8C0:042A 01 00000000:00000000 02:0009B649 00000000   505        0 8666176 2 d1979700 437 40 11 3 -1

Yes, local_address and rem_address represents the local and remote IP address + port for the TCP connection, respectively. The information are stored in hexidecimal format. Converting to decimal and taking care about network byte order, we get

4E01A8C0:05F1 = 192.168.1.78:1521
E201A8C0:042A = 192.168.1.226:1066

These are the local and remote ports on this connection.

If 192.168.1.226 is linux, you can also go to have a look on /proc/net/tcp. You should see that the pair appears again, with local and remote reversed. Searching through /proc/nnnnn/fd, the exact client process is found. (Of course, it is easier to use netstat.)

I don't know about anything like /proc filesystem on Windows. (Windows tends to hide as much from the user as possible.) Windows netstat also does not include pid / program name. One program called Active Port can list the process the the opened ports.