الانتقال إلى المحتوى

Ora-01000: Maximum Open Cursors Exceeded


ahmedkhaled

Recommended Posts

السلام عليكم ورحمة الله وبركاته



جزى الله عني خيراً من فتح مشكلتي ليساعدني او ليستفيد اما بعد
المشكله اني احاول الاتصال بالاوراكل من خلال برنامج جافا صممته بنفسي هذا البرنامج يقوم بإنشاء اتصال مع الاوراكل ثم يستخدم هذة الاتصال ك قناةاو طريق ليوصل من خلاله البيانات الراجعه من (loop) ومن ثم اقوم بإدخال بعض البيانات (insert ) ولكن في كل مره اقوم بتجربة الكود
يحدث لي هذا الاستثناء علما بانه ينجح في ادخل عدد لا بئس به من الصفوف ثم يظهر هذا الاستثناء ثم يظهر مره اخرى
ORA-01000: maximum open cursors exceeded
علما بانه يحدث بعد عدد كبير جدا من الصفوف المدخلة وفي كل مرة يختلف عدد الصفوف التي يستطيع ادخالها

Ahmed Khaled
Oracle developer
Java developer
[email protected]
0129572557

ان الله فى عون العبد مادام العبد فى عون اخيه

رابط هذا التعليق
شارك

السلام عليكم ورحمة الله وبركاته

ORA-01000

ORA-01000maximum open cursors exceeded

Cause:A host language program attempted to open too many cursors. The maximum number of cursors per user is determined by the initialization parameter OPEN_CURSORS.

Action:Modify the program to use fewer cursors. If this error occurs often, shut down ORACLE, increase the value of OPEN_CURSORS, and then restart ORACLE.

جزالك الله كل خير

رابط هذا التعليق
شارك

السلام عليكم
انا رايى انك لو كنت بعت الكود اللى بيعمل المشكلة دى كنت لقيت اللى يساعدك افضل
يا ريت لو تبعت الكود اللى بيعمل المشكله
وانا رايى انك بتفتح Cursor فى داخل ال loop وما بتقفلش ال Cursor بعد ما البيانات بترجع
فاتاكد من كودك ويا ريت لو ترسله للتاكد منه


Best regards,




Eng. Saeed H. Keshk

System Analyst - Riyadh Municipality

Mobile KSA : (+966 54) 023-3171

Mobile EGYPT : (+2 012) 426-6883


رابط هذا التعليق
شارك

Oracle.Database.10g.Insider.Solutions_BOOK
Open Cursors. This is a list of currently open cursors obtained from the V$OPEN_CURSORS view. It can be instrumental in figuring out the various activities completed by this session, when such cursor information is available.



Oreilly.Oracle.Essentials.Oracle.Database.10g.3rd.Edition.eBook-LiB_BOOK
The number of private SQL areas, and therefore the amount of memory in the PGA they consume, is determined by the intialization parameter OPEN_CURSORS. You should set this parameter based on the SQL your application submits, the number of active users, and the available memory on the server.

OReilly.Oracle.PL.SQL.Programming.4th.Edition.Aug.2005_BOOK
Oracle Technology Network offers a detailed analysis of how and when PL/SQL closes cursors in an article titled "Cursor reuse in PL/SQL static SQL." Nested anonymous blocks provide an example of one case in which PL/SQL does not implicitly close cursors. For an interesting discussion of this issue see Jonathan Gennick's article, "Does PL/SQL Implicitly Close Cursors?" at http://gennick.com/open_cursors.html.
• If you declare a cursor in a package at the package level and then open it in a particular block or program, that cursor will stay open until you explicitly close it or until your session closes. Therefore, it is extremely important that you include your CLOSE statement for any packaged cursors as soon as you are done with them (and in the exception section as well), as in the following:
• BEGIN
• OPEN my_package.my_cursor;

• ... Do stuff with the cursor

