select
  c.KMPCSOTCMON,                           -- CMON_ADDR RAW(8)
  b.SADDR,                                 -- SESSION_ADDR RAW(8)
  a.CONNECTION_ADDR,                       -- CONNECTION_ADDR RAW(8)
  a.USERNAME,                              -- USERNAME VARCHAR2(1024)
  a.PROXY_USER,                            -- PROXY_USER VARCHAR2(1024)
  a.CCLASS_NAME,                           -- CCLASS_NAME VARCHAR2(1024)
  a.PURITY,                                -- PURITY VARCHAR2(1024)
  a.TAG,                                   -- TAG VARCHAR2(1024)
  a.SERVICE,                               -- SERVICE VARCHAR2(64)
  a.PROCESS_ID,                            -- PROCESS_ID VARCHAR2(24)
  a.PROGRAM,                               -- PROGRAM VARCHAR2(64)
  a.MACHINE,                               -- MACHINE VARCHAR2(64)
  a.TERMINAL,                              -- TERMINAL VARCHAR2(16)
  a.CONNECTION_MODE,                       -- CONNECTION_MODE VARCHAR2(1024)
  DECODE(                                  -- CONNECTION_STATUS VARCHAR2(10)
    c.KMPCSOSTATE, 0 , 'NONE', 1 , 'CONNECTING', 2 , 'NONE', 3 , 'ACTIVE', 4 , 'WAITING', 5 , 'IDLE', 6 , 'NONE', 7 , 'CLOSING') CONNECTION_STATUS,
  a.INST_ID                                -- INST_ID NUMBER
from
  X$KPPLCONN_INFO a left join V$SESSION b on a.sid = b.sid and a.serial# = b.serial#,
  X$KMPCSO c
where
  a.CONNECTION_ADDR = c.KMPCSOADDR