Change the expired state of an Oracle user

In Oracle, the state of the regular user becomes locked, expired, and so on, how to deal with this situation?

First, if the locked state is OK, the DBA executes the ALTER user Scott account unlock directly.

However, if it becomes a expired state, Oracle itself does not provide an unlocked statement, because the account expires, the user must change the password, the account can be reused. But sometimes, because of various reasons, we do not know what the plaintext of the original password, this time is very troublesome, after the study found that there are two ways to achieve:

One. Change your password with the redaction of the original password:

[SQL] View plaincopyprint?
01.sql>conn/as SYSDBA
02.sql>select password from dba_users where username= ' SCOTT ';
03.password
------------------------------.
05.e65e6af62b2449cf
06.
07.sql>alter user SCOTT identified by values ' E65E6AF62B2449CF ';
Sql>conn/as SYSDBA
Sql>select password from dba_users where username= ' SCOTT ';
Password
------------------------------
E65e6af62b2449cf

Sql>alter user SCOTT identified by values ' E65E6AF62B2449CF ';

In this case, although we do not know what the original password is, but can use its redaction change the password, so that in the case of not knowing the original password, both keep the password unchanged, but also can change the state of expired.

Two. The following statement is done, regardless of the user's State:

[SQL] View plaincopyprint?
01.UPDATE user$ SET astatus=0 WHERE name= ' SCOTT ';
UPDATE user$ SET astatus=0 WHERE name= ' SCOTT ';

Explanation of principle:
User information is present in a system table such as user$

[SQL] View plaincopyprint?
01.sql> Select Name,astatus,password from user$ where name in (' SYS ', ' SCOTT ');
02.
03.
04.NAME Astatus PASSWORD
----------------------------------------------------------------------.
06.SCOTT 1 E65E6AF62B2449CF
07.SYS 0 8a8f025737a9097a
08.
09.sql> Select Username,account_status from Dba_users where username in (' SYS ', ' SCOTT ');
10.
11.USERNAME Account_status
--------------------------------------------------------------.
13.SYS OPEN
14.SCOTT EXPIRED
Sql> Select Name,astatus,password from user$ where name in (' SYS ', ' SCOTT ');

NAME Astatus PASSWORD
------------------------------ ---------- ------------------------------
SCOTT 1 E65E6AF62B2449CF
SYS 0 8a8f025737a9097a

Sql> Select Username,account_status from Dba_users where username in (' SYS ', ' SCOTT ');

USERNAME Account_status
------------------------------ --------------------------------
SYS OPEN
SCOTT EXPIRED

and the user astatus corresponding table is: User_astatus_map

[SQL] View plaincopyprint?
01.sql> select * from User_astatus_map;
02.
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
Ten EXPIRED (GRACE) & LOCKED
14.
15.9 rows selected.
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
Ten EXPIRED (GRACE) & LOCKED

9 rows selected.

The key is that the user$ table itself can be changed, so at this point we can directly change the user's Astatus field to 0.

[SQL] View plaincopyprint?
01.sql> UPDATE user$ SET astatus=0 WHERE name= ' SCOTT ';
02.1 Row updated.
03.
04.sql> COMMIT;
05.Commit complete.
06.
07.sql> alter system flush Shared_pool;
08.System altered.
sql> UPDATE user$ SET astatus=0 WHERE name= ' SCOTT ';
1 row updated.

Sql> COMMIT;
Commit complete.

Sql> alter system flush Shared_pool;
System altered.

Again to observe the result:

[SQL] View plaincopyprint?
01.sql> Select Name,astatus,password from user$ where name in (' SYS ', ' SCOTT ');
02.
03.NAME Astatus PASSWORD
----------------------------------------------------------------------.
05.SCOTT 0 E65E6AF62B2449CF
06.SYS 0 8a8f025737a9097a
07.
08.sql> Select Username,account_status from Dba_users where username in (' SYS ', ' SCOTT ');
09.
10.USERNAME Account_status
--------------------------------------------------------------.
12.SCOTT OPEN
13.SYS OPEN
Sql> Select Name,astatus,password from user$ where name in (' SYS ', ' SCOTT ');

NAME Astatus PASSWORD
------------------------------ ---------- ------------------------------
SCOTT 0 E65E6AF62B2449CF
SYS 0 8a8f025737a9097a

Sql> Select Username,account_status from Dba_users where username in (' SYS ', ' SCOTT ');

USERNAME Account_status
------------------------------ --------------------------------
SCOTT OPEN
SYS OPEN

Change the expired state of an Oracle user