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.