Resolving ORA-01000 error
A cursor is a name used to access a specific private SQL area.
Oracle creates implicit cursors for all SQL DML (data manipulation language) statements, even for select statements returning one row. If your query returns more than one row, you can explicitly declare a cursor in PL/SQL to process the rows one by one.
Here is a simple example of using a cursor in a ‘cursor for loop’ (which automatically opens, fetches from and closes the cursor for you) :
SQL> set serveroutput on size 100000;
2 for c1 in (select empno from emp) loop
4 end loop;
PL/SQL procedure successfully completed.
Your application team may hit the following error :
ORA-01000: maximum open cursors exceeded
This means that your application’s session has hit the database-wide limit of the maximum number of open cursors which one session is allowed to have.
The database parameter which governs this limit is : open_cursors
You can change this parameter in real-time, but you should ask yourself the question if the value is really too low, perhaps the application code should be rewritten, to either do better housekeeping (close cursors when they are done), or rewrite the code so that many DML statements can be recorded to be used in one cursor.
Never set this to an excessive value, you want to keep open cursors in check.
Rather let a new, un-tuned application hit this error so that the code can be tuned.
Setting this value higher does not cause performance overhead on the database.
If you do decide to change the value , this is how to do it :
(the default value of 50 is usually too small, so you will probably change it sooner or later).
alter system set open_cursors=300 scope=both;
How to find out which cursors are open for a session ?
Get the session’s sid from v$session, then get the sql_text from v$open_cursor :
SQL> select sid, serial#, username from v$session where username = 'SYS';
SID SERIAL# USERNAME
--- ------ ------------------------------
69 3569 SYS
SQL> select sql_text from v$open_cursor where sid = 69;
select sql_text from v$open_cursor where sid = 69
When you want to make strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would be enjoy more advance topics from our partner resource.