Monday, April 29, 2013

Oracle ORA-28001: the password has expired

Just happened today on an new Oracle Build.

In Oracle Database, A remote DBA face this because password have reached 180 Default limit for Password life time.
The life of a password is defined as 180 days by default.

To check which user got Expired password:

select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%';


First connect as DBA:

$ sqlplus / as sysdba


To unlock an user:
SQL> alter user [user_name] ACCOUNT UNLOCK;


To reset the password:
SQL> alter user [user_name] identified by [password];


To Keep same Password:

SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: scott
old 3: where d.username = upper('&&username')
new 3: where d.username = upper('scott')


C
--------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';


SQL> alter user "SCOTT" identified by values 'F894844C34402B67';


On a production system, has to change this limit to unlimited to solve this and to avid this in future.

ALTER PROFILE DEFAULT LIMIT password_life_time UNLIMITED;

3 comments:

  1. Oracle ORA-28001: the password has expired | Million Dollar Server

    ReplyDelete
  2. I precisely had to say thanks once again. I do not know the things that I would have achieved in the absence of the type of concepts discussed by you directly on such situation. It actually was a very horrifying difficulty in my view, but noticing a new skilled approach you managed it made me to jump over delight. I am just happier for your work as well as believe you really know what an amazing job you have been accomplishing training the others all through your blog. Probably you haven't met any of us.

    ReplyDelete
  3. Thanks for sharing your thoughts on kzn a ru. Regards

    ReplyDelete