本文共 4087 字，大约阅读时间需要 13 分钟。
今天突然有客户问我一个问题，数据库要添加一个监控用户，想做一个会话数的限制，这里做了一个小测试，平日维护的时候也需要关注一下数据库的资源限制。
<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, 2013, 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, 2013, Oracle. All rights reserved. ERROR: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit Enter user-name:小伙伴们，不要光看不练，花个十分二十分钟动手操作一下哈！