• How To Set Oracle Environment Variables In Linux
• What Is Oracle_home

Many of my users do not use SQLPlus. I cannot give them alter user. We expire passwords every 60 days.

Aug 1, 2011 - The sqlnames.ora file is where the Oracle Client stores various. Locate the folder where you installed the Oracle Client (ORACLEHOME). Either install the sqlnet.fdf file in $ORACLEHOME/network/admin or turn off tracing in your ORA file. ORA-12210: TNS:error in finding Navigator data.

I can't get the SQLPlus command 'password' to work in SQL developer.

1. When I hit run, I get an invalid command error
2. When I hit run script, nothing happens.

I don't want to write them a package to change their passwords since we have a lot of databases. Do I have a better option?

Tiago MussiBobBob

14 Answers

The correct syntax for updating the password using SQL Developer is:

alter user user_name identified by 'new_password' replace 'old_password' ;

You can check more options for this command here: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4003.htm#i2058207

Jon HellerAmerico SavinonAmerico Savinon

In an SQL worksheet:

type in 'password' (without the quotes)Highlight, hit CTRL+ENTER.Password change screen comes up.

Igor Pashchuk

SQL Developer has a built-in reset password option that may work for your situation. It requires adding Oracle Instant Client to the workstation as well. When instant client is in the path when SQL developer launches you will get the following option enabled:

Oracle Instant Client does not need admin privileges to install, just the ability to write to a directory and add that directory to the users path. Most users have the privileges to do this.

Recap: In order to use Reset Password on Oracle SQL Developer:

1. You must unpack the Oracle Instant Client in a directory
2. You must add the Oracle Instant Client directory to the users path
3. You must then restart Oracle SQL Developer

At this point you can right click a data source and reset your password.

See http://www.thatjeffsmith.com/archive/2012/11/resetting-your-oracle-user-password-with-sql-developer/ for a complete walk-through

Also see the comment in the oracle docs: http://docs.oracle.com/cd/E35137_01/appdev.32/e35117/dialogs.htm#RPTUG41808

How To Set Oracle Environment Variables In Linux

An alternative configuration to have SQL Developer (tested on version 4.0.1) recognize and use the Instant Client on OS X is:

1. Set path to Instant Client in Preferences -> Database -> Advanced ->Use Oracle Client
2. Verify the Instance Client can be loaded succesfully using the Configure... -> Test... options from within the preferences dialog

3. (OS X) Refer to this question to resolve issues related to DYLD_LIBRARY_PATH environment variable. I used the following command and then restarted SQL Developer to pick up the change:

   $ launchctl setenv DYLD_LIBRARY_PATH /path/to/oracle/instantclient_11_2

Your users can still change their passwords by 'alter user onlyTheirUserName identified by newpassword'. They do not have to have the ALTER USER privilege to change their own password.

Depending on the admin settings, you may have to specify your old password using the REPLACE option

Jon Heller

To make it a little clear :

If the username: abcdef and the old password : a123b456, new password: m987n654

alter user abcdef identified by m987n654 replace a123b456;

Brad Bruce

I confirmed this works in SQL Developer 3.0.04. Our passwords are required to have a special character, so the double-quoted string is needed in our case. Of course, this only works if the password has not already expired and you are currently logged in.

Dominik Honnef

I realise that there are many answers, but I found a solution that may be helpful to some. I ran into the same problem, I am running oracle sql develop on my local computer and I have a bunch of users. I happen to remember the password for one of my users and I used it to reset the password of other users.

Steps:

1. connect to a database using a valid user and password, in my case all my users expired except 'system' and I remember that password

2. find the 'Other_users' node within the tree as the image below displays

3.within the 'Other_users' tree find your users that you would like to reset password of and right click the note and select 'Edit Users'

4.fill out the new password in edit user dialog and click 'Apply'. Make sure that you have unchecked 'Password expired (user must change next login)'.

