DROP TABLE system.client_logons; CREATE TABLE system.client_logons ( username varchar2(100), login_count number, first_login date, last_login date, client_version varchar2(100), proxy_user varchar2(100), client_osuser varchar2(100), client_host varchar2(100), client_ip varchar2(15), client_module varchar2(100), client_info varchar2(100), client_action varchar2(100), network_protocol varchar2(100), terminal varchar2(30), dblink_source varchar2(100), authent_method varchar2(50), authent_identy varchar2(50), authent_type varchar2(50), ident_type varchar2(50), session_nls_language varchar2(50), session_nls_sort varchar2(50), is_sysdba varchar2(30), oci_library varchar2(50), client_charset varchar2(50), client_driver varchar2(50), connection_flag varchar2(50), service_name varchar2(100), server_host varchar2(100), instance_no varchar2(50), instance_name varchar2(50) ) TABLESPACE users; create index system.client_logons_idx_username on system.client_logons(username); CREATE OR REPLACE TRIGGER sys.client_logons_al_trg AFTER LOGON ON DATABASE BEGIN INSERT INTO system.client_logons SELECT nvl(sys_context('USERENV', 'SESSION_USER'),'null') username, 0 login_count, sysdate first_login, sysdate last_login, a.client_version client_version, nvl(sys_context('USERENV', 'PROXY_USER'),'null') proxy_user, nvl(sys_context('USERENV', 'OS_USER'),'null') client_osuser, nvl(sys_context('USERENV', 'HOST'),'null') client_host, nvl(sys_context('USERENV','IP_ADDRESS', 15),'null') client_ip, nvl(sys_context('USERENV', 'MODULE'),'null') client_module, nvl(sys_context('USERENV', 'CLIENT_INFO'),'null') client_info, nvl(sys_context('USERENV', 'ACTION'),'null') client_action, nvl(sys_context('USERENV', 'NETWORK_PROTOCOL'),'null') network_protocol, nvl(sys_context('USERENV', 'TERMINAL'),'null') terminal, nvl(substr(sys_context('USERENV', 'DBLINK_INFO'), 1, instr(sys_context('USERENV', 'DBLINK_INFO'), '.') -1),'null') dblink_source, nvl(sys_context('USERENV', 'AUTHENTICATION_METHOD'),'null') authent_method, nvl(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),'null') authent_identy, a.authentication_type authent_type, nvl(sys_context('USERENV', 'IDENTIFICATION_TYPE'),'null') ident_type, nvl(sys_context('USERENV', 'LANGUAGE'),'null') session_nls_language, nvl(sys_context('USERENV', 'NLS_SORT'),'null') session_nls_sort, sys_context('USERENV', 'ISDBA') is_sysdba, a.client_oci_library oci_library, a.client_charset client_charset, nvl(a.client_driver,'null') client_driver, a.client_connection connection_flag, nvl(sys_context('USERENV', 'SERVICE_NAME'),'null') service_name, nvl(sys_context('USERENV', 'SERVER_HOST'),'null') server_host, nvl(sys_context('USERENV', 'INSTANCE'),'null') instance_no, nvl(sys_context('USERENV', 'INSTANCE_NAME'),'null') instance_name FROM v$session_connect_info a WHERE a.sid = sys_context('USERENV', 'SID') AND nvl(sys_context('USERENV', 'AUTHENTICATION_METHOD'),'null') != 'JOBS' AND (a.network_service_banner like '%TCP%' OR a.network_service_banner like 'Oracle Bequeath%') AND NOT EXISTS (SELECT NULL FROM system.client_logons b WHERE b.username = nvl(sys_context('USERENV', 'SESSION_USER'),'null') AND b.client_version = a.client_version AND b.proxy_user = nvl(sys_context('USERENV', 'PROXY_USER'),'null') AND b.client_osuser = nvl(sys_context('USERENV', 'OS_USER'),'null') AND b.client_host = nvl(sys_context('USERENV', 'HOST') ,'null') AND b.client_ip = nvl(sys_context('USERENV','IP_ADDRESS', 15),'null') AND b.client_module = nvl(sys_context('USERENV', 'MODULE'),'null') AND b.client_info = nvl(sys_context('USERENV', 'CLIENT_INFO'),'null') AND b.client_action = nvl(sys_context('USERENV', 'ACTION'),'null') AND b.network_protocol = nvl(sys_context('USERENV', 'NETWORK_PROTOCOL'),'null') AND b.terminal = nvl(sys_context('USERENV', 'TERMINAL'),'null') AND b.dblink_source = nvl(substr(sys_context('USERENV', 'DBLINK_INFO'), 1, instr(sys_context('USERENV', 'DBLINK_INFO'), '.') -1),'null') AND b.authent_method = nvl(sys_context('USERENV', 'AUTHENTICATION_METHOD'),'null') AND b.authent_identy = nvl(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),'null') AND b.authent_type = a.authentication_type AND b.ident_type = nvl(sys_context('USERENV', 'IDENTIFICATION_TYPE'),'null') AND b.session_nls_language = nvl(sys_context('USERENV', 'LANGUAGE'),'null') AND b.session_nls_sort = nvl(sys_context('USERENV', 'NLS_SORT'),'null') AND b.is_sysdba = sys_context('USERENV', 'ISDBA') AND b.oci_library = a.client_oci_library AND b.client_charset = a.client_charset AND b.client_driver = nvl(a.client_driver,'null') AND b.connection_flag = a.client_connection AND b.service_name = nvl(sys_context('USERENV', 'SERVICE_NAME'),'null') AND b.server_host = nvl(sys_context('USERENV', 'SERVER_HOST'),'null') AND b.instance_no = nvl(sys_context('USERENV', 'INSTANCE'),'null') AND b.instance_name = nvl(sys_context('USERENV', 'INSTANCE_NAME'),'null') ); UPDATE system.client_logons b SET b.last_login = sysdate, b.login_count = b.login_count+1 WHERE client_osuser = nvl(sys_context('USERENV', 'OS_USER'),'null') AND b.username = nvl(sys_context('USERENV', 'SESSION_USER'),'null') AND b.proxy_user = nvl(sys_context('USERENV', 'PROXY_USER'),'null') AND b.client_host = nvl(sys_context('USERENV', 'HOST'),'null') AND b.client_ip = nvl(sys_context('USERENV','IP_ADDRESS', 15),'null') AND b.client_module = nvl(sys_context('USERENV', 'MODULE'),'null') AND b.client_info = nvl(sys_context('USERENV', 'CLIENT_INFO'),'null') AND b.client_action = nvl(sys_context('USERENV', 'ACTION'),'null') AND b.network_protocol = nvl(sys_context('USERENV', 'NETWORK_PROTOCOL'),'null') AND b.network_protocol = nvl(sys_context('USERENV', 'NETWORK_PROTOCOL'),'null') AND b.terminal = nvl(sys_context('USERENV', 'TERMINAL'),'null') AND b.dblink_source = nvl(substr(sys_context('USERENV', 'DBLINK_INFO'), 1, instr(sys_context('USERENV', 'DBLINK_INFO'), '.') -1),'null') AND b.authent_method = nvl(sys_context('USERENV', 'AUTHENTICATION_METHOD'),'null') AND b.authent_identy = nvl(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),'null') AND b.ident_type = nvl(sys_context('USERENV', 'IDENTIFICATION_TYPE'),'null') AND b.session_nls_language = nvl(sys_context('USERENV', 'LANGUAGE'),'null') AND b.session_nls_sort = nvl(sys_context('USERENV', 'NLS_SORT'),'null') AND b.is_sysdba = sys_context('USERENV', 'ISDBA') AND b.service_name = nvl(sys_context('USERENV', 'SERVICE_NAME'),'null') AND b.server_host = nvl(sys_context('USERENV', 'SERVER_HOST'),'null') AND b.instance_no = nvl(sys_context('USERENV', 'INSTANCE'),'null') AND b.instance_name = nvl(sys_context('USERENV', 'INSTANCE_NAME'),'null') AND b.client_module = nvl(sys_context('USERENV', 'MODULE'),'null') AND b.client_version||b.authent_type||b.oci_library||b.client_charset||b.client_driver||b.connection_flag = (SELECT a.client_version||a.authentication_type||a.client_oci_library||a.client_charset||nvl(a.client_driver,'null')||a.client_connection FROM v$session_connect_info a WHERE a.sid = sys_context('USERENV', 'SID') AND nvl(sys_context('USERENV', 'AUTHENTICATION_METHOD'),'null') != 'JOBS' AND (a.network_service_banner like '%TCP%' OR a.network_service_banner like 'Oracle Bequeath%')); EXCEPTION WHEN OTHERS THEN RETURN; END; /