A database error occurred while executing Stored Procedure "EDA_TRUSTEES.GETLIST_DGM"

Question

I am trying to figure out an error we are seeing when using core service to create a folder or any item (basic operations)...

 A database error occurred while executing Stored Procedure "EDA_TRUSTEES.GETLIST_DGM".
 ORA-01000: maximum open cursors exceeded
 ORA-06512: at "TCMDBUSER.EDA_TRUSTEES", line 1267
 ORA-06512: at line 1

I have viewed this Tridion CMS & Oracle: ORA-01000: maximum open cursors exceeded and it says its a known defect with Tridion 2011, but I don't think in that post they are using core service.

I also receive this error when migrating with Content Porter 8.

Seems like it could be a product defect, since we are just hitting API. Even if we increase cursors, it will just mask the issue, right?

UPDATE

It is worth mentioning that we are still leveraging CoreService2013 within a mixed mode environment CMS = Web 8.1.1 & CD = 2013 SP1 HR1.

I wonder since these are basic operations within cms db and that is web 8; we should be using CoreService2015 (web 8 API) ?

Answer 1

bump up the 'open_cursor' to 2000 (from 400) seem to have helped. For example: ALTER SYSTEM SET OPEN_CURSORS=2000 SID='*' SCOPE=BOTH;

Of course, the 2000 value is varied from one system to another...you just have to do trial and error to see what value is best.

Answer 2

I think that that Stored Procedure is used when a Directory Service user ("LDAP user" if you wish) logs in. This means it is a very commonly used Stored Procedure and thus it is very unlikely a general product defect.

Much more likely is that the number of open cursors is exceeded by a suboptimal query execution plan in the database. These plans are created dynamically based on DB statistics and rely on DB maintenance (up-to-date statistics, well-maintained indices).

So, the problem may be related to DB mainenance (or lack thereof). A DBA should be able to provide more insight in whether this is indeed the case.

Answer 3

Are you getting this error only when you try to create an item using Core service, but you have no issues with create a item inside CME manually?

If so, we may be logging as different users with different permission rights for core service and CME Web.

If the error occurs in both in CME and core service, it could be that we installed a hotfix, but forget to run the sql scripts available with the hotfix, so the dll's and stored procedures parameters are out of sync

Give a try using the latest coreservice API like CoreService201501.svc rather than using the older API's(CoreService2011.svc)