Is it possible to query the history of previously locked/unlocked Oracle database user accounts? I've tried dba_user but there is no option to get history.


You can't. The database stores the actual status of an account and the time since it is expired or locked, but no history. The only history it stores is the password history, for enforcing password policies.

  • Any other options to get these results? – ree Apr 2 '16 at 9:34
  • @ree Sure. With some custom script, periodically collect and store the required information. – Balazs Papp Apr 2 '16 at 10:04
  • Dear Balazs,can you please share,if you have – ree Apr 2 '16 at 10:50
  • @ree This question was about "past history", Balazs his comment would log future changes. Logging future changes seems a new question to me. – Tom V Apr 2 '16 at 12:08
  • 1
    A more fundamental question seems to by "why?" Of what real use could be made of this history information? What business decision is determined by it? – EdStevens Apr 2 '16 at 14:37

Going forward, you could implement auditing of alter user commands which would capture when a user ID is unlocked, but would not capture when it got locked due to number of unsuccessful login attempts. Take a look at http://docs.oracle.com/database/121/DBSEG/audit_config.htm#DBSEG1115 for details. If you really need to know when it was locked, etc. as Balazs suggests, you could invoke SQL every hour/day/week that would insert into a table a copy of the columns of interest from dba_users:

create table my_log (as_of date,
                 username varchar2(128),
                 account_status varchar2(32),
                 lock_date date);

Then every so often, do this:

insert into my_log (select sysdate, 
          a.username, a.account_status, a.lock_date from dba_users a);

Once the account is open and you want to know when it was locked, you can use user$ view.

 select NAME,LTIME 
 from user$ 
 where NAME='abc';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.