To Force User Password Expire In Oracle Database

As DBA we can force database users to renew their password by using the following command (ex. user “testing” should change his password):

oracle@mytestbed:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 6 12:06:53 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER USER testing PASSWORD EXPIRE;

User altered.

SQL> QUIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
oracle@mytestbed:~$

Whenever user “testing” try to login using his password, he will get the error message that forced him to change his password :

oracle@mytestbed:~$ sqlplus testing/testing123

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 6 12:07:01 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> QUIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
oracle@mytestbed:~$

Try once again to login using the new password (on example above the new password is “testing”):

oracle@mytestbed:~$ sqlplus testing/testing

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 6 12:07:16 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

2 thoughts on “To Force User Password Expire In Oracle Database

  1. This may be an old post, but i’ll reply anyway. A Lot of times when users are forced to change their password, and they use sqlplus command line as their client they can’t login. It allows to login but then when trying to change password it says invalid password login denied. No matter what they do it won’t allow them to login. If we remove the force login then the user can login and change their password. I have seen this on more than one occasion on different oracle servers at different companies. Any ideas?
    Thanks

  2. it should says it won’t allow them to change their password, they can login but they can’t get passed the password prompt.

Leave a Reply