今天突然有客户问我一个问题，数据库要添加一个监控用户，想做一个会话数的限制，这里做了一个小测试，平日维护的时候也需要关注一下数据库的资源限制。

<roidb1:orcl1:/home/oracle>$sqlplus / as sysdba

sql*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 18:16:19 2017

Copyright (c) 1982,2013,Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Real Application Clusters,Automatic Storage Management,OLAP,
Data Mining and Real Application Testing options



sql> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ---------
resource_limit boolean FALSE --默认值
sql>


sql> set linesize 160
sql> select * from dba_profiles order by 1,3,2;

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------- -------------------------------- ------------ ---------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT cpu_PER_CALL KERNEL UNLIMITED
DEFAULT cpu_PER_SESSION KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT Failed_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------- -------------------------------- ------------ --------
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT
MONITORING_PROFILE cpu_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE cpu_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------- -------------------------------- ------------ ---------------
MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
MONITORING_PROFILE Failed_LOGIN_ATTEMPTS PASSWORD UNLIMITED
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

32 rows selected.


测试1： Failed_LOGIN_ATTEMPTS=10 是否是生效

sql> create user roidba identified by roidba;

User created.

sql> grant connect,resource,dba to roidba;

Grant succeeded.

sql> exit


省略..................经过十次登陆...........



<roidb1:orcl1:/home/oracle>$sqlplus roidba/roidbaa

sql*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 18:26:37 2017

Copyright (c) 1982,Oracle. All rights reserved.

ERROR:
ORA-28000: the account is locked


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:


实验证明不管 resource_limit 是否为true，和密码相关的限制都是生效，其他和密码相关的大家可以自己测试。


DEFAULT Failed_LOGIN_ATTEMPTS PASSWORD 10 --密码输入十次都是错误，用户锁定
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 --锁定一天以后自动解锁
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 --密码生命周期180天，之后密码失效
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 --宽限延续期，宽限期内登陆会有提示。


安装完数据库，一般会把password_life_time设置为unlimited。
sql> alter profile default limit password_life_time unlimited;
Profile altered.



继续测试2：
sql> create profile sess limit
2 SESSIONS_PER_USER 2;

Profile created.


sql> alter user roidba profile sess;

User altered.

sql> alter system set resource_limit=true;

System altered.


打开三个窗口，前两个都顺利登陆，第三个出现以下报错。


<roidb1:orcl1:/home/oracle>$sqlplus roidba/roidba

sql*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 18:48:24 2017

Copyright (c) 1982,Oracle. All rights reserved.

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


Enter user-name:

小伙伴们，不要光看不练，花个十分二十分钟动手操作一下哈！