Without Audit Trail

SELECT username,

       account_status,

       lock_date,

       profile

  FROM dba_users

 WHERE username='MYUSER'; 

Trigger Based Information Capture

CREATE TABLE sys.myfailedlogins

(

  USERNAME   VARCHAR2(30),

  USERHOST   VARCHAR2(30),

  USERACTION VARCHAR2(30),

  USERMODULE VARCHAR2(30),

  TIMESTAMP  DATE,

  ERRORCODE  VARCHAR2(5)

);

CREATE OR REPLACE TRIGGER sys.logon_trigger

AFTER SERVERERROR ON DATABASE

DECLARE

  TYPE list_tab_type IS TABLE OF VARCHAR2(5);

  list_tab list_tab_type := list_tab_type('1005','1017','28000','28001');

BEGIN

DBMS_OUTPUT.PUT_LINE( 'Got this far' );

  FOR e IN list_tab.first .. list_tab.last

  LOOP

    IF (IS_SERVERERROR(e)) THEN

      INSERT INTO myfailedlogins VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'),

                                        SUBSTR(SYS_CONTEXT('USERENV', 'HOST'),1,30),

                                        SUBSTR(SYS_CONTEXT('USERENV', 'ACTION'),1,30),

                                        SUBSTR(SYS_CONTEXT('USERENV', 'MODULE'),1,30),

                                        SYSDATE,

                                        e);

      COMMIT;

    END IF;

  END LOOP;

END;

/

To Query the results...

ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';

SELECT * FROM sys.myfailedlogins ORDER BY TIMESTAMP DESC;

To drop the trigger and table when done...

DROP TRIGGER sys.logon_trigger

DROP TABLE sys.logon_trigger

Bibliography