open_cursors 与 ORA-01000
open_cursors 是可以打开的游标数,如果只打开不关闭,很容易就到上限了
下面我们来做下实验:
先把值改小一点
SQL> alter system set open_cursors=5; System altered.
建一proc
CREATE OR REPLACE PROCEDURE p_cur_test(OUT cur SYS_REFCURSOR) AS BEGIN OPEN cur FOR SELECT * FROM emp; END;
多次打开
SQL> DECLARE 2 TYPE t_cur IS REF CURSOR; 3 cur1 t_cur; 4 cur2 t_cur; 5 cur3 t_cur; 6 BEGIN 7 p_cur_test(cur1); 8 p_cur_test(cur2); 9 p_cur_test(cur3); 10 END; 11 / PL/SQL procedure successfully completed
这时还没超限,我们再增加次数
SQL> DECLARE 2 TYPE t_cur IS REF CURSOR; 3 cur1 t_cur; 4 cur2 t_cur; 5 cur3 t_cur; 6 cur4 t_cur; 7 cur5 t_cur; 8 cur6 t_cur; 9 BEGIN 10 p_cur_test(cur1); 11 p_cur_test(cur2); 12 p_cur_test(cur3); 13 p_cur_test(cur4); 14 p_cur_test(cur5); 15 p_cur_test(cur6); 16 END; 17 / DECLARE TYPE t_cur IS REF CURSOR; cur1 t_cur; cur2 t_cur; cur3 t_cur; cur4 t_cur; cur5 t_cur; cur6 t_cur; BEGIN p_cur_test(cur1); p_cur_test(cur2); p_cur_test(cur3); p_cur_test(cur4); p_cur_test(cur5); p_cur_test(cur6); END; ORA-01000: maximum open cursors exceeded ORA-06512: at "TEST.P_CUR_TEST", line 3 ORA-06512: at line 13
现在报错了
再试下每次打开后关闭
SQL> DECLARE 2 TYPE t_cur IS REF CURSOR; 3 cur1 t_cur; 4 cur2 t_cur; 5 cur3 t_cur; 6 cur4 t_cur; 7 cur5 t_cur; 8 cur6 t_cur; 9 BEGIN 10 p_cur_test(cur1); 11 CLOSE cur1; 12 p_cur_test(cur2); 13 CLOSE cur2; 14 p_cur_test(cur3); 15 CLOSE cur3; 16 p_cur_test(cur4); 17 CLOSE cur4; 18 p_cur_test(cur5); 19 CLOSE cur5; 20 p_cur_test(cur6); 21 CLOSE cur6; 22 END; 23 / PL/SQL procedure successfully completed SQL>
正常结束
恢复参数
SQL> alter system set open_cursors=150; System altered.
声明:该文观点仅代表作者本人,牛骨文系教育信息发布平台,牛骨文仅提供信息存储空间服务。