[acuartero]

FYI,

Using 5.5.5.2 we experienced an "ORA-01000 Maximum Open Cursors Exceeded" error during order integration and were able to trace the problem to the following SQL

select distinct order_release_gid from order_release where order_base_gid=:1

After about an hour this lead to an out of memory condition on the app server necessitating a restart.

We reported this to Oracle and they quickly replied that this is a known issue and provided a patch for bug 8507644 (which we are now testing).

If you experience a similar cursor leak the following SQL's may be helpful in finding the problem.

-- Monitor OTM Cursor usage
select c.user_name, c.sid, count(*), sql.SQL_TEXT, o.object_name Package_name,
sql.PROGRAM_ID, sql.PROGRAM_LINE# Line#
from v$open_cursor c, v$sql sql, dba_objects o,
(select s.sid from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid = a.sid
and b.name = 'opened cursors current'
-- More than 50 cursors open in a session
and a.value > 50 and s.username = 'GLOGDBA') t1
where c.sql_id = sql.sql_id
and o.object_id(+) = sql.PROGRAM_ID
and c.sid = t1.sid
group by c.user_name, c.sid, sql.SQL_TEXT, o.object_name, sql.PROGRAM_ID, sql.PROGRAM_LINE#
having count(*) > 20
order by sid, count(*) desc;

--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;

Thanks,
Alan

[chrisplough]

Alan,

Thanks a lot for sharing this. I ran into a client recently who experienced the same issue. Much appreciated!

--Chris

[josephliang]

You are lucky.
I spent almost two weeks convincing Oracle that this is a bug.
According to Development, the bug was tied around the mediator and the OBModFullImpact did not close the sql query.
For Version 5.5 CU5: it is fixed in RU5
For Version 6.0: it will be fixed in RU2

[josephliang]

There is another similar bug relates DirectSqlUpdate

BUG 8672390 - Client with high integration volume using a lot of direct SQL updates encountered Ora-01000: maximum open cursors exceeded errors during integration

It is fixed in OTM 5.5 CU5 RU6 patchset.

[josephliang]

According to Metalink Note 866873.1 - "ORA-01000: Maximum Open Cursors Exceeded" Errors Are Occurring During Integration
the issue affects OTM Version: 5.5.04 to 6.1

Version 5.5 CU4 - A Patch is available upon request, please open a MetaLink SR for a patch for Bug 8668288
Version 5.5 CU5 - Download and Install RU6 available on MetaLink (includes fix for Bug 8672390)
Version 5.5 CU6 - Download and Install RU1 (includes fix for Bug 8672391) - Approximate Availability is End of October, 2009.
Version 6.0 - Download and Install RU2 (includes fix for Bug 8672392 - Approximate Availability is End of August, 2009
Version 6.0 CU1 - CU1 includes fix for Bug 8672394 - Approximate Availability is End of 2009