STOP. DO NOT PASS GO – if you do not have version 4.2 of SQL Developer, I suggest going and getting it.
It makes this 100% easier. You can see for yourself here. But it’s basically, just right click on your connection, and use ‘Reset Password.’ That’s it.
Or watch the movie.
When you reset your password with the GUI, we change it in your connection properties, too!
You don’t have to update your password twice – that is, you just use the Reset option on the right-click, and we’ll change it in the database, AND update your connection details in one go.
If you’re stuck on on an older version of SQL Developer – please, continue on with the post!
The OLD Instructions – Version 4.1 and Earlier
There’s nothing more annoying than having to email, call, or log a support ticket to have one of your accounts reset. This is no less annoying in the Oracle database. Those pesky security folks have determined that your password should only be valid for X days, and your time is up. Time to reset the password!
Except…you can’t log into the database to reset your password.
What now?
Wait a second, look at this nifty thing I see in SQL Developer:
The JDBC Driver Doesn’t Support This Operation
We can’t make this call over the Oracle JDBC layer, because it hasn’t been implemented. However our primary interface, OCI, does indeed support this. In order to use the Oracle Call Interface (OCI), you need to have an Oracle Client on your machine.
The good news is that this is fairly easy to get going.
The Instant Client will do.
You have two options, the full or ‘Lite’ Instant Clients. If you want SQL*Plus and the other client tools, go for the full. If you just want the basic drivers, go for the Lite.
Make sure you get a 32 bit Instant Client if you run 32 bit SQL Developer or 64 bit if you run 64
Mind the version of Oracle too!
You want to be at the same level or higher of the database you’re working with.
You can use a 11.2.0.3 client with 11.2.0.1 database but not a 10gR2 client with 11gR2 database.
Clear as mud?
Download and Extract
Put it where you want – Program Files is as good as place as any if you have the rights. When you’re done, copy that directory path you extracted the archive to, because we’re going to add it to your Windows PATH environment variable.
The easiest way to find this in Windows 7 is to open the Start dialog and type ‘path’. In Windows 8 you’ll cast your spell and wave at your screen until something happens.
Now with that set, let’s start up SQL Developer.
Check the Connection Context menu again
What happened there?
SQL Developer looks to see if it can find the OCI resources. Guess where it looks? That’s right, the PATH. If it finds what it’s looking for, and confirms the bit level is right, it will activate the Reset Password option.
We have a Preference to ‘force’ an OCI/THICK connection that gives you a few other edge case features, but you do not need to enable this to activate the Reset Password.
There are a few actual benefits to using OCI powered connections, but that’s beyond the scope of today’s blog post…to be continued.
Ok, so we’re ready to go.
Now, where was I again?
Right click on your connection and now choose ‘Reset Password’
I Need Another Option, This Ain’t Working!
If you have another account in the database, you can use the DBA Panel to reset a user’s password, or of course you can spark up a SQL*Plus session and issue the ALTER USER JEFF IDENTIFIED BY _________; command – but you knew this already, yes?
I need more help ‘installing’ the Instant Client, help!
There are lots and lots of resources out there on this subject. But I also know from personal experience that many of you have problems getting this to ‘work.’ The key things to remember is to download the right bit level AND make sure the client install directory is in your path. I know many folks that will just ‘install’ the Instant Client directly to one of their ‘bin’ type directories. You can do that if you want, but I prefer the cleaner method. Of course if you lack admin privs to change the PATH variable, that might be your only option.
Or you could do what the original ORA- message indicated and ‘contact your DBA.’
77 Comments
Sir… How to view or know the saved password for a DB? as I work for an organization I cannot install any extensions or packages to the parent folder..
Thanks and Regards,
Pavan
You can’t. If you’ve forgotten a password, the best thing to do is reset it. Or ask the DBA to reset it for you.
I have installed oracle on window 7 & now i want to connect to sql prompt but i forgot the password.but now i am getting below error.please help
SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01034: ORACLE not availabl
Process ID: 0
Session ID: 0 Serial number: 0
You need to start the database.
There is another way to reset the password through command prompt …
Go to the Oracle Database Folder ( In my case Oracle Database 11g Express Edition) in the START MENU.
Within that folder click “Run SQL Commandline”
–> Type “connect username/password” (your old username and password without the quotation marks)
The message displayed is …
ERROR:
ORA-28001: the password has expired
Changing password for hr
–> New password:
Type the new password
Retype the new password
Message displayed is …
Password changed
Connected.
SQL>
GO TO Sql developer –> type the new password
Thank you very much Suchitra for your help. I was worried and at stake about my entire project due to this one problem. I thank you once again.
Thanks a lot.. It worked for me… 🙂
Hi Jeff,
I have gone through this reset procedure and worked charm for OPEN accounts. But, having issue with EXPIRED accounts.
Tested Environment:
Oracle DB : 11.2.0.2
Instant Client: 11.2.0.4
Looks hitting below bug:
Bug 18289450 : UNABLE TO “RESET PASSWORD” WHEN PASSWORD ALREADY EXPIRED
Have logged SR with Oracle but, still fix is not provided. Being product manager of SQL Developer team, can you pls look into this issue. Your help is much appreciated by our company.
P.S: Most of our users are using the sql developer to access the database. Every time they contact us to reset the password whenever it expires and logging 3-4 requests at average everyday. So, can eliminate these unwanted requests if provide the fix. (Note: Shouldn’t set non-expiry for sake of our IT security complaint)
Hello Jeff. What to do if you just don’t remember what your password is? How do you go back and find it? I am taking a class on Oracle 12c. We are using sql developer.
You’re probably going to have to ask your instructor to reset your database password.
This works fine for account with the current password. Is there a way to reset the password without providing a current password (Assuming the user has privilege to change the password of the particular account) ?
Jeff,
What we really need is a way to drag a box over multiple connections, and do a ‘bulk connection’ password change.
i.e. the situation I have the same named account which exists on 50 different Oracle instances at work. Every 6 months, I have to change the password on all 50 accounts. Now we have a front-end tool that will change all database passwords, but that would be a nice feature to add in.
The problem is, I have to go into each and every of those 50 SQL Developer connections and change each individual password. I would like an easy way to just mass select all my accounts in a certain folder (I put DBA/SYS/functional accounts in one folder, and personal account logins in another folder), and simultaneously change all the passwords at once.
Please add !!
change all 50 at once, to the same password?
Yes, a “bulk” password change feature would be nice. We have to change every 90-days…
Are you saying that you use a different password on every instance you can access? I hope you don’t have a cheat sheet under your keyboard!
So what would be more non-secure – Having a cheat sheet in your office or having the same hardened password on multiple instances that are behind a corp firewall?
Even if you use a password safe app, I double most folks will remember distinct passwords for every account that get changed every 90-days except for the ones they logon to every day. Plus the overhead to also update the new password in the password safe for each account. Better block out your day at work for that one.
The way I make bulk password changes is to change one password. Then, find the new encrypted password in the connections.xml file and do a “change all” to replace the old encrypted password with the new encrypted password.
I agree that a “bulk” password change feature would be helpful.
but that only changes the passwords that SQLDev uses to connect to your db – it doesn’t change the user password IN the database
did you know if you use the ‘change password’ feature in SQLDev, we auto-update the password in your connection?
Yes. We have another process that changes all our passwords in the database. I just needed to update my saved passwords in SQLDev.
I see below when I hit test button
============================================
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable LD_LIBRARY_PATH. Check it to verify that
the expected native library directory /home/oracle/Downloads/instantclient_12_1 is present and precedes any other client installations.
java.library.path = /usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
So that tells you what to check, LD_LIBRARY_PATH
Hi Jeff,
I have downloaded Oracle DB Developer VM (in macbook) and having similar issue. Although, I have downloaded instantclient-basic-linux.x64 from oracle site and extracted it , finally I have configure path of the extracted folder in tools>>preferences>database>advance>use Oracle client.
Then I have restarted but still password reset option is disable. Could you please help.
what do you see when you hit the ‘test’ button on the preferences screen where you specify the Instant Client location?
That was an enormous help! I had never logged into the db before, and password change was required before login. I configured and tested the connection, made sure to get the ‘password expired’ msg, saved profile and applied all the steps from there. This was for SQL Developer 4.0.3.16. Thanks very much!
Oh, very cool Rob. I know there’s a lot going on here, but I’m very glad to hear you got this sorted out.
Jeff,
I know I am a bit behind on this thread, but.. we are currently upgrading to 4.1.1 from 3.0.4 (I know, way to old). The installed client we have on our machines is the ODAC client 12.1 connecting to an 11.2.0.4 database. I am unable to get the reset password feature to work for expired passwords. Any help would be greatly appreciated. Thank!
Was able to get this to work.
How did you get it to work? I am having the exact same issue.
Delirium is an initial, heart pounding, interesting, as well as pleasant read.
The property of the tale, rnment stating love unsafe as well as mandating that every person have
an operation, is uncommon as well as terrifying. When Lena is reasoning through exactly what she’s been lifted to believe concerning love with
what she feels for Alex she explains it as this, “Love: a single word, a slight point, a word no bigger or longer than an edge.
Thanks a lot !
You saved me a lot of time !
Checco
Yes, I forgot my password in Oracle 11g XE and it isn’t expanding the tree with my tables because I have to enter in the password I forgot. I don’t have SQL Plus installed but I do have SQL Server 2014 that I recently installed. Should I install the SQL Plus instant client to reset the oracle 11g database? or is it possible to use the newly installed server?
if you installed XE – you have sqlplus. Open a command prompt and try this.
Also, uninstall SQL Server before it corrupts your mind 🙂
Jeff, Honestly confused about what to enter in the sql command from this thread. Plzrb.
you can launch a connection to the database as the admin – just say…
sqlplus ‘/ as sysdba’
that should connect you using your OS user account – assuming said OS user is in the admin group, which it almost always is in a std XE Windows install.
If you can’t figure this out, delete everything and install it again. This time WRITE DOWN your passwords.
Works fine! Thank you!
Hi Jeff,
thanks for the interesting post! It seems to me that the statement that you can use a client with a higher version than your database comes with a little footnote when you want the password-change functionality to work.
I tried it using Oracle 11g on the database side and installed an Oracle12-Client. The password change menu item was active, but when I clicked it, I got the message that SQL Developer misses the DLL “ocijdbc11.dll”. So it seems that there is at least a name issue there that forces you to use a client with the same major version as the database. After I installed the Oracle 11g-Client, everything worked fine.
The setup was:
Windows 7, 32 Bit
SQL Developer 32 Bit, Version 3.2.20.09
“Use OCI/thick”: unchecked
Actually, that SHOULD work, and I’m pretty sure I tested it, but the thing CAN be finicky…I’m glad you got it working though.
Found out that if you use Toad , it prompts to enter a new password when trying to login using the expired id.
So does SQL*Plus. The password reset on connect isn’t available to JDBC. We’ve hooked it up via the OCI thick connections, but would require a bit more work to catch the error and offer the password change on connect vs a right-click.
Thank you for this clear post.
Experimenting with various InstantClient versions solved the issue for me.
Also had to uncheck the OCI/Thick Driver option though.
Alternatively copy-paste in command prompt:
sqlplus /@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))(CONNECT_DATA=(SID=)))’
Lol… info between tags was deleted.
sqlplus Username/Password@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your.host.com)(PORT=1234)))(CONNECT_DATA=(SID=yourSID)))’
I finally got the Reset Password option activated, and get prompted with the password change screen. However, when I try to change it, SQL Developer closes down and… well… that’s it. I can still get into all my other DB’s where the password isn’t expired without issue.
That’s not enough information to debug what’s going on. I need more to help you.
All I can say is that it works for us, so something is different between your environment and ours.
I had the same thing. I use sqldev 3.2.20 on winx64. We use oracle 11.2.0.3 for DB so I first tried the oci 12.1 but sqldev did not like that. The reset pwd was not selected and when I tried to connect to a db, sqldev gave an error saying it was looking for the v11 dll.
Then I got the oci 11.2.0.3 version and now can connect a db. But if i check the box to use oci/thick driver and try to connect, it crashes. If I uncheck the box and connect fine. If I try to do a password reset, it crashes after entering the new pwd.
Basically, any time it uses the oci dlls, it crashes when connecting.
But the sqlplus command line client from both v11 and v12 OCI will connect to the DB and allow a password change, so good enough.
>> it crashes
It crashes how exactly? Did you get an error stack?
Nope, just poof and gone. Once it connects, the process dies and sqldev window disappears. I’m happy to repeat under some type of debugger or whatever is easy enough, but not sure where to find any logs. It does the same if run as admin too. This is win7 x64 fully patched.
Hi Jim,
Similar issues
Windows 7 x64
SQL Developer 64-bit
Java 6 64-bit
I believe the error message I got was about using a 32-bit dll on a 64-bit machine (can’t recall the exact message, but I also think it referred to oci.dll). Maybe that will help you track your problem.
Anyway, I have since started using SQL Developer 4 instead, and the above method works fine.
Hope this helps,
Liam
As a dba, I create new accounts with expired passwords, this is a common-sense policy to protect and enforce security.
However SQL Dev is not intuitive on this. Sure I know about right click, but 50% of the users do not find it.
I really would consider a huge improvement if sql developer could catch the ora exception and simply prompt for a password instead of displaying the “contact your admin” message. Changing user password is (as well as alter tablespace add datafile) one of the most boring task for a dba.
Cheers
Laurent
keeps getting better. manually changed my pw through sql plus. cant get my results to export. hung up at row 137590. hit my sql developer frustration limit for the day!
Export to…Excel? Yeah, we have a known issue with the JVM being consumed on XLS exports. You can increase the amount of memory OR you can export to CSV instead. So sorry for all the headaches today.
yes, i have use oci/thick checked in preferences>database>advanced
rebooted. no dice. running 32.
I have it listed very first. does my path definition look correct?
You installed a 32 bit or 64 bit client to ‘c:\oracle\ora112’ ? You don’t need the \bin, but that shouldn’t hurt anything. I don’t think it’s necessary but a restart of SQLDev and/or Windows might be in order.
When you attempt to connect, do you get any error messages in SQL Developer? You have set the Advanced OCI option to ‘true,’ yes?
Jeff,
I am having a hard time getting path setup so i can use reset pw. My oci.dll is located at c:\oracle\ora112\bin. I created a path c:\oracle\ora112\bin\oci.dll and restarted sql developer. pw reset is still grayed out.
When you say you ‘created a path’ – do you mean you modified the EXISTING path environment variable on your Windows machine? If so, make sure it’s listed at the front of the directory string so it’s found first.
Thanks for the tip and help Jeff!
In my case it appears the Installer actually set the PATH correctly. Will wonders never cease.
It is nice when things work, even if it does surprise us 😉
I have a write-up on installing the Instant Client on Ubuntu. It’s the first half of http://maxolasersquad.blogspot.com/2011/04/cxoracle-on-ubuntu-1104-natty.html
Thanks for sharing! Ubuntu is a pretty popular Linux distribution, so I’m sure this will come in handy 🙂