Solution for Oracle Database User Account being expired

Solution for Oracle Database User Account being expired

The Oracle database account has expired. You must change the password before you can use it again.

But sometimes, for various reasons, we do not know what the plain text of the original password is, but we often cannot modify the existing password. Fortunately, we can use the original password to change the password.

In 11G, dba_users.password no longer displays the user's password:

SQL> select username, ACCOUNT_STATUS from dba_users where username in ('system', 'Scott ');

USERNAME ACCOUNT_STATUS

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

SCOTT OPEN

SYSTEM EXPIRED

SQL> select password from dba_users where username in ('system', 'Scott ');

PASSWORD

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

In the system table user $, you can view the user's PASSWORD and status (ASTATUS)

SQL> select user #, name, password, astatus from user $ where name in ('system', 'Scott ');

USER # NAME PASSWORD ASTATUS

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

83 SCOTT F894844C34402B67 0

5 SYSTEM 2D594E86F93B17A1 1

SQL>

You can use the previous query result to change the password of an expired user:

SQL> alter user system identified by values '2d594e86f93b17a1 ';

User altered.

SQL> select user #, name, password, astatus from user $ where name in ('system', 'Scott ');

USER # NAME PASSWORD ASTATUS

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

83 SCOTT F894844C34402B67 0

5 SYSTEM 2D594E86F93B17A1 0

SQL>

In fact, you can also directly modify the table user $ field ASTATUS to 0, so that the user account becomes open.
The meaning of the value corresponding to ASTATUS can be viewed through user_astatus_map.

SQL> select * from user_astatus_map;

STATUS # STATUS

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

0 OPEN

1 EXPIRED

2 EXPIRED (GRACE)

4 LOCKED (TIMED)

8 LOCKED

5 EXPIRED & LOCKED (TIMED)

6 EXPIRED (GRACE) & LOCKED (TIMED)

9 EXPIRED & LOCKED

10 EXPIRED (GRACE) & LOCKED

9 rows selected.

SQL>