db:: 4.27::PSOD - Spin Count Exceeded p8
Widget settings form goes hereOracle概要文件（资源配置文件）简介
Oracle系统为了合理分配和使用系统的资源提出了概要文件的概念。所谓概要文件，就是一份描述如何使用系统的资源(主要是CPU资源)的配置文件。将概要文件赋予某个数据库用户，在用户连接并访问数据库服务器时，系统就按照概要文件给他分配资源。在有的书中将其翻译为配置文件，其作用包括：
1、管理数据库系统资源。
利用Profile来分配资源限额，必须把初始化参数resource_limit设置为true
ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;
2、管理数据库口令及验证方式。
默认给用户分配的是DEFAULT概要文件，将该文件赋予了每个创建的用户。但该文件对资源没有任何限制，因此管理员常常需要根据自己数据库系统的环境自行建立概要文件
通过数据字典查看缺省资源配置内容：
select * from dba_profi
范例：当一个用户连接到上以后，如果在某一段时间内没有任何动作的话，该进程就会自己中断，这样在应用程序就会因为进程超时自动断开而不能正常执行。
分析原因，应该是该进程的用户的概要文件(profile)配置问题。发现用户所在的profile的IDLE_TIME为30。即30秒该进程没有任何操作，就会自动断开。
PROFILE的管理（资源文件）当需要设置资源限制时，必须设置数据库系统启动参数RESOURCE_LIMIT，此参数默认值为FALSE
可以使用如下命令来启动当前资源限制：
alter system set RESOURCE_LIMIT=
当需要以后启动时也启动限制，必须在init&SID&.ora中设置
RESOURCE_LIMIT=true
建立语法：
CREATE PROFILE profile LIMIT
{ resource_parameters | password_parameters }
[ resource_parameters | password_parameters ]...;
{ { SESSIONS_PER_USER | CPU_PER_SESSION | CPU_PER_CALL |
CONNECT_TIME |
IDLE_TIME&&&&
| LOGICAL_READS_PER_SESSION | LOGICAL_READS_PER_CALL |
COMPOSITE_LIMIT }
{ integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA { integer [ K | M ] | UNLIMITED | DEFAULT }
{ { FAILED_LOGIN_ATTEMPTS | PASSWORD_LIFE_TIME |
PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX | PASSWORD_LOCK_TIME | PASSWORD_GRACE_TIME
{ expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
CREATE PROFILE new_profile
LIMIT PASSWORD_REUSE_MAX DEFAULT
&&&&&&&&&&
PASSWORD_REUSE_TIME UNLIMITED;
CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER&&&&&&&&&&&&
CPU_PER_SESSION&&&&&&&&&&&&&&
CPU_PER_CALL&&&&&&&&&&&&&&&&&
CONNECT_TIME&&&&&&&&&&&&&&&&&
LOGICAL_READS_PER_SESSION&&&&
LOGICAL_READS_PER_CALL&&&&&&&
PRIVATE_SGA&&&&&&&&&&&&&&&&&&
COMPOSITE_LIMIT&&&&&&&&&&&&&&
CREATE PROFILE app_user2 LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10;
&&&&&&&&&&
一、PROFILE的管理内容：
1、CPU的时间
2、I/O的使用
3、IDLE TIME（空闲时间）
4、CONNECT TIME（连接时间）
5、并发会话数量
6、口令机制：
二、DEFAULT PROFILE：
1、所有的用户创建时都会被指定这个PROFILE
2、DEFAULT PROFILE的内容为空，无限制
三、PROFILE的划分：
If a user attempts to perform an operation that exceeds the limit
for other session resources,
Oracle aborts the operation, rolls back the current statement, and
immediately returns an error.
The user can then commit or roll back the current transaction, and
must then end the session.
that exceeds the limit(超过限制) for other session
resources(范围之外的其他资源)
被理解为：连接控制资源范围之外的其他资源。
当一个用户试图操作超过限制的资源时，Oracle终止当前操作，取消正在执行的处理，并且返回一个错误，
用户可以提交或回滚当前事物，之后必须断开会话。
如果一个用户试图操作一个超出他限制的资源，Oracle将放弃这个操作，取消正在执行的处理，并立即返
回一个错误。这个用户然后提交或回滚当前的处理，之后必须终止这个会话。
如果是调用限制时，Oracle终止当前操作，取消正在执行的处理，并且返回一个错误，回滚整个事务。
1、CALL级LIMITE：
对象是语句：
当该语句资源使用溢出时：
A、该语句终止
B、事物回退
C、SESSION连接保持
2、SESSION级LIMITE：
对象是：整个会话过程
溢出时：连接终止
四、如何管理一个PROFILE
1、CREATE PROFILE
2、分配给一个用户
3、象开关一样打开限制。
五、如何创建一个PROFILE：
CREATE PROFILE 名称
SESSION_PER_USER 2
CPU_PER_SESSION 1000
用户在数据库终止前，可以让连接空闲多长时间（分钟）
CONNECT_TIME 480
六、限制参数：
0、UNLIMITED 取消资源限制
DEFAULT&&&&&
缺剩资源限制，即使用default资源限制
1、CONNECT_TIME&&&&&&&
：一个连接会话的最长连接时间（分钟）
&&&&&&&&&&&&&&&&&&&&&&&&&&&&
当用户会话时间超过CONNECT_TIME指定的时间，Oracle将回滚当前事务，并且结束他的会话
IDLE_TIME&&&&&&&&&&
：一个连接会话的最长连接时间
&&&&&&&&&&&&&&&&&&&&&&&&&&&&
当用户空闲时间超过IDLE_TIME指定的时间，Oracle将回滚当前事务，并且结束他的会话
2、SESSIONS_PER_USER：一个用户允许同时(并发)会话的总数，超过后系统禁止该用户的后续会话，并返回错误：
&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit
3、SESSION级LIMITE：
CPU_PER_SESSION：定义了一个SESSION占用的CPU的时间（1/100
秒），当达到这个限制用户不能在此会话中执行任何操作，
&&&&&&&&&&&&&&&&&&&&&&&&&&
此时必须先断开连接，再连接才行。
CPU_PER_CALL&&&&&
：限制每次调用SQL语句期间(parse、execute、fetch)可用的CPU时间总量，单位是百分之一秒。达到限制时
&&&&&&&&&&&&&&&&&&&&&&&&&&
语句以报错结束。不同于CPU_PER_SESSION的是，没达到CPU_PER_SESSION限制，还可以进行新的查询。
4、LOGICAL_READS_PER_SESSION: 一个会话允许读写的逻辑块的数量限制
5、CALL级LIMITE
CPU_PER_CALL:每个语句占用的CPU时间
LOGICAL_READS_PER_CALL:一次调用的SQL期间，允许读数据库块数限制
6、PRIVATE_SGA：一个会话允许分配的最大SGA大小，使用K/M定义
COMPOSITE_LIMIT：一个会话的资源成本限制，CPU_PER_SESSION, CONNECT_TIME,
LOGICAL_READS_PER_SESSION, and PRIVATE_SGA
七、分配给一个用户：
CREATE USER 名称。。。。。。
PROFILE 名称
ALTER USER 名称 PROFILE 名称
八、打开资源限制：
1、RESOURCE_LIMT:资源文件中含有
2、ALTER SYSTEM SET RESOURCE_LIMIT=TRUE；
3、默认不打开
九、修改PROFIE的内容：
1、ALTER PROFILE
名称参数&&&&&
2、对于当前连接修改不生效。
DROP一个PROFILE
1、DROP PROFILE 名称
删除一个新的尚未分配给用户的PROFILE，
2、DROP PROFILE 名称 CASCADE
3、注意事项
A、一旦PROFILE被删除，用户被自动加载DEFAULT PROFILE
B、对于当前连接无影响
C、DEFAULT PROFILE不可以被删除
十、信息获取：
1、DBA_USERS:
用户名，PROFILE
2、DBA_PROFILES:
PROFILE及各种限制参数的值
每个用户的限制：PROFILE（关键字段）
十一、PROFILE的口令机制限制
1、限制内容
A、限制连续多少次登录失败，用户被加锁
B、限制口令的生命周期
C、限制口令的使用间隔
2、限制生效的前提：
A、RESOURCE_LIMIT:=TRUE
B、ORACLE\RDBMS\ADMIN\UTLPWDMG.SQL
3、如何创建口令机制：
CREATE PROFILE 名称
SESSIONS_PER_USER
password_life_time 30
failed_log_attempts 3
password_reuse_time 3
4、参数的含义：
A、FAILED_LOGIN_ATTEMPTS&&&&&
：当连续登陆失败次数达到该参数指定值时，用户被加锁；
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
经过DBA解锁(或PASSWORD_LOCK_TIME天)后可继续使用
B、PASSWORD_LIFE_TIME&&&&&&&&
：口令的有效期（天），默认为UNLIMITED
C、PASSWORD_LOCK_TIME&&&&&&&&
：帐户因FAILED_LOGIN_ATTEMPTS锁定时，加锁天数
D、PASSWORD_GRACE_TIME&&&&&&&
：口令修改的宽限期（天）
E、PASSWORD_REUSE_TIME&&&&&&&
：口令被修改后原有口令隔多少天被重新使用，默认为UNLIMITED
F、PASSWORD_REUSE_MAX&&&&&&&&
：口令被修改后原有口令被修改多少次才允许被重新使用。
G、PASSWORD_VERIFY_FUNCTION：口令效验函数
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。ORA-02391 Exceeded simultaneous session_per_user limit
Burleson is the American Team
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.&
Feel free to ask questions on our
experience!
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
&Oracle technology is changing and we
strive to update our BC Oracle support information.& If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback.& Just&
and include the URL for the page.
&&&&&&&&&&&&&&&&&&&&
Burleson Consulting
The Oracle of
Database Support
Copyright & 1996 -& 2017
All rights reserved by
is the registered trademark of Oracle Corporation.
Remote Emergency Support provided by
��&& && && ORA-2391: exceeded simultaneous SESSIONS_PER_USER limit (Oracle 10.2.03)
Messages: 2Registered: February 2008 Location: singapore
I am using Oracle 10.2.03 database with asp.net 1.1 web application,
My application deployed on the production, but I got the Oracle Error:
ORA-2391: exceeded simultaneous SESSIONS_PER_USER limit
Can I have suitable remedy for this error?
Thanks & regards,
Singapore.
[ is a reply to ]
Messages: 8803Registered: November 2002 Location: California, USA
The following is from the online documentation:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
Cause: An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSION_PER_USER clause of the user prfile.
Action: End one or more concurrent sessions or ask the database administrator to increase the SESSION_PER_USER limit of the user profile.
[ is a reply to ]
Messages: 2Registered: February 2008 Location: singapore
Hi Barbara Boehmer,
Already we set the SESSION_PER_USER limit of the user profile is 80,
So concurrently 80 users session may occur.
But still we get the same error.
thanks & regards,
Singapore.
[ is a reply to ]
Messages: 8803Registered: November 2002 Location: California, USA
Perhaps you have something that is starting sessions without exiting them.
Try to identify how so many sessions are open at the same time.
[ is a reply to ]
Messages: 579Registered: April 2012 Location: India
I was trying to execute
a procedure over
db connection but got the error.
Why this error occurs and how to resolve it. And if some session open then how to close it.
[Updated on: Wed, 30 October ]
[ is a reply to ]
Messages: 65342Registered: March 2007 Location: Nanterre, France, http://...
If you don't tell us what you actually did and got we can't help you.
With 365 posts you should know it.
For instance, why is this in this topic and why didn't you create a new one?
[ is a reply to ]
Messages: 579Registered: April 2012 Location: India
Can anyone please help me to resolve the above issue. I got the following error.
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
[ is a reply to ]
Messages: 2847Registered: May 2007 Location: Scotland
Reduce the number if open sessions or get the dba to increase the parameter value mentioned above.
[ is a reply to ]
Messages: 579Registered: April 2012 Location: India
We are USING A dblink TO get THE DATA AND showing A report.But sometime it IS throwing AS follows AND THE whole PACKAGE got invalidated.
And after this we have to compile it manullay to get the package work correct.
Error(1): ORA-04052: error occurred when looking up remote object relSTA.srt@alr_dblink
ORA-00604: ERROR occurred AT recursive SQL LEVEL 1
ORA-02391: exceeded simultaneous SESSIONS_PER_USER LIMIT
ORA-02063: preceding line from alr_dblink
Why THE above ERROR IS happening AT regular basis AND how can it resolved. What IS MAIN cause OF THE above ERROR.
IF we have reached THE MAX SESSIONS_PER_USER THEN we can NOT increase more that its MAX. What IS THE necessary action
must be take care of. Please help I am in big trouble here.
[ is a reply to ]
Messages: 65342Registered: March 2007 Location: Nanterre, France, http://...
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
*Cause: An attempt was made to exceed the maximum number of
concurrent sessions allowed by the SESSION_PER_USER clause
of the user profile.
*Action: End one or more concurrent sessions or ask the database
administrator to increase the SESSION_PER_USER limit of
the user profile.
[ is a reply to ]
Messages: 579Registered: April 2012 Location: India
The problem was like
CREATE OR REPLACE procedure p_link_test(p_deptno in number,c_emp_details out sys_refcursor) IS
OPEN c_emp_details FOR SELECT * FROM emp@db_
variable c_emp_
p_link_test(10,:c_emp_details);
The procedure p_link_test is called from a different application .And the session over the dblink was getting opened eachtime when the procedure is being called
which can never be closed again in frontend because no commit issued anywhere either in frontend and backend. So after maximum session opened the above error was
throwing .In Oracle document mentions that the two phase commit must required in db link even in select statement.
So We have modified the procedure like and it started working perfectly.
CREATE global temporary TABLE eMP_temp
NUMBER(4,0) ,
VARCHAR2(10 BYTE),
VARCHAR2(9 BYTE),
NUMBER(4,0),
HIREDATE DATE,
NUMBER(7,2),
NUMBER(7,2),
NUMBER(2,0)
CREATE OR REPLACE procedure p_link_test(p_deptno in number,c_emp_details out sys_refcursor) IS
delete from emp_
insert into eMP_temp SELECT * FROM emp@db_link where deptno=p_
--the problem solved here
OPEN c_emp_details FOR SELECT * FROM eMP_
[Updated on: Thu, 02 January ]
Previous Topic:
Next Topic:
Goto Forum:
- SQL & PL/SQL&&SQL & PL/SQL&&Client Tools- RDBMS Server&&Server Administration&&Backup & Recovery&&Performance Tuning&&Security&&Networking and Gateways&&Enterprise Manager&&Server Utilities- Server Options&&RAC & Failsafe&&Data Guard&&Replication&&Streams & AQ&&Spatial&&Text & interMedia&&Multitenant- Developer & Programmer&&Application Express, ORDS & MOD_PLSQL&&Forms&&Reports & Discoverer&&Designer&&JDeveloper, Java & XML&&Data Integration&&Precompilers, OCI & OCCI- Fusion Middleware & Colab Suite&&Weblogic & Application Server&&Business Intelligence&&WebCenter Suite & Portal&&Collaboration & Content Management&&SOA Suite, BPEL and OWSM- Applications&&Oracle Fusion Apps & E-Business Suite&&PeopleSoft, JD Edwards & Siebel&&Primavera&&Other Application Suites- Infrastructure&&Cloud Hosting Services&&Hardware&&Unix&&Linux&&Windows&&Other Operating Systems- Open Source&&Programming Interfaces&&MySQL&&NoSQL- Other&&General&&Training & Certification&&Marketplace&&Community Hangout&&Suggestions & Feedback&&Test- Non-English Forums&&Arabic&&Dutch&&French&&German&&Japanese&&Portuguese&&Russian&&Spanish
Total time taken to generate the page: 0.02358 seconds博客访问： 24619
博文数量： 17
注册时间：
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Linux
原文地址： 作者：
& & &sessions_per_user的作用是限制每个user能登录的最多会话数，如果没启用资源限制的话，那这个参数
即使设置了也不会生效的:
&&&& 以下为测试过程：
创建每个用户只允许一个会话的限制
&&& create profile one_session limit sessions_per_user 1;
将这个资源限制赋予给test用户
&alter user test profile one_
启用资源限制&&alter system set resource_limit=
C:\Documents and Settings\paulyi>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on 星期日 2月 7 12:04:03 2010
Copyright (c) , Oracle Corporation.& All rights reserved.
连接到:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - Production
第二个用户登录时就报超过限制的报错了
C:\Documents and Settings\paulyi>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on 星期日 2月 7 12:04:21 2010
Copyright (c) , Oracle Corporation.& All rights reserved.
ERROR:ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
请输入用户名:& 设置成功
阅读(4443) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。