Programmatically Change Expired Oracle DB Password?

Where I work, some databases get copied down from our production environment to our test environment, but the DBAs set all the passwords to expired on the (new) test DB right after the copy so that the production passwords are not known. So if I run 开发者_开发百科sqlplus and connect to the test db with a specific username it immediately prompts me for a new password.

Is there a way via a java app or shell scripting to automate the changing of an expired oracle 10g database password for a specific user?

On Unix

If you're on unix, you can do with a shell script.

I have tested it like so:

drop user foo cascade;
create user foo identified by old_password password expire;
grant create session to foo;
exit

Now use this little scriptlet:

cat <<DOG | sqlplus foo/old_password
    new_password
    new_password
    exit
DOG

and change the password with the scriptlet

sh change_expired_password.sh

Then, the password is changed and it can be connected to the instance:

sqlplus foo/new_password@ORCL

Obviously, you would put the cat ... construct in a shell script that suits you.

On Windows

On Windows you could go with a batch file like so

@(
  echo new_password
  echo new_password
) | sqlplus foo/old_password@ORCL

I assume the problem you have is with ORA-28001: the password has expired. It is usually an effect of ALTER USER unittest PASSWORD EXPIRE or simply of the password expired because of the user's profile setting. This is rather tricky situation.

When you connect with SQL*Plus it offers you a prompt asking for the new password and then sets the new passwod like this:

jxa@ub16a|2014$ sqlplus unittest/unittest@//localhost/orclpdb1

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 22 13:06:04 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

Changing password for unittest
New password: 
Retype new password: 
Password changed

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
UNITTEST@ORCLCDB> 

But with JDBC or cx_Oracle this exception leaves you with dead connection and you cannot use it to issue ALTER USER xx IDENTIFIED BY yy REPLACE zz.

So what you need to do is drop the existing connection and reconnect once again with a magic setting. The magic for JDBC is setting OCINewPassword connection property to the new password. Just google for the OCINewPassword and it gets you to examples.

With Python's both cx_Oracle.connect and cx_Oracle.Connection have newpassword= argument which allows to change programmatically the password when the current one is reported expired.

In both cases it is enough to connect with the old password and setting the OCINewPassword (JDBC) or newpassword (Python). It results with the working connection and the user having the password changed to the new one.

This way it opens ways to automate your passwords change for expired oracle users.