【原创】手工给用户解锁

可以通过操作user$解锁锁定的用户（实际意义并不大，呵呵，唯一想到的就是如果一个用户如果有update user$的权限，即使没有altre user的权限也可以解锁用户），下面是实验步骤：

没有锁定的用户的ASTATUS值是0

sys@APPROD>select name,astatus from user$ where name='TEST';

NAME                                                            ASTATUS
------------------------------------------------------------ ----------
TEST                                                                  0

sys@APPROD>alter user test account lock;

User altered.

锁定用户的ASTATUS值是8
sys@APPROD>select name,astatus from user$ where name='TEST';

NAME                                                            ASTATUS
------------------------------------------------------------ ----------
TEST                                                                  8

直接将8改为0
sys@APPROD>update user$ set astatus=0 where name='TEST';

1 row updated.

sys@APPROD>commit;

Commit complete.

sys@APPROD>select name,astatus from user$ where name='TEST';

NAME                                                            ASTATUS
------------------------------------------------------------ ----------
TEST                                                                  0

可以看到用户还是不能登录，这是因为dba_users数据字典信息已经缓存在字典缓存中了，刷一下shared pool就可以了。推测alter user account lock/unlock执行后会自动刷新一下字典缓存，而直接update user$是不会有这个步骤的
idle>conn test/test@approd.test
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

sys@APPROD>alter system flush shared_pool;

System altered.

idle>conn test/test@approd.test
Connected.

test@APPROD>

附录：ASTATUS的可取值可以通过下面的查询知道：
sys@APPROD>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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/498744/viewspace-348213/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/498744/viewspace-348213/