| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: limitation of CURSORs
A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com>
(if that email address didn't require changing)
On Sat, 07 Aug 1999 21:23:19 -0400, you wrote:
>It sounds to me like he's implying that the open_cursors is set at a
>reasonable
>level, he just opened too many, and now what does he do... ??
>
reasonable is in the eye of the beholder. my concept of reasonable for open_cursors is around 500-1,000... I run lots of plsql -- i use 1,000 myself.
plsql will cache cursors by default. If you execute a procedure that runs 3 selects -- you'll find that 3 cursors are open in your session. For example:
SQL> create or replace procedure test_proc
2 as
3 begin
4 for x in ( select * from dual where 1=0 ) loop 5 null; 6 end loop; 7 for x in ( select * from dual where 1=1 ) loop 8 null; 9 end loop; 10 for x in ( select * from dual where 1=2 ) loop 11 null; 12 end loop;
Procedure created.
SQL> select sql_text from v$open_cursor where sid = ( select sid from v$session 2 where audsid = userenv('sessionid'));
SQL_TEXT
SQL> exec test_proc
PL/SQL procedure successfully completed.
SQL>
SQL> select sql_text from v$open_cursor where sid = ( select sid from v$session
2 where audsid = userenv('sessionid'));
SQL_TEXT
SELECT * FROM DUAL WHERE 1 = 0 SELECT * FROM DUAL WHERE 1 = 2 SELECT * FROM DUAL WHERE 1 = 1
So, after running the procedure -- our cursors are still there even though in PLSQL terms they are closed. plsql caches the cursors -- in the even we rerun the procedure they are already parsed and ready to go, just bind and execute them. The following shows that by default when we commit:
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select sql_text from v$open_cursor where sid = ( select sid from v$session
2 where audsid = userenv('sessionid'));
SQL_TEXT
SELECT * FROM DUAL WHERE 1 = 0 SELECT * FROM DUAL WHERE 1 = 2 SELECT * FROM DUAL WHERE 1 = 1
they stay there. We can change this behaviour in 8.0 and before by doing this:
SQL>
SQL> alter session set close_cached_open_cursors = true;
Session altered.
SQL> commit;
Commit complete.
SQL>
SQL> select sql_text from v$open_cursor where sid = ( select sid from v$session
2 where audsid = userenv('sessionid'));
SQL_TEXT
By setting close_cached_open_cursors -- the cursors plsql caches are closed when we commit. this can be done at the session level as above or at the system level permanently by setting it in the init.ora file.
In 8.1, this parameter is obsoleted (close_cached_open_cursors) as the algorithm for caching cursors has been changed. the system will flush and reuse cursor entries as needed so you will just use open_cursors and session_cached_cursors to control this behavior.
>Johannes Wilhelm wrote:
>
>> Nandakumar <nanban_at_my-deja.com> wrote:
>> > It looks like, i opened many dynamic cursors from within a procedure
>> > and didn't close them in the end. And now, i am at a level
>> > where i can't open any more cursors. How could i close these
>> > cursors so that further cursors can be opened.
>>
>> > There should be some DICT table that records the status of cursors with
>> > cursor ids, i guess.
>>
select * from v$open_cursor;
>> > Would appreciate any solution to this probelm.
>>
>> > Thanks
>>
>> > In article <7o564l$bff$1_at_nnrp1.deja.com>,
>> > Nandakumar <nanban_at_my-deja.com> wrote:
>> >>
>> >>
>> >> When i try to drop a view, i get an error that says maximum CURSOR
>> > limit
>> >> has exceeded.
>> >>
>> >> Anyone can give an idea on how this can be reset?
>> >>
>> >> drop view table1
>> >> *
>> >> ERROR at line 1:
>> >> ORA-00604: error occurred at recursive SQL level 1
>> >> ORA-01000: maximum open cursors exceeded
>> >>
>> >> Thanks
>> >> --
>> >> Nandakumar
>> >> Systems Analyst
>> >> New York
>> >> (N.Kumar_at_rocketmail.com)
>> >>
>> >> Sent via Deja.com http://www.deja.com/
>> >> Share what you know. Learn what you don't.
>> >>
>>
>> > --
>> > Nandakumar
>> > Systems Analyst
>> > New York
>> > (N.Kumar_at_rocketmail.com)
>>
>> > Sent via Deja.com http://www.deja.com/
>> > Share what you know. Learn what you don't.
>>
>> Add:
>> open_cursors = #
>> to the init.ora file with #>64 .
>>
>> Every access to a table or joined table lets an open cursor to your
>> session. If you need more than the default of 64 you can enlarge the
>> number without problems.
>>
>> Johannes
>
>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Aug 08 1999 - 13:49:01 CDT
 |
 |