Friday, August 14, 2020
dbametrix
More
    Home Oracle Troubleshooting How 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 -
    dbametrix

    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 -
    dbametrix

    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 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.

    1+
    - Advertisement -
    dbametrix

    1 COMMENT

    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 -
    dbametrix

    Most Popular

    ORA-01940: Cannot drop a user that is currently connected

    Detail explanation and solution of ORA-01940: Cannot drop a user that is currently connected error.

    Ubuntu and Oracle SQL Developer

    Blog post explains how to install and use of Oracle SQL Developer on Ubuntu linux server

    Create Schema in Oracle 18c XE

    Article explain how to create new user in Oracle 18c XE and grant some system privileges with connect as new schema user in database.

    Oracle Autonomous JSON database

    The blog article explains about What is the Oracle autonomous JSON database, usage and how to get it from Oracle Cloud.

    Recent Comments

    Margareta Combes on nosql Security Vulnerabilities
    Gordon Coleclough on Oracle Tuning Basics Part-1
    Gordon Coleclough on ASM Disk creation issue