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.

- Advertisement -

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;
SQL> begin
2 for c1 in (select empno from emp) loop
3 dbms_output.put_line(c1.empno);
4 end loop;
5 end;
6 /

7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

- Advertisement -

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;
SQL_TEXT
------------------------------------------------------------
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
select sql_text from v$open_cursor where sid = 69

When you want to make a 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 keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a 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 enjoy more advanced topics from our partner resource.

- Advertisement -