“超过最大打开的游标":如何知道某个DB对象打开了多少个游标? [英] "Maximum open cursors exceeded": How to know for some DB object how many cursors are open?

问题描述

在 Finacle 中，有时由于oracle错误，用户面临资源繁忙"错误在后端生成为已超出最大打开游标数".并不是这个错误经常发生，而是我们要采取预防措施，这就是为什么我们试图为Finacle工程师(即我们自己)生成警报，以便每当某个数据库对象的游标数量超过最大值时，我们都可以获取该警报.警报.为此，我们需要知道什么命令才能知道在给定时间为数据库对象打开了多少个游标.有人可以在这件事上指导我们吗?

In Finacle, sometimes users are facing "The resource is busy" error due to the oracle error generated in back-end as "Maximum open cursors exceeded". Not that this error comes often, but we want to take precautionary measures and that is why we are trying to generate an alert for the Finacle engineers i.e. for ourselves so that whenever the maximum no of cursors exceeds for some DB object, we can get the alert. For that, we need to know what is the command to know how many cursors are open for the DB objects at a given time. Can somebody guide us on this matter please.

要澄清我的概念的另一件事，Oracle是否使用任何默认游标来查询表?因为，我的猜测是，由于该软件的用户数量增加，并且使用select和update查询为同一个数据库对象打开了许多会话，因此产生了该错误，这就是打开的游标数量不断增加的原因.

Another thing to clear my concept, does Oracle use any default cursor for querying a table? Because, my guess is this error is generating because number of users for this software has increased and there are lots of sessions getting opened for the same DB object with select and update query and that is how number of open cursors is getting increased.

推荐答案

我猜想您在使用它后不会关闭游标，例如，我自定义了open_cursor参数，以允许用户只能打开一个游标(Oracle自身需要使用3个游标):

I guess you don't close your cursors after you use it, For example, I customized the open_cursor parameter to allow user to have only one opened cursor (3 cursors oracle uses for its own needs):

SQL> conn / as sysdba
Connected.
SQL> alter system set open_cursors=4 scope=memory
  2  /

System altered.

SQL> conn hr/hr
Connected.
SQL> var l_cursor refcursor;
SQL> ed
Wrote file afiedt.buf

  1  begin
  2    open :l_cursor for select 1 from dual;
  3* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> var l_cursor1 refcursor;
SQL> ed
Wrote file afiedt.buf

  1  begin
  2    open :l_cursor1 for select 1 from dual;
  3* end;
SQL> /
begin
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded

如您所见，我也遇到了同样的错误.让我们关闭l_cursor光标(打印命令显示并关闭光标的内容)，然后再次打开l_cursor1:

As you see I face the same error you had. Let's close the l_cursor cursor (the print command displays a cursor's content and closes it) and then open l_cursor1 once again:

SQL> print l_cursor

         1
----------
         1

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    open :l_cursor1 for select 1 from dual;
  3* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> print l_cursor1

         1
----------
         1

如您所见，Oracle可以成功打开l_cursor1光标.

As you can see Oracle can successfully open the l_cursor1 cursor.

作为您的问题的快速解决方案，可以使用命令增加open_cursor参数(您的系统可能需要更多资源来保持当前性能):

As a quick solution of your problem you can increase the open_cursor parameter with command (you system might need additional resources to keep the current performance):

alter system set open_cursors=800 scope=both 

我使用了scope=memory，因为我希望该演示程序在重启数据库服务器后不会影响我的系统.您必须指定scope = both才能将此参数保留在spfile中.但是我想您必须检查系统，并确定是否打开了不再使用的游标.

I used scope=memory, because I want this demo doesn't affect my system after the database server is restarted. You have to specify scope=both to persist this parameter in spfile. But I guess you have to exam your system and find out if you have opened cursors that aren't used anymore.

您的猜测是"Oracle是否使用任何默认游标来查询表?"是的，Oracle使用游标查询表，您可以确保:

Your guess about "does Oracle use any default cursor for querying a table?" is true, Oracle uses cursors to query tables, you can make sure:

SQL> var l_cursor refcursor;
SQL> ed
Wrote file afiedt.buf

  1  begin
  2      open :l_cursor for select 1 from dual;
  3* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from employees;
select * from employees
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

SQL> print l_cursor

         1
----------
         1
SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY
------------------------- -------------------- -------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        205 Shelley              Higgins
...

要获取所有打开的游标，请检查此 answer

To get all opened cursors check this answer

这篇关于“超过最大打开的游标":如何知道某个DB对象打开了多少个游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！