How to list logon/logoff sessions in the Oracle (Clinical) database including failed logins?
Example for Oracle 11g database (OC 4.6.x)
Logon to SQL*Plus or TOAD as DBA user in the Oracle database with access to DBA system views and tables.
SQL query DBA Audit Session
/* Logon/Logoffs using referencing lookup function for returning error message for return (error) code */ select username, terminal, action_name, to_char(timestamp,'YYYYMMDD HH24:MI:SS') timestamp, to_char(logoff_time,'YYYYMMDD HH24:MI:SS') logoff_time, returncode, get_ora_error_message(-returncode) error_msg from dba_audit_session where 1=1 -- timestamp > sysdate-7 AND username like '%' -- = 'OPS$<init>' -- OC users are prefixed with OPS$ and returncode <> 0 -- <> 0 to list failed logins --and logoff_time is not null order by 5 desc;
Function used to return error message from error code (return code)
create or replace function get_ora_error_message(p_ora_number in number) return varchar2 is v_msg varchar2(255); begin v_msg := sqlerrm(p_ora_number); return v_msg; end; /
Return codes and messages (commonly seen)
ORA-01017: invalid username/password; logon denied
ORA-01045: user lacks CREATE SESSION privilege; logon denied
ORA-03136: inbound connection timed out
ORA-28000: the account is locked
ORA-28001: the password has expired
ORA-28003: password verification for the specified password failed
ORA-28007: the password cannot be reused
ORA-28043: invalid bind credentials for DB-OID connection