Auditing Account Lock and Unlock history

1.     Enable auditing by changing audit trail to DB,EXTENDED.

alter system set audit_trail='DB','EXTENDED' scope=spfile;

2.    Bounce database

C:\Users\farhat>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 6 13:57:50 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2262048 bytes
Variable Size            2164263904 bytes
Database Buffers         2097152000 bytes
Redo Buffers               12103680 bytes
Database mounted.
Database opened.
SQL>

3.     Enable Auditing on alter user activities


audit ALTER USER

4.    Login with a user other than sys and Alter any user 

alter user farhat identified by myuser


alter user farhat account lock

alter user farhat account unlock

5.  Query Audit Trail .

select username,sql_text from dba_audit_trail where sql_text like 'alter user%';

USERNAME    SQL_TEXT                          TERMINAL     TIMESTAMP

SYSTEM alter user farhat account unlock FARHT-PC 10/6/2016 2:19:39 PM

SYSTEM alter user farhat account lock FARHT-PC 10/6/2016 2:07:17 PM

SYSTEM alter user farhat identified by * FARHT-PC 10/6/2016 2:03:28 PM