This discussion is archived
8 Replies Latest reply: Oct 17, 2013 2:43 AM by Raunaq RSS

ORA-01000: Too many open cursors -- Need Help

user580970 Newbie
Currently Being Moderated

Hi All,

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

PL/SQL Release 11.1.0.7.0 - Production

I am getting error ora-01000 for  the following procedures gather stats

Could you please guide how to get-rid-off this error.

thanks in advance;

CREATE OR REPLACE PROCEDURE SHEMA_NAME ANALYZE_TABLES IS

   rec_table_name   VARCHAR2 (30);

   CURSOR c1

   IS

      SELECT table_name

        FROM USER_tables;  ------ 18000 table for this cursor

BEGIN

   OPEN c1;

   LOOP

      FETCH c1 INTO rec_table_name;

      EXIT WHEN c1%NOTFOUND;

      -- block was hereÿÿÿ

      BEGIN

         DBMS_STATS.

         GATHER_TABLE_STATS (

            OWNNAME            => 'SHEMA_NAME',

            TABNAME            => rec_table_name,

            PARTNAME           => NULL,

            ESTIMATE_PERCENT   => 30,

            METHOD_OPT         => 'FOR ALL COLUMNS SIZE AUTO',

            DEGREE             => 5,

            CASCADE            => TRUE);

      END;

   END LOOP;

   CLOSE c1;

EXCEPTION

   WHEN OTHERS

   THEN

      raise_application_error (

         -20001,

         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);

END;

  • 1. Re: ORA-01000: Too many open cursors -- Need Help
    user580970 Newbie
    Currently Being Moderated

    i have open cursors  2000

  • 2. Re: ORA-01000: Too many open cursors -- Need Help
    TSharma-Oracle Guru
    Currently Being Moderated

    There are few bugs regarding cursors in 11.1.0.7. Search metalink.

    Check this also

    Bugs, Fixed Versions and Workarounds in PL/SQL 11.1.0.X (Doc ID 799756.1)

  • 3. Re: ORA-01000: Too many open cursors -- Need Help
    davidp 2 Pro
    Currently Being Moderated

    If you really want to gather statistics for all tables in the schema, then use DBMS_STATS.GATHER_SCHEMA_STATS instead. That works without the ORA-01000 Too many open cursors.

    I can't see why your procedure doesn't work - I've used similar procedures, with fewer tables. It looks like an Oracle bug. possibly DBMS_STATS is leaking cursors when DEGREE is specified.

    I hate your exception handler - it just removes information about exactly where the error occurred.

  • 4. Re: ORA-01000: Too many open cursors -- Need Help
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    user580970 wrote:

     

    EXCEPTION

       WHEN OTHERS

       THEN

          raise_application_error (

             -20001,

             'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);

    END;

     

    Perfect example. Of how NOT to write an exception handler EVER.

     

    There is nothing right about.

  • 5. Re: ORA-01000: Too many open cursors -- Need Help
    Raunaq Explorer
    Currently Being Moderated

    Billy ,

     

    can you guide us for a better approach , and whats wrong in it.

    As it will help us improve.

     

    Thanks

  • 6. Re: ORA-01000: Too many open cursors -- Need Help
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Look at the following:

     

    SQL> begin
      2          raise no_data_found;
      3  end;
      4  /
    begin
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 2

     

    The error code the caller executing this code receive is -01403. A unique error number that has a known and specific meaning.

     

    In addition, the error stack tells the caller that this unique error occurred on line 2 in the source code.

     

    The caller knows EXACTLY what the error is and where it occurred.

     

     

    SQL> begin
      2          raise no_data_found;
      3  exception when OTHERS then
      4          raise_application_error(
      5                  -20000,
      6                  'oh damn some error happened. the error is '||SQLERRM
      7          );
      8  end;
      9  /
    begin
    *
    ERROR at line 1:
    ORA-20000: oh damn some error happened. the error is ORA-01403: no data found
    ORA-06512: at line 4

     

    In this case the caller gets the error code -20000. It is meaningless as the same error code will be use for ALL errors (when OTHERS). So the caller will never know what the actual real error is.

     

    For the caller to try and figure that out, it will need to parse and process the error message text to look for the real error code. A very silly thing to do.

     

    In addition, the error stack says that the error was caused by line 4 in the code called.. except that this is the line that raised the meaningless generic error and not the actual line causing the error.

     

    There are 3 basic reasons for writing an exception handler:

    - the exception is not an error

    - the exception is a system exception (e.g. no data found) and needs to be turned into meaningful application exceptions (e.g. invoice not found, customer not found, zip code not found, etc)

    - the exception handler is used as a try..finally resource protection block (which means it re-raises the exception)

     

    If your exception handler cannot tick one of these three reasons for existing, you need to ask yourself why you are writing that handler.

  • 7. Re: ORA-01000: Too many open cursors -- Need Help
    Hoek Guru
    Currently Being Moderated

    In addition to Billy and to emphasize what's wrong with it, read this followup (not 100% applicable to your case, but you're close enough):

    Ask Tom "Is there an easy way to make a ctas"

  • 8. Re: ORA-01000: Too many open cursors -- Need Help
    Raunaq Explorer
    Currently Being Moderated

    Thanks Billy

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points