And that worked for me, It is not as good as other solution because you need to be able to login to at least one account but it does work.

The built-in reset password option may not work for user. In this case the password can be reset using following SQL statement:

speeday125

you can find the user in DBA_USERS table like

Now go to the sys/system (administrator) and use query

To verify the account status just go through

and you can see status of your user.

You can now do this in SQL Developer 4.1.0.17, no PL/SQL required, assuming you have another account that has administrative privileges:

1. Create a connection to the database in SQL Developer 4.1.0.17 with an alternative administrative user
2. Expand the 'Other Users' section once connected, and right-click the user whose password has expired. Choose 'Edit User'.
3. Uncheck the 'Password Expired...' checkbox, type in a new password for the user, and hit 'Save'.
4. Job done! You can test by connecting with the user whose password had expired, to confirm it is now valid again.

Fuzzy AnalysisFuzzy Analysis

There is another way to reset the password through command prompt ...

1) Go to the Oracle Database Folder ( In my case Oracle Database 11g Express Edition) in the START MENU.

2) Within that folder click 'Run SQL Commandline'

3) Type 'connect username/password' (your username and old password without the quotation marks)

4) The message displayed is ...

ERROR:ORA-28001: the password has expired

Changing password for hr

--> New password:

5) Type the new password

6) Retype the new password

7) Message displayed is ...

Password changedConnected.

SQL>

8) GO TO Sql developer --> type the new password --> connected

One note for people who might not have the set password for sysdba or sys and regularly use a third party client.Here's some info about logging into command line sqlplus without a password that helped me.I am using fedora 21 by the way.

In my case, sqlplus is located here:

Now run

Now you need to connect to database with your old credentials.You can find Oracle provided template in your output:

In my case I have user 'oracle' with password 'oracle' so my input looks like

Done. Now type your new password twice.Then if you don't want your password to expire anymore you could run

What Is Oracle_home

Try this solution if the Reset Password option (of SQL Developer) did not work:

Step 1: Open Run SQL Command Line (from the start menu, which comes with SQL Developer installation package)

Step 2: Run the following commands:

Note: If password has already expired, Changing password for <user> option will automatically come.

LeoLeo

protected by Community♦Sep 29 '16 at 14:29

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?

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

Many of my users do not use SQLPlus. I cannot give them alter user. We expire passwords every 60 days.

I can't get the SQLPlus command 'password' to work in SQL developer.

1. When I hit run, I get an invalid command error
2. When I hit run script, nothing happens.

I don't want to write them a package to change their passwords since we have a lot of databases. Do I have a better option?

Tiago MussiBobBob

14 Answers

The correct syntax for updating the password using SQL Developer is:

alter user user_name identified by 'new_password' replace 'old_password' ;

You can check more options for this command here: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4003.htm#i2058207

Jon HellerAmerico SavinonAmerico Savinon

In an SQL worksheet:

type in 'password' (without the quotes)Highlight, hit CTRL+ENTER.Password change screen comes up.

Igor Pashchuk

SQL Developer has a built-in reset password option that may work for your situation. It requires adding Oracle Instant Client to the workstation as well. When instant client is in the path when SQL developer launches you will get the following option enabled:

Oracle Instant Client does not need admin privileges to install, just the ability to write to a directory and add that directory to the users path. Most users have the privileges to do this.

Recap: In order to use Reset Password on Oracle SQL Developer:

1. You must unpack the Oracle Instant Client in a directory
2. You must add the Oracle Instant Client directory to the users path
3. You must then restart Oracle SQL Developer

At this point you can right click a data source and reset your password.

See http://www.thatjeffsmith.com/archive/2012/11/resetting-your-oracle-user-password-with-sql-developer/ for a complete walk-through

Also see the comment in the oracle docs: http://docs.oracle.com/cd/E35137_01/appdev.32/e35117/dialogs.htm#RPTUG41808

An alternative configuration to have SQL Developer (tested on version 4.0.1) recognize and use the Instant Client on OS X is:

1. Set path to Instant Client in Preferences -> Database -> Advanced ->Use Oracle Client
2. Verify the Instance Client can be loaded succesfully using the Configure... -> Test... options from within the preferences dialog

3. (OS X) Refer to this question to resolve issues related to DYLD_LIBRARY_PATH environment variable. I used the following command and then restarted SQL Developer to pick up the change:

   $ launchctl setenv DYLD_LIBRARY_PATH /path/to/oracle/instantclient_11_2

Your users can still change their passwords by 'alter user onlyTheirUserName identified by newpassword'. They do not have to have the ALTER USER privilege to change their own password.

Depending on the admin settings, you may have to specify your old password using the REPLACE option

Jon Heller

To make it a little clear :

If the username: abcdef and the old password : a123b456, new password: m987n654

alter user abcdef identified by m987n654 replace a123b456;

Brad Bruce

I confirmed this works in SQL Developer 3.0.04. Our passwords are required to have a special character, so the double-quoted string is needed in our case. Of course, this only works if the password has not already expired and you are currently logged in.

Dominik Honnef

I realise that there are many answers, but I found a solution that may be helpful to some. I ran into the same problem, I am running oracle sql develop on my local computer and I have a bunch of users. I happen to remember the password for one of my users and I used it to reset the password of other users.

Steps:

1. connect to a database using a valid user and password, in my case all my users expired except 'system' and I remember that password

2. find the 'Other_users' node within the tree as the image below displays

3.within the 'Other_users' tree find your users that you would like to reset password of and right click the note and select 'Edit Users'

4.fill out the new password in edit user dialog and click 'Apply'. Make sure that you have unchecked 'Password expired (user must change next login)'.

And that worked for me, It is not as good as other solution because you need to be able to login to at least one account but it does work.

The built-in reset password option may not work for user. In this case the password can be reset using following SQL statement:

speeday125

you can find the user in DBA_USERS table like

Now go to the sys/system (administrator) and use query

To verify the account status just go through

and you can see status of your user.

You can now do this in SQL Developer 4.1.0.17, no PL/SQL required, assuming you have another account that has administrative privileges:

1. Create a connection to the database in SQL Developer 4.1.0.17 with an alternative administrative user
2. Expand the 'Other Users' section once connected, and right-click the user whose password has expired. Choose 'Edit User'.
3. Uncheck the 'Password Expired...' checkbox, type in a new password for the user, and hit 'Save'.
4. Job done! You can test by connecting with the user whose password had expired, to confirm it is now valid again.

Fuzzy AnalysisFuzzy Analysis

There is another way to reset the password through command prompt ...

1) Go to the Oracle Database Folder ( In my case Oracle Database 11g Express Edition) in the START MENU.

2) Within that folder click 'Run SQL Commandline'

3) Type 'connect username/password' (your username and old password without the quotation marks)

4) The message displayed is ...

ERROR:ORA-28001: the password has expired

Changing password for hr

--> New password:

5) Type the new password

6) Retype the new password

7) Message displayed is ...

Password changedConnected.

SQL>

8) GO TO Sql developer --> type the new password --> connected

One note for people who might not have the set password for sysdba or sys and regularly use a third party client.Here's some info about logging into command line sqlplus without a password that helped me.I am using fedora 21 by the way.

In my case, sqlplus is located here:

Now run

Now you need to connect to database with your old credentials.You can find Oracle provided template in your output:

In my case I have user 'oracle' with password 'oracle' so my input looks like

Done. Now type your new password twice.Then if you don't want your password to expire anymore you could run

Try this solution if the Reset Password option (of SQL Developer) did not work:

Step 1: Open Run SQL Command Line (from the start menu, which comes with SQL Developer installation package)

Step 2: Run the following commands:

Note: If password has already expired, Changing password for <user> option will automatically come.

LeoLeo

protected by Community♦Sep 29 '16 at 14:29

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?

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