Oracle:idle_time 似乎被忽略 [英] Oracle: idle_time appears to be ignored

问题描述

根据我的理解，创建一个将 idle_time 设置为某个值(以分钟为单位)的配置文件，并使用此配置文件创建一个用户应该会强制使用 SNIPED 状态用户的会话，以防他空闲时间超过 idle_time.当用户在发生这种情况后尝试执行查询时，他会收到一条消息，要求他再次连接.

In my understanding, creating a profile with the idle_time set to a certain value (in minutes) and creating a user with this profile should force the SNIPED status for that user's session in case he is idle for longer than idle_time. When the user tries to execute a query after this has happened, he receives a message that he must connect again.

第一个问题:是吗?如果是，请继续阅读:

我在 sqlplus 中按如下方式运行测试脚本(显然没有占位符):

I'm running a test script as follows in sqlplus (without the placeholders obviously):

connect system/<password>@<tns>
CREATE PROFILE test_profile LIMIT idle_time 1;
CREATE USER test_user PROFILE test_profile IDENTIFIED BY test_user;
GRANT CREATE SESSION TO test_user;
GRANT ALTER SESSION TO test_user;
GRANT SELECT ON <schema>.<table> TO test_user;
disconnect;
connect test_user/test_user@<tns>
SELECT * FROM <schema>.<table>;

到目前为止一切正常；sqlplus 窗口仍然打开.现在我打开一个额外的 sqplus 窗口并使用系统帐户连接，在做其他事情一段时间后运行以下查询:

Everything works up to this point; the sqlplus window is still open. Now I open an additional sqplus window and connect using the system account, running the following query after doing other stuff for a while:

SELECT username, status, seconds_in_wait FROM v$session WHERE username = 'test_user';

我得到类似的信息:

USERNAME  STATUS   SECONDS_IN_WAIT
--------- -------- ---------------
TEST_USER INACTIVE            1166

为什么状态没有设置为SNIPED?

显然，如果我从 test_user 的 sqlplus 窗口运行另一个查询，我不会收到要求我重新连接的消息.

Obviously, If I run another query from the test_user's sqlplus window, I do not get a message asking me to reconnect.

推荐答案

您需要将数据库的 RESOURCE_LIMIT 参数设置为 TRUE 以便配置文件中的资源限制生效影响.假设您使用 spfile(否则省略 scope = BOTH 部分)

You need to set the database's RESOURCE_LIMIT parameter to TRUE in order for resource limits in profiles to take effect. Assuming you use a spfile (otherwise omit the scope = BOTH part)

ALTER SYSTEM SET resource_limit = TRUE scope = BOTH 

一旦你这样做了，PMON 应该在它每隔几分钟醒来时开始狙击超过你的 IDLE_TIME 的会话.

Once you do that, PMON should start sniping the sessions that have exceeded your IDLE_TIME when it wakes up every few minutes.

这篇关于Oracle:idle_time 似乎被忽略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！