Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle error message after service failure

Highlighted
Fepelof_1
Regular Collector

Oracle error message after service failure

Hi there,

When we try to start "HP Openview Server" service at our application service, we were unsucessfull, and we got the following error message (- we got this every time we try to start this service):

Mon, 06/08/2007 19:24:40 JDBC error 1795: ORA-01795: maximum number of expressions in a list is 1000
, SQL state: 42000 for query: delete from ifc_entityusages where eua_id in (1641474143,.....,529666591)

does anyone know something about this message ? why the service is trying to do this ? Is this SQL error related to the service´s start failure ?

thanks
Fep
2 REPLIES
Daniel Segschne
Super Collector

Re: Oracle error message after service failure

I've got the same error. What can I do?

Thanks
Daniel
anilech
Acclaimed Contributor

Re: Oracle error message after service failure

recently we hit this bug, service desk won't start with ORA-01795 in the logserver.txt. It executes

delete from ifc_entityusages where eua_id in (<more then 1000 values in list>)

and, of course, oracle refuses to do it. 

 

firstly we try to delete those records by splitting that huge in() list into several delete statements which have less then 1000 values in their in(), but this didn't help.

 

next, we set oracle trace on servicedesk oracle user in database logon trigger to see what statements it executes before this wrong delete command. From trace we found that just before the deletion servicedesk populates that huge in() list by following query: 

select rep_sessions.ses_oid from rep_sessions,  rep_servers where rep_servers.svi_hostname = '<our application server hostname>' and rep_servers.svi_ipport = 30999 and rep_servers.svi_oid = rep_sessions.ses_svi_oid

so we just delete those records with

delete from rep_sessions where ses_oid in (
  select rep_sessions.ses_oid
    from rep_sessions,  rep_servers
   where rep_servers.svi_hostname = '<our application server hostname>'
     and rep_servers.svi_ipport = 30999
     and rep_servers.svi_oid = rep_sessions.ses_svi_oid);

 and service desk starts fine.

 

//Add this to "OnDomLoad" event