oracle : the password has expired

http://www.dbas-oracle.com/2011/06/ora-28001-password-has-expired.html

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

1) -- CHECK PASSWORD_LIFE_TIME, SHOULD BE 180 BY DEFAULT
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

2) -- CHANGE IT TO UMLIMITED
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

3) -- GEN SCRIPT TO UPDATE PASSWORD AS THE CURRENT PASSWORD
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;  

4) -- UNLOCK THE USER
alter user CPADEMO account unlock;

5) -- CHECK THE USER, IT SHOULD BE OPNE NOW.
select username,account_status from dba_users;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Solutions:

1.  Connect to database using sys users.

2. Execute the following query

Sql > select * from dba_profiles;

the output of this query will be like.

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
MONITORING_PROFILE   COMPOSITE_LIMIT           KERNEL   DEFAULT
DEFAULT              COMPOSITE_LIMIT           KERNEL   UNLIMITED
MONITORING_PROFILE   SESSIONS_PER_USER         KERNEL   DEFAULT
DEFAULT              SESSIONS_PER_USER         KERNEL   UNLIMITED
MONITORING_PROFILE   CPU_PER_SESSION           KERNEL   DEFAULT
DEFAULT              CPU_PER_SESSION           KERNEL   UNLIMITED
MONITORING_PROFILE   CPU_PER_CALL              KERNEL   DEFAULT
DEFAULT              CPU_PER_CALL              KERNEL   UNLIMITED
MONITORING_PROFILE   LOGICAL_READS_PER_SESSION KERNEL   DEFAULT
DEFAULT              LOGICAL_READS_PER_SESSION KERNEL   UNLIMITED
MONITORING_PROFILE   LOGICAL_READS_PER_CALL    KERNEL   DEFAULT

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
DEFAULT              LOGICAL_READS_PER_CALL    KERNEL   UNLIMITED
MONITORING_PROFILE   IDLE_TIME                 KERNEL   DEFAULT
DEFAULT              IDLE_TIME                 KERNEL   UNLIMITED
MONITORING_PROFILE   CONNECT_TIME              KERNEL   DEFAULT
DEFAULT              CONNECT_TIME              KERNEL   UNLIMITED
MONITORING_PROFILE   PRIVATE_SGA               KERNEL   DEFAULT
DEFAULT              PRIVATE_SGA               KERNEL   UNLIMITED
MONITORING_PROFILE   FAILED_LOGIN_ATTEMPTS     PASSWORD UNLIMITED
DEFAULT              FAILED_LOGIN_ATTEMPTS     PASSWORD 10
MONITORING_PROFILE   PASSWORD_LIFE_TIME        PASSWORD DEFAULT
DEFAULT              PASSWORD_LIFE_TIME        PASSWORD 180

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
MONITORING_PROFILE   PASSWORD_REUSE_TIME       PASSWORD DEFAULT
DEFAULT              PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
MONITORING_PROFILE   PASSWORD_REUSE_MAX        PASSWORD DEFAULT
DEFAULT              PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
MONITORING_PROFILE   PASSWORD_VERIFY_FUNCTION  PASSWORD DEFAULT
DEFAULT              PASSWORD_VERIFY_FUNCTION  PASSWORD NULL
MONITORING_PROFILE   PASSWORD_LOCK_TIME        PASSWORD DEFAULT
DEFAULT              PASSWORD_LOCK_TIME        PASSWORD 1
MONITORING_PROFILE   PASSWORD_GRACE_TIME       PASSWORD DEFAULT
DEFAULT              PASSWORD_GRACE_TIME       PASSWORD 7

32 rows selected.

Here PASSWORD_LIFE_TIME field is responsible for expiring of password after 180 days.

3.  execute following command to disable this feature:

Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

4. Now crosscheck for disabling of this feature.

Sql > select * from dba_profiles;

The value in PASSWORD_LIFE_TIME has changed to unlimited. Now password will never expire.

5. Now reset the password of locked user. Here are two options to reset password. Either you can keep same password or you can give a new 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';

To give a new Password:

sql> alter user [user_name] identified by [password];

6. Unlock user account using below command

sql> alter user [User_name] account unlock;

7. Crosscheck by value of accout_status field in dba_users view.

sql> select username,account_status from dba_users;

The value of account_status filed should by "OPEN" for corresponding user.