• CLOSE my_package.my_cursor;
• EXCEPTION
• WHEN OTHERS
• THEN
• CLOSE my_package.my_cursor;
• END;

• If you have opened a SELECT FOR UPDATE query, it is especially important to close the cursor as soon as you are done because this query causes row-level locks to be applied.
• You should close a cursor only if it is currently open. You can check a cursor's status with the %ISOPEN cursor attribute before you try to close the cursor:
• IF company_cur%ISOPEN
• THEN
• CLOSE company_cur;
• END IF;

• If you leave too many cursors open, you may exceed the value set by the database initialization parameter, OPEN_CURSORS. If this happens, you will encounter the dreaded error message:
• ORA-01000: maximum open cursors exceeded


If you get this message, check your usage of package-based cursors to make sure they are closed when no longer needed.
This brings us to another important fact about cursors: there are two ways a cursor can be closed. A soft-closed cursor is one that you can no longer use in your application without reopening it. This is what you get when you close a cursor using a statement such as this one:
CLOSE cursor_name;

or even when an implicit cursor closes automatically. However, PL/SQL does not immediately free the session memory associated with this cursor. Instead, it caches cursors to avoid a soft parse should the cursor be opened again, as often happens. You will see, if you look in the V$OPEN_CURSOR view , that the CLOSE alone does not reduce the count of this session's open cursors.
It turns out that PL/SQL maintains its own "session cursor cache"; that is, it decides when to close a cursor for good. This cache can hold a maximum number of cursors, as specified by the OPEN_CURSORS database initialization parameter. A least-recently-used (LRU) algorithm determines which of the soft-closed cursors need to be hard-closed and hence deallocated.
However, PL/SQL's internal algorithm works optimally only if your programs close their cursors immediately after they are through fetching with them. So remember:
If you explicitly open a cursor, you should explicitly close it ... as soon as you are through using it (but not sooner).
There are a few ways that Oracle allows PL/SQL programmers to intervene in the default behavior. One way you can close all of your session cursors, of course, is to terminate the session! Less drastic ways include:
If you'd like to discover for yourself how much UGA and PGA your current session uses, you can run a query like the following:
SELECT n.name, ROUND(m.value/1024) kbytes
FROM V$STATNAME n, V$MYSTAT m
WHERE n.statistic# = m.statistic#
AND n.name LIKE 'session%memory%';


Here is an example that looks at the memory required by objects in the shared pool immediately after database startup:[*] [*] If you're wondering why the columns of data do not line up properly with their headings, it's probably because of the severe limitations of DBMS_OUTPUT. If you don't like it, write your own (grab the query from V$SQLAREA after running the package).
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC DBMS_SHARED_POOL.sizes(minsize => 125)

SIZE(K) KEPT NAME
------- ------ ---------------------------------------------------------------
433 SYS.STANDARD (PACKAGE)
364 SYS.DBMS_RCVMAN (PACKAGE BODY)
249 SYSMAN.MGMT_JOB_ENGINE (PACKAGE BODY)
224 SYS.DBMS_RCVMAN (PACKAGE)
221 SYS.DBMS_STATS_INTERNAL (PACKAGE)
220 SYS.DBMS_BACKUP_RESTORE (PACKAGE)
125 MERGE INTO cache_stats_1$ D USING (select * from table(dbms_sta
ts_internal.format_cache_rows(CURSOR((select dataobj# o, st
atistic# stat, nvl(value, 0) val from gv$segstat where stat
istic# in (0, 3, 5) and obj# > 0 and inst_id = 1) union all
(select obj# o, 7 stat,nvl(sum(num_buf), 0) val from x$kcb
oqh x where inst_id = 1 group by obj#) order by o))) wh
(20B5C934,3478682418) (CURSOR)

The "minsize => 125" means "show only objects that are 125K or larger." This output shows that the package STANDARD occupies the most shared memory, 433K.[ ]
[ ] There is a bug in older versions of DBMS_SHARED_POOL.sizes that results in the amount's being over-reported by about 2.3%. Oracle's package erroneously computed kilobytes by dividing bytes by 1000 instead of by 1024.
SHARED_POOL_SIZE
Bytes set aside for the shared pool.

DB_CACHE_SIZE
Bytes of memory reserved to hold rows of data from the database (may need to be reduced in order to increase the size of the shared pool).

LARGE_POOL_SIZE
Bytes of memory reserved for an optional region of memory that holds UGA for shared server connections. This prevents the variable portion of the UGA from competing for use of the shared pool.

JAVA_POOL_SIZE
Bytes used by the Java memory manager.

STREAMS_POOL_SIZE
Bytes used by the Oracle Streams feature.
If that's too much to fuss with, and you are using Oracle Database 10g or later, the DBA can turn on the Automatic Shared Memory Management features:

SGA_TARGET
Set to a nonzero number of bytes, which indicates the size of the SGA from which Oracle will automatically allocate the cache and pools indicated above.

PGA_AGGREGATE_TARGET
Total amount of memory used by all of the server processes in the instance. Generally, it should be equal to the amount of server memory available to Oracle minus the SGA size.







Que.Oracle.9i.Fundamentals.I.Exam.Cram.2.Nov.2004.eBook-LiB_BOOK
An example of an initSID.ora file follows:
[View full width]

#initialization parameter file: initmydb1.ora
db_name = "mydb1"
instance_name = mydb1
control_files = ("/mydatabases/mydb1/control01.ctl", "/mydatabases2/mydb1/control02.ctl",
"/mydatabases3/mydb1/control03.ctl")
open_cursors = 100
shared_pool_size = 360407040
large_pool_size = 614400
java_pool_size = 52428800
processes = 220
background_dump_dest = /mydatabases/mydb1/logs/bdump
core_dump_dest = /mydatabases/mydb1/logs/cdump
user_dump_dest = /mydatabases/mydb1/logs/udump
db_block_size = 8192
compatible = "9.1.0"
sort_area_size = 65536
sort_area_retained_size = 65536


OPEN_CURSORS : The maximum number of cursors that you want to have opened in your instance at any given time.
Modifying the SPFILE
The SPFILE can be modified; however, it has to be modified indirectly, either through the OEM interface or from the SQLPLUS command line.
Modifying the SPFILE Using OEM
To modify the SPFILE using the GUI OEM interface, you launch the console either in standalone mode or by connecting to an existing repository. Expand the database that you are working on from the Database folder, expand the Instance folder, and click on Configuration.
The interface can be launched both by issuing the command oemapp console at the command line and by launching through the Windows Start menu.
Within the General tab, click the All Initialization Parameters button to see the parameters that you can work with. They are viewed in alphabetical order. If you have logged in as SYSDBA, you can modify both the working instance parameters as well as the SPFILE values, both the dynamic parameters as well as the static ones. If you don't log in as someone with SYSDBA authority, you can alter only dynamic parameters.
Modify the parameter's value column and click OK.
To alter the contents of the SPFILE from the SQL prompt command line, you could enter the following command (this alters the running session and makes the change in the SPFILE):
Alter system set open_cursors=4000 scope=both;

Table 4.2 shows the parameters that the ALTER SYSTEM command can take when you are using an SPFILE.
The change is made in the server parameter file (SPFILE) only, but the changes will not take place until the next startup.

رابط هذا التعليق
شارك

انضم إلى المناقشة

يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.

زائر
أضف رد على هذا الموضوع...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   تمت استعادة المحتوى السابق الخاص بك.   مسح المحرر

×   You cannot paste images directly. Upload or insert images from URL.

جاري التحميل
×
×
  • أضف...

برجاء الإنتباه

بإستخدامك للموقع فأنت تتعهد بالموافقة على هذه البنود: سياسة الخصوصية