Maximo Open Forum

 ORACLE CURSORS EXCEEDED

  • Reporting
Abhinav Bhuria's profile image
Abhinav Bhuria posted 08-10-2021 14:04
Hi All,

We are facing ORA-01000: maximum open cursors exceeded problem in our production environment. On checking through the v$session and v$open_cursors tables we could see one SQL query causing the spike in open cursors and it seems to be coming from Work Order Print Report(which is custom btw). Below is the open method of the data set containing the sql in question

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText = " select WORKORDER.WONUM, WORKORDER.DESCRIPTION WDESC, WORKORDER.SITEID, WORKORDER.workorderid, WORKORDER.WOEQ9, "
+ " WORKORDER.WORKTYPE, WORKORDER.HASCHILDREN, "
+ " (select ldtext from longdescription where ldkey = workorder.workorderid and ldownercol = 'DESCRIPTION' and ldownertable = 'WORKORDER') LONGDESC "
+ " from WORKORDER "
+ " where wonum = '" + rows[0]["wonum"] + "' "
+ " AND WORKORDER.SITEID = '" + rows[0]["siteid"] + "' "
;

maximoDataSet.setQuery(sqlText);

Also the fetch method of the same.
if (!maximoDataSet.fetch())

return (false);

row["wonum"] = maximoDataSet.getString("wonum"); 

row["wdesc"] = maximoDataSet.getString("wdesc");

row["siteid"] = maximoDataSet.getString("siteid");

row["workorderid"] = maximoDataSet.getInteger("workorderid"); 

row["woeq9"] = maximoDataSet.getString("woeq9");

row["worktype"] = maximoDataSet.getString("worktype");

row["haschildren"] = maximoDataSet.getInteger("haschildren");

row["longdesc"] = maximoDataSet.getString("longdesc"); 

return (true);

Again we are not opening any dataset in fetch method which normally causes such issue in case we forget to close the same.
Any inputs/ideas on this issue are welcome

Thanks in Advance

P.S Maximo version is 7.5, Database is Oralce 12c
Danielle Shaw's profile image
Danielle Shaw posted 08-10-2021 14:41

Hi,

I have a couple questions on how the report is written. maximoDataSet is usually the data set for the main table of the report so why are you using rows[0]["wonum"]? rows[0] is usually used in a child table (sub report) to get a value from the parent table. Aren't you running off the user's query in Maximo to generate the work orders that show the report? Or do you have a parent table that is deriving the work order/site id in some other way and this data set is for a child table?

Happy to look at the report if you want to include the .rptdesign file.

Abhinav Bhuria's profile image
Abhinav Bhuria posted 08-11-2021 15:49
Hi Danielle,

Thanks for having a look at it. I am not sure I have the answers to your questions as I am new to reporting and just ended up on the report while debugging the issue. I have attached the rptdesign file of the report for your reference(though I have modified some things such as removed client specific data eg text and logos). There are multiple datasets in that report. The one containing the sql causing the issue is named IFCMNoOps. Let me know if you need anything else?

P.S This forum won't let me attach files with extension .rptdesign so attaching a txt version of the same. Just change the extension back to .rptdesign and it should work
Attachment  View in library
wo_print.txt 816 KB
Barry Blanchard's profile image
Barry Blanchard posted 08-12-2021 08:35
I had the same issue earlier this week. It presented itself as a report problem causing excessive cursors, and turned out to be caused by a full Disk on the Maximo Server. Something to check.
Abhinav Bhuria's profile image
Abhinav Bhuria posted 08-13-2021 05:13
Hi Barry,

Thanks for your response. Indeed the space on the Maximo Servers is running a bit low. Will have a look at this.
Thanks Again

Related Content