oracle限制用户连接数

最新推荐文章于 2023-07-23 09:31:23 发布

逝鸿

最新推荐文章于 2023-07-23 09:31:23 发布

阅读量6k

点赞数

文章标签：限制用户连接数

本文链接：https://blog.csdn.net/qq_21127313/article/details/79406358

版权

本操作不需要重启数据库

SQL> show parameter resource_limit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
SQL> alter system set resource_limit=true;

System altered.

SQL> create profile sess limit sessions_per_user 3;

Profile created.

SQL> alter user test profile sess;

User altered.

此时再开四个窗口，使用test用户连接，在第四个窗口连接报错：

[oracle@11g ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 28 14:19:12 2018

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


Enter user-name:

那么，如果限制数小于当前连接数呢？

SQL> select count(*) from gv$session where username='TEST';

  COUNT(*)
----------
         3

SQL> alter profile sess limit sessions_per_user 2;

Profile altered.

SQL> select count(*) from gv$session where username='TEST';

  COUNT(*)
----------
         3

SQL> alter user test profile sess;

User altered.

SQL> select count(*) from gv$session where username='TEST';

  COUNT(*)
----------
         3

不影响当前连接的会话，那么把当前会话关掉再打开呢？

[oracle@11g ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 28 14:19:09 2018

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, OLAP, Data Mining and Real Application Testing options

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11g ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 28 14:30:43 2018

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

关掉再连就连不进去了,另外，修改profile之后需要再alter user profile xxx吗？

SQL> alter profile sess limit sessions_per_user 1;

Profile altered.

SQL> select count(*) from gv$session where username='TEST';

  COUNT(*)
----------
         2
（此时将一个会话断掉重连报错）
SQL> select count(*) from gv$session where username='TEST';

  COUNT(*)
----------
         1

不需要