Oracle Cursor Leak

Hello All,

This week we had a problem with the cursors.:) ORA-1000

We made a change to our oracle portal website to use the App server Datasource instead of Apache BasicDataSource.


While testing this across instances we detected a cursor leak. As we all know again it was a problem with a stmt or result set object which was not closed.

Detecting the open result set/statement object is a bit tough job in a huge code base.

This is how the object was identified

Query to find our open cursors

select a.value, s.username, s.sid, s.serial#
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' and username = 'EXTERNALPORTAL';

This gives the open cursor count and SID which opened it.

This returned me the sid 937 for my Application, used this sid against the V$open_cursor view.

for eg:

SELECT COUNT(*), address
FROM v$open_cursor
WHERE sid = 937
GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);

The result lists each cursor, which has been opened by the session more than once in descending order.

Returnes the count of cursor and the memory Address of the query which opened it.

SELECT sql_fulltext
FROM v$sql
WHERE address = '6C806444'


this returned me the query which created the issue. Just fixed the code. and monitored the cursor count for about a day it is good.


Thanks
Prem

Java/SOA Project

Hello All,

Please take some time to share and comment about the thoughts below.

The plan is to develop a project on a local system and integrate with other systems by implementing SOA.

The technologies which i have chosed to do this

Technolgy :java1.4,spring mvc 2.5,hibernate 3.1,JMS,quartz Scheduler,BPEL,BAM,WebServices,mysql,Oracle XE,Oracle SOA suite.

The IDE for this project will be Jdeveloper10.1.3

As a initial Step i have installed all the Software on my local system.

SOA suite installation

Problems faced during installation: As the default port for mysql is 8080 ,i had an issue in configuring XE which has 8080 as default port.

So i followed this blog to solve this

http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html

Server Configuration:

a. Create a Oc4j_instance name. (eg:oc4j_prem)
b. just deploy a webapp to test with the index.page
c: i wish to run this test app on portnumber 7777


So i navigated to "cluster topology"
clicked on "Runtime Ports"
configred the default-web-site port to 7777 and selected the protocol as http.

Apply the changes and restart the oc4j_instance name

http://localhost:7777/webapp_name/test.html

will come with an action plan and a use case for this project...

Welcome

Friends ,

Welcome to my Blog...

Objective of this blog is to share all the technical challenges ,discussion about java.I am also considering this place for my Future reference .I would be really Happy if my blogs help others in their work.
Thanks
Prem