为保证安全,本站点已取消所有百度云分享链接,如有下载需要,请在评论里留下邮箱。
Oracle数据库查看用户状态
一、当前ORACLE用户的状态可查看视图DBA_USERS;一般情况下在使用的正常用户均处于OPEN状态。
SQL> select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
--------------------- ------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN OPEN
DEMO OPEN
OUTLN EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
ANONYMOUS EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
DIP EXPIRED & LOCKED
ORACLE_OCM EXPIRED & LOCKED
TSMSYS EXPIRED & LOCKED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL>selectusername,account_statusfromdba_users;
USERNAMEACCOUNT_STATUS
---------------------------------------
MGMT_VIEWOPEN
SYSOPEN
SYSTEMOPEN
DBSNMPOPEN
SYSMANOPEN
DEMOOPEN
OUTLNEXPIRED&LOCKED
MDSYSEXPIRED&LOCKED
ORDSYSEXPIRED&LOCKED
ANONYMOUSEXPIRED&LOCKED
EXFSYSEXPIRED&LOCKED
WMSYSEXPIRED&LOCKED
XDBEXPIRED&LOCKED
ORDPLUGINSEXPIRED&LOCKED
SI_INFORMTN_SCHEMAEXPIRED&LOCKED
DIPEXPIRED&LOCKED
ORACLE_OCMEXPIRED&LOCKED
TSMSYSEXPIRED&LOCKED
二、ORACLE数据库用户有多种状态,可查看视图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
1
2
3
4
5
6
7
8
9
10
11
12
SQL>select *fromuser_astatus_map;
STATUS# STATUS
--------------------------------------
0OPEN
1EXPIRED
2EXPIRED(GRACE)
4LOCKED(TIMED)
8LOCKED
5EXPIRED&LOCKED(TIMED)
6EXPIRED(GRACE)&LOCKED(TIMED)
9EXPIRED&LOCKED
10EXPIRED(GRACE)&LOCKED
三、看看dba_users中该用户的状态等信息
SQL>select account_status,lock_date,profile from dba_users where username='用户名';
1
SQL>selectaccount_status,lock_date,profilefromdba_userswhereusername='用户名';
发现就是今天才锁定的,帐号状态为“LOCKED(TIMED)”,是密码错误次数超过了系统设置的允许最大次数。
查看现在设置的最大次数
SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';
1
SELECTresource_name,resource_type,limitFROMdba_profilesWHEREprofile='DEFAULT';
找到
FAILED_LOGIN_ATTEMPTS PASSWORD 10
也就是说,原来允许的最大密码错误次数为10次,超过10次自动锁定帐号。难怪我怎么alter unlock都没用,看来还得先把这限制放宽才行。
把限制参数 FAILED_LOGIN_ATTEMPTS 修改成:unlimited
sql> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
1
sql>alterprofiledefaultlimitFAILED_LOGIN_ATTEMPTSunlimited;
或 修改成比较大次数:100000
sql>alter profile default limit FAILED_LOGIN_ATTEMPTS 100000;
1
sql>alterprofiledefaultlimitFAILED_LOGIN_ATTEMPTS100000;
再执行如下
SQL>alter user 用户名 account unlock;
1
SQL>alteruser用户名accountunlock;
就可以正常登录了。