Thursday, October 29, 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.

    - 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

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments