Error in backend system/Web service; ORA-01795: maximum number of expressio

[Rich_Turnquist]

I am running the Role Usage Synchronization job in ERM and am receiving the following error:

Error in backend system/Web service; ORA-01795: maximum number of expressions in a list is 1000.

LOG:

2010-02-18 10:36:41,901 [Thread-7405] ERROR ORA-01795: maximum number of expressions in a list is 1000

java.lang.Throwable: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)

at com.sap.sql.jdbc.basic.BasicPreparedStatement.executeUpdate(BasicPreparedStatement.java:103)

at com.sap.sql.jdbc.direct.DirectPreparedStatement.executeUpdate(DirectPreparedStatement.java:325)

at com.sap.sql.jdbc.common.CommonPreparedStatement.executeUpdate(CommonPreparedStatement.java:252)

at com.sap.engine.services.dbpool.wrappers.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:254)

at com.virsa.re.dao.jdbc.RoleUsageDAO.deleteUsers(RoleUsageDAO.java:477)

at com.virsa.re.bo.impl.RoleUsageByUserBO.getChangedUsers(RoleUsageByUserBO.java:171)

at com.virsa.re.bo.impl.RoleUsageByUserBO.getRoleUsage(RoleUsageByUserBO.java:73)

at com.virsa.re.backgroundjobs.RoleUsageSync.execute(RoleUsageSync.java:60)

at com.virsa.service.backgroundjobs.BackgroundTask.run(BackgroundTask.java:53)

at java.util.TimerThread.mainLoop(Timer.java:461)

at java.util.TimerThread.run(Timer.java:408)

Any help is appreciated.

Thanks,

Peggy

[Rich_Turnquist]

After talking with SAP, my problem has been resolved. I had to put in a start date in the "role usage Synchonization job" in order to work around the error. Once I put in a start date of 3/8/09 (1 year prior), the job finished.

SAP is still going to fix this issue in a later support pack since the real problem is that Oracle has a limit of 1000 deleted users. This error only occurs if you have more than 1000 SU01 records that were deleted. Just be aware that if you don't put in a start date, the syncronization goes back to the beginning of time, so hitting 1000 deleted users may not be hard to do.

Thanks,

Peggy

[Rich_Turnquist]

Yes, I saw that it will be fixed in SP13 which is due out in August. Unfortunately this is in our PRODUCTION system and it needs to be fixed sooner so we can satisfy some of our audit requirements that are due this month. I can't really wait until August or GRC is of little use to us at this point. Do you know of any reason I couldn't get a fix in the form of an OSS note rather than having to wait for a support pack?

Thanks,

Peggy

[Former Member]

Hi Peggy,

SAP Support has updated the OSS message with the possible options. Also SAP Support has sent an e-mail to "Barabara Garrett" for the best available time to discuss this issue.

Please respond back with your best avaialable timings for today.

Thanks & Regards,

Sirish Gullapalli.

[Former Member]

Hi Peggy,

As discussed please update the OSS message with your findings after completing the job in ERM.

Thanks & Regards,

Sirish Gullapalli.

[Rich_Turnquist]

Hi Amol,

Right now the SAP developers say this error is indeed a problem and have sent it up to R&D.

We have 3 systems connected to GRC (ECC 6.0, BI, and SRM). This is the first time I am running the ERM sync jobs. It ran fine for the ECC 6.0 system (which has the most number of users and roles). It ran fine for the SRM system (which has the least number of users and roles). The BI system is where the error happens. This system has more users than our SRM system but way less than our ECC system.

There is no system dump in the backend system.

I'll keep you posted on what SAP comes back with.

Thanks,

Peggy

[Former Member]

Hi Peggy,

It is identified as an issue and will be fixed in future SP.

CSS message was updated with this information.

Best Regards,

Sirish Gullapalli.

[Former Member]

Peggy,

At what stage you get this error in ERM role usage sync job? This job operates as below -

1) based on Sync date provided it fetches roles assigned to all the users changed in given period from backend.

2) It then gets the tcode usage for users from RAR virsaccactionusage service

3) It fetches tcodes in a role and then calculates the usage information per role for each user.

Can you check and provide the job history? Also check if there is any dump on the backend system against which role usage sync is done. You can also verify if there is any error in RAR Logs.

What support pack level are you at?

I remember getting similar error a while back. Not able to recollect exact fix, but above information should provide some hint.

With SP06 onwards ERM parameter "noOfUsersForUAR" is used to control user batch size for doing ERM role usage sync. Having a larger value for this parameter can sometime cause issues. You can check value for this parameter in vt_gbl_ermconfig table.

Regards,

Amol

[former_member366047]

Peggy,

I just saw your message in our CSS database. It is with our Development Support, and he will respond to your message asap.

Thanks!

Ankur

SAP GRC RIG