ORA-01000- What is it?How you can fix it!

You may hit ORA-01000 maximum open cursors exceeded in your daily life.

So what is a cursor? Why you are hitting this error? In this post let us see inside of this error.

Cursors in Oracle

A cursor is a temporary work area created in the system memory when a SQL statement is executed. It can hold more than one row but can process only one row at a time. A set of rows the cursor holds is called an active set. The cursor might be used for retrieving data on a row-by-row basis like a looping statement.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.

I have been checked my current settings as below:

SQL> show parameter cursor
Name       Type      Value
processes  integer   1500
SQL> show parameter session
Name       Type      Value
processes  integer   1655
SQL> show parameter process
Name       Type      Value
processes  integer   1500

So the question is How To Determine the SQL Expression That May Be Causing ORA-01000 on our system. There is a metalink note available for this question’s answer.

Here is the steps:

1. Find top 10 sessions which are currently opening most cursors.(In our example my open_cursors has been set to 1500)

# sqlplus / as sysdba
SQL> select * from ( select ss.value, sn.name, ss.sid
 from v$sesstat ss, v$statname sn
 where ss.statistic# = sn.statistic#
 and sn.name like '%opened cursors current%'
 order by value desc) where rownum < 11 ;

You may also go with the below query:

SQL> select c.sid as "OraSID",
c.address||':'||c.hash_value as "SQL Address",
COUNT(c.saddr) as "Cursor Copies"
from v$open_cursor c
group by
having COUNT(c.saddr) > 2
order by 3 DESC ;

Ps: Do not forget. That SQL for standalone database, If you have RAC you need to use gv$ instead of v$ views.

2. Identify session and SQL text by using below queries:

SQL> select A.SID as sid, a.status as status, A.EVENT as event, a.seconds_in_wait as wait, a.blocking_session as blk_sesn , a.prev_sql_id as SQL_ID from v$session a where a.sid='xxx'; << If you use first query
SQL> select SQL_FULLTEXT from v$sql where ADDRESS ||':'||HASH_VALUE = '<SQL Address>' ; << If you use second query

3. So now you have 2 option to can solve this error

a. Kill session if its INACTIVE status,
b. Increase this parameter dynamically.

If you go with option b than the issue is:

SQL> ALTER SYSTEM SET open_cursors = 3000 SCOPE=BOTH;  << If its standalone database
SQL> ALTER SYSTEM SET open_cursors = 3000 SCOPE=BOTH SID='*';  << If its RAC database

You can use event 1000 for additional diagnosis to can solve this problem. For details please check reference guide

How To Determine the SQL Expression That May Be Causing ORA-01000 [ID 1333600.1]


Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Connecting to %s

%d blogcu bunu beğendi: