I had a user in my oracle DB with some 'x' password for sometime. Without notifying my team I changed the password to 'y'. But my team members tried to connect to the machine with the old passowrd 'x' and as the limit was set, the user account got locked. I know how to set the resource limit for the login.

It would be helpful if anyone can help in finding who and all has tried to connect to the DB. As a administrator I would like to view from where the connection was from.

Thanks in advance.



google for : oracle auditing login failed login


The password 'x' is known to everyone and not the password 'y'.


From your command prompt, type sqlplus "/ as sysdba"

Once logged in as SYSDBA, you need to unlock the locked account (eg. scott) SQL> alter user scott account unlock; SQL> grant connect, resource to scott; SQL> commit;


In the listener log, you will get the IP address of the incoming connection. The Oracle username you already know, as it is the one that is locked, but Oracle will not see the Unix username of the user until they are connected (in v$session). You may be able to correlate that IP address to your user's machine.


Login in to your DB with user SYS

SQL*Plus: Release Production on Wed Jul 25 15:13:25 2012

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

Enter user-name: sys as sysdba

Enter password:

then issue

alter user scott account unlock;

Then you will be able to login as scott.

conn scott/tiger

Your Answer

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