I have an issue with my database Oracle 11gR2.

When I try to connect to my database via SQL Developer, it says that my account is locked after too many missed connections due to a wrong password but when I use the query

SELECT username, account_status FROM dba_users

on SQLPlus, the account is OPEN. I tried to unlock/lock/unlock the account without success.

This problem comes after the expiry of passwords. I've changed them via SQLPlus (with the command ALTER USER <username> IDENTIFIED BY <password>) but when I wanted to connect with my new password on SQL Developer, I had the error message

ORA-01017 Invalid Username/Password.

(and if I put the old password, I had the error message "expired password".)

I would like to know how to unlock the locked account (even though I see it open).

  • 1
    Sounds like you connect to 2 different databases. – Balazs Papp Sep 12 '16 at 13:20
  • yep but i have just one database :( When i'm on SQLPlus with my user i can access to my tables but when i want to connect to my database via sqldeveloper i can't because the account is locked.. the same account with the same login and password... – Flasticot Sep 12 '16 at 13:23
  • Try SELECT * FROM dba_users for the account and see if it looks like everything is set up correctly. You could perhaps check the dba_profiles view to ensure that no 'silly' changes have been made to it. – BriteSponge Sep 12 '16 at 13:33
  • Everything is ok for me, i didn't change anything. I just changed the password of the account this morning. After changing the password i tried to reconnect on SQL Developer but it said "invalid password" as if the password change had not been "registered", so I tried to connect again.. and again.. and now the account is locked but appears "open" with sqlplus.. – Flasticot Sep 12 '16 at 13:48

Try running the following from sqlplus and posting the results.

select username, account_status, lock_date, expiry_date from dba_users where username='<your user name>';
select instance_name, host_name from v$instance;

set term off
set serveroutput on
set long 10000000
set trimspool on
set tab off
set linesize 190
set pagesize  50
set feedback  on
set timing on
set term on
| improve this answer | |
  • With your 2nd query I saw that i'm on the wrong instance ! i changed the password of an account on another instance but i thought i was on the another... omg :). How can i change change the instance i'm working on? i would like to connect to the instance where my account is blowked with sysdba – Flasticot Sep 12 '16 at 15:05
  • 1
    But you said, "yep but i have just one database"?!?! ;-) Perhaps you need to provide additional information such as, what OS, version of Oracle, how do you authenticate when you use sqlplus. For example, if you are on a Linux/Unix server using 10g/11g etc and you are connecting to sqlplus with os authentication, such as sqlplus "/as sysdba" you need to check that you have the correct ORACLE_HOME and ORACLE_SID set. Since you said that you are connecting to the wrong instance. You could also have a similar issue with Windows if you have SQLNET.AUTHENTICATION_SERVICES set. – Gandolf989 Sep 12 '16 at 15:23
  • Yes sorry i'm a begginer with Oracle. The database is on a Windows Server 2008R2 and I have Oracle 11gr2. When i use sqlplus i use "sqlplus / as sysdba" to log in. For me I have just one database, the second instance is a test i made few months ago to test the import/export of the database. But with your query i saw that i was working on my "test" instance. So i understand that all the changes i made today is on the test instance and not on my "real" database, that's why i thought it was not working ! how can i check the ORACLE_HOME and ORACLE_SID? i don't remember having changed these values – Flasticot Sep 12 '16 at 15:46
  • Try running sqlplus directly from dos. type "set" to see the environment variables that are set. Make sure that the following are set: ORACLE_HOME, LD_LIBRARY_PATH, TNS_ADMIN, and ORACLE_SID. Also your %ORACLE_HOME%\bin directory needs to be in your path environment variable. Then when you connect to a database either set the ORACLE_SID to the right database or specify the database and use the listener, assuming that the listener is setup. docs.oracle.com/cd/E11882_01/server.112/e10839/… – Gandolf989 Sep 12 '16 at 16:16
  • I added some commands that you should add to your %ORACLE_HOME%\sqlplus\admin\glogin.sql file above. This will show you which database that you are connected to when you connect using sqlplus. – Gandolf989 Sep 12 '16 at 16:18

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