rem second_client_info.sql
rem (c)Go-Faster Consultancy 2008

spool second_client_info

rem requires following privileges to be granted explicitly
GRANT EXECUTE ON sys.dbms_application_info TO SYSADM;
GRANT SELECT ON sys.v_$session to SYSADM;
GRANT SELECT ON sys.v_$process to SYSADM;
GRANT SELECT ON sys.v_$mystat to SYSADM;

DROP TRIGGER sysadm.connect_secondary_session;
CREATE OR REPLACE TRIGGER sysadm.gfc_connect_secondary_session
AFTER LOGON
ON sysadm.schema
DECLARE
  l_client_info VARCHAR2(64);
BEGIN
  SELECT SUBSTR(p.client_info||'Secondary Session',1,64)
  INTO   l_client_info
  FROM   v$session p           --primary session
  ,      v$session s           --secondary session
  WHERE  s.program = p.program --same program
  AND    s.process = p.process --same process
  AND    s.machine = p.machine --same machine
  AND    s.user# = p.user#     --same user
  AND    s.sid != p.sid        --different session
  AND    s.client_info IS NULL --client info not already set
  AND    s.sid = (SELECT m.sid 
                  FROM   v$mystat m 
                  WHERE  rownum = 1)
  AND    p.logon_time < s.logon_time;

  --this is skipped if query returns no rows
  sys.dbms_application_info.set_client_info(client_info=>l_client_info);
  sys.dbms_application_info.set_action(action_name=>'Secondary Session');
 
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
/


show errors

spool off