Ok, this is a common issue, once it’s like the default behavior of oracle for DEFAULT profile, used as DEFAULT to new users. But it’s really easy to solve. 🙂
Before expiring you should be received some:
ORA-28002: the password will expire within x days
Ok then, but what if you are facing errors to reuse the same password, don’t know the password and yet, how to never let it happen again?
Just create a new profile and adjust those limits. Here it goes:
SQL> col username for a35
col account_status for a20
col profile for a20
select username, account_status, created, lock_date, EXPIRY_DATE,temporary_tablespace, profile, default_tablespace from dba_users where username like upper('%&username%')
order by username asc;
SQL> SQL> SQL> 2 3 4
Enter value for username: TESTUSER
old 2: where username like upper('%&username%')
new 2: where username like upper('%TESTUSER%')
USERNAME ACCOUNT_STATUS CREATED LOCK_DATE
----------------------------------- -------------------- --------- ---------
EXPIRY_DA TEMPORARY_TABLESPACE PROFILE
--------- ------------------------------ --------------------
DEFAULT_TABLESPACE
------------------------------
TESTUSER EXPIRED(GRACE) 30-OCT-15
06-OCT-16 TEMP DEFAULT
USERS
SQL> password
Changing password for TESTUSER
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20011: Password should differ from the \
old password by at least 3 characters
Password unchanged
SQL> create profile TESTUSER limit PASSWORD_LIFE_TIME unlimited;
Profile created.
SQL> ALTER PROFILE TESTUSER limit PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_GRACE_TIME UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_REUSE_TIME UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_REUSE_MAX UNLIMITED;
ALTER PROFILE TESTUSER limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_LOCK_TIME UNLIMITED;
Profile altered.
SQL>
Profile altered.
SQL>
Profile altered.
SQL>
Profile altered.
SQL>
Profile altered.
SQL>
Profile altered.
SQL> alter user TESTUSER profile TESTUSER;
User altered.
SQL> select password from sys.user$ where name='TESTUSER';
PASSWORD
------------------------------
402008CF7D4974F3
SQL> ALTER user TESTUSER identified by values '402008CF7D4974F3';
User altered.
SQL> col username for a35
col account_status for a20
col profile for a20
select username, account_status, created, lock_date, EXPIRY_DATE,temporary_tablespace, profile, default_t ablespace from dba_users
where username like upper('%&username%')
order by username asc
/SQL> SQL> SQL> 2 3 4
Enter value for username: TESTUSER
old 2: where username like upper('%&username%')
new 2: where username like upper('%TESTUSER%')
USERNAME ACCOUNT_STATUS CREATED LOCK_DATE
----------------------------------- -------------------- --------- ---------
EXPIRY_DA TEMPORARY_TABLESPACE PROFILE
--------- ------------------------------ --------------------
DEFAULT_TABLESPACE
------------------------------
TESTUSER OPEN 30-OCT-15
01-APR-17 TEMP DEFAULT
USERS
