remote dba support
    HomeOracle TroubleshootingHow to resolve error ORA-01000

    How to resolve error ORA-01000

    How to resolve ORA-01000: maximum open cursors exceeded error

    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 /


    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';
    --- ------ ------------------------------
    69 3569 SYS

    SQL> select sql_text from v$open_cursor where sid = 69;
    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 -


    1. Very old but still valid solution. It is very nice to see that you are sharing such tricks and tips. Love to you get connected. I am going to bookmark your website. In information technology we are always learning.

    Comments are closed.

    - Advertisment -
    remote dba services

    Most Popular