Tag: SYS.USER$

How to Reset User Expired Password to the Original Password

We want to keep using the old password due to application restraints.

SQL>  alter user TESTUSER identified by "Testuser#Password123";
 alter user TESTUSER identified by "Testuser#Password123"
*
ERROR at line 1:
ORA-28007: the password cannot be reused

Check profile DEFAULT resource limits:

DEFAULT    PASSWORD_REUSE_MAX               UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME              UNLIMITED
SQL>  alter user TESTUSER profile default;
SQL> select  NAME, SPARE4 FROM SYS.USER$ WHERE NAME ='TESTUSER';

NAME            SPARE4
--------------- ------------------------------------------------
TESTUSER S:E75D474EB09C5E5222BAC532E6F7595BF3A4D7EE6927E615860872DE1892;T:C53F84BF7BF4D1D865D31E7D864B6FC732CF6CB0FE23DD0E780221D514BF05B663FF20B504DBDF2A17A4F82B0376E09C245E7F915BDFAB1FD6E841B21080774321AE15C9FF5A87A9C2E660E82789AC04
SQL>  alter user TESTUSER identified by values 'S:E75D474EB09C5E5222BAC532E6F7595BF3A4D7EE6927E615860872DE1892;T:C53F84BF7BF4D1D865D31E7D864B6FC732CF6CB0FE23DD0E780221D514BF05B663FF20B504DBDF2A17A4F82B0376E09C245E7F915BDFAB1FD6E841B21080774321AE15C9FF5A87A9C2E660E82789AC04';

User altered.

SQL> connect testuser
Enter password: Testuser#Password123 
Connected.
SQL>

How to Know When Oracle User Password Changed?

Oracle database internal table SYS.USER$ contains useful information about users and roles. Some of the columns have got following meaning:

  • NAME – name for user or role
  • TYPE# – 0 for role or 1 for user
  • CTIME – the date of creation
  • PTIME – the date the password was last changed
  • EXPTIME – the date the password has last expired
  • LTIME – the date the resource was last locked
  • LCOUNT – number of failed logon
SQL> SELECT
        name,
       ctime,
       ptime
FROM
      sys.user$
WHERE
      name = 'SYS';

NAME                 CTIME     PTIME
-------------------- --------- ---------
SYS                  31-MAR-16 11-MAY-21