Resetting Your Oracle User Password with SQL Developer

thatjeffsmith SQL Developer 67 Comments

Tell Others About This Story:

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.

Or watch the movie.

If you INSIST on staying on your 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:

Right click on my connection, reset password not available! Why not?

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.

Either of these is fine, but mind the bit level and version of Oracle!

Make sure you get a 32 bit Instant Client if you run 32 bit SQL Developer or 64 bit if you run 64

Here’s the download link

What, you didn’t believe me?

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.

I recommend you put it up front so we find our DLLs first.

Now with that set, let’s start up SQL Developer.

Check the Connection Context menu again

Bingo!

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.

Not necessary, but won’t hurt anything either.

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?

Oh yeah, my password has expired…

Right click on your connection and now choose ‘Reset Password’

You’ll need to know your existing password and select a new one that meets your databases’s security standards.

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.’

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 67

  1. If your account is not already locked or expired and you’re able to Connect to your instance, the ‘password’ command just like you would do in SQL*Plus:

    Open a SQL Worksheet.
    Type: password
    Double-Click to highlight ‘password’
    Click on the ‘Run Statement’ icon (green arrow).

    The ‘New password’ window will open for your current account login and prompt you to enter your “Old password” followed by your “New password” twice.

    1. thatjeffsmith Post
      Author
  2. I tried this a few times, but it silently fails (i.e. it looks like the pw reset works, but in fact it doesn’t do anything). Any idea how to figure out why it’s not working?

  3. Hi thatjeffsmith! πŸ™‚
    I have to reinstall multiple copies of PL SQL v 7.0.2.1076 on upgraded PC’s

    Is there any way I can pre-install one copy of the the software with my Windows 10 Image then add the individual product codes and serial numbers as I deploy those imaged machines? (they have several 5 license code/serial combinations to be applied)
    Best regards – Col. Sanders … not *** the*** Colonel Sanders πŸ™

    1. thatjeffsmith Post
      Author

      Very easy, just unzip Oracle SQL Developer to their desktops. There’s no licensing mechanism, because the tool is a no-cost feature of your Database. Or in other words, it’s free.

  4. I am using unix box with oracle instance client vesion “instantclient-basiclite-linux.x64-12.1.0.2.0.zip” and “instantclient-basiclite-linux.x64-11.2.0.3.0.zip”, I can changed a expired password for oracle 12c, 11.2.0.4,11.2.0,3 db user, But it is not working if any password changes(expire case only) from oracle 11.2.0.2 and getting “ORA-01017: invalid username/password; logon denied” while i am sure i am passing correct password

    Any one have similar issue , I tried to add 11.2.0.2 library to my program same a other one but did not get success, Using java and OCI driver (Thik driver)

    1. thatjeffsmith Post
      Author
      1. Thanks Jeff for response , I want little bit more details about driver support. can you please share more details about “sqldev 413 isn’t compatible with a 11202 thick ”

        I am already using higher version 12.1.0.2 and 11.2.0.3 for my program and it is working good for all oracle version 12c, 11204,11203 except 11202 only and getting always error “ORA-01017: invalid username/password” please suggest .

    1. thatjeffsmith Post
      Author
  5. 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

    1. thatjeffsmith Post
      Author
  6. 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

    1. thatjeffsmith Post
      Author
  7. 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

    1. 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.

  8. 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)

  9. 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.

    1. thatjeffsmith Post
      Author
      1. 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) ?

  10. 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 !!

    1. thatjeffsmith Post
      Author
      1. 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.

        1. 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.

          1. thatjeffsmith Post
            Author

            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?

          2. Yes. We have another process that changes all our passwords in the database. I just needed to update my saved passwords in SQLDev.

  11. 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

    1. thatjeffsmith Post
      Author
  12. 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.

    1. thatjeffsmith Post
      Author
  13. 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!

    1. thatjeffsmith Post
      Author
      1. 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!

  14. 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.

  15. 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?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          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.

  16. 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

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      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.

  17. 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=)))’

    1. 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)))’

  18. 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.

    1. thatjeffsmith Post
      Author

      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.

    2. 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.

      1. thatjeffsmith Post
        Author
        1. 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.

          1. 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

  19. 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

  20. 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!

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author

      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?

  21. 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.

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *