list of connected users outdated

LionelMartz

Hallo everyone,

We switched to direct connect and are experiencing different problems. One of them is, that the list of users, that are "currently" connected to database, is having orphaned entries. (ArcGIS 10.2.1 on oracle 11g) 

To obtain the users we more or less followed the arctical here:How To: Obtain a listing of user processes and locks from a geodatabase when there is no ArcSDE service 

Our current code lookes like that:

 <SPAN class="keyword token">private</SPAN> <SPAN class="keyword token">static</SPAN> List<SPAN class="operator token"><</SPAN>UserObject<SPAN class="operator token">></SPAN> <SPAN class="token function">ReadUsersFromOracle</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> <SPAN class="keyword token">new</SPAN> <SPAN class="token class-name">List</SPAN><SPAN class="operator token"><</SPAN>UserObject<SPAN class="operator token">></SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">using</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> connection <SPAN class="operator token">=</SPAN> <SPAN class="keyword token">new</SPAN> <SPAN class="token class-name">Oracle<SPAN class="punctuation token">.</SPAN>DataAccess<SPAN class="punctuation token">.</SPAN>Client<SPAN class="punctuation token">.</SPAN>OracleConnection</SPAN><SPAN class="punctuation token">(</SPAN>ConnectionString<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Open</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">try</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> getuserCommand <SPAN class="operator token">=</SPAN> <SPAN class="string token">"SELECT SDE_ID, OWNER, NODENAME, START_TIME, XDR_NEEDED, DIRECT_CONNECT FROM PROCESS_INFORMATION"</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> command <SPAN class="operator token">=</SPAN> <SPAN class="keyword token">new</SPAN> <SPAN class="token class-name">Oracle<SPAN class="punctuation token">.</SPAN>DataAccess<SPAN class="punctuation token">.</SPAN>Client<SPAN class="punctuation token">.</SPAN>OracleCommand</SPAN><SPAN class="punctuation token">(</SPAN>getuserCommand<SPAN class="punctuation token">,</SPAN> connection<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;command<SPAN class="punctuation token">.</SPAN>CommandType <SPAN class="operator token">=</SPAN> System<SPAN class="punctuation token">.</SPAN>Data<SPAN class="punctuation token">.</SPAN>CommandType<SPAN class="punctuation token">.</SPAN>Text<SPAN class="punctuation token">;</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> reader <SPAN class="operator token">=</SPAN> command<SPAN class="punctuation token">.</SPAN><SPAN class="token function">ExecuteReader</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">while</SPAN> <SPAN class="punctuation token">(</SPAN>reader<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Read</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> sdeid <SPAN class="operator token">=</SPAN> Convert<SPAN class="punctuation token">.</SPAN><SPAN class="token function">ToDecimal</SPAN><SPAN class="punctuation token">(</SPAN>reader<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> user <SPAN class="operator token">=</SPAN> Convert<SPAN class="punctuation token">.</SPAN><SPAN class="token function">ToString</SPAN><SPAN class="punctuation token">(</SPAN>reader<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> nodeName <SPAN class="operator token">=</SPAN> Convert<SPAN class="punctuation token">.</SPAN><SPAN class="token function">ToString</SPAN><SPAN class="punctuation token">(</SPAN>reader<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> connTime <SPAN class="operator token">=</SPAN> Convert<SPAN class="punctuation token">.</SPAN><SPAN class="token function">ToDateTime</SPAN><SPAN class="punctuation token">(</SPAN>reader<SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> xdr <SPAN class="operator token">=</SPAN> Convert<SPAN class="punctuation token">.</SPAN><SPAN class="token function">ToString</SPAN><SPAN class="punctuation token">(</SPAN>reader<SPAN class="punctuation token">[</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">var</SPAN> isDc <SPAN class="operator token">=</SPAN> Convert<SPAN class="punctuation token">.</SPAN><SPAN class="token function">ToString</SPAN><SPAN class="punctuation token">(</SPAN>reader<SPAN class="punctuation token">[</SPAN><SPAN class="number token">5</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;result<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Add</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">new</SPAN> <SPAN class="token class-name">UserObject</SPAN><SPAN class="punctuation token">(</SPAN>sdeid<SPAN class="punctuation token">,</SPAN> user<SPAN class="punctuation token">,</SPAN> nodeName<SPAN class="punctuation token">,</SPAN> connTime<SPAN class="punctuation token">,</SPAN> xdr<SPAN class="punctuation token">,</SPAN> isDc<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">}</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">}</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">finally</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Close</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">}</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="punctuation token">}</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">return</SPAN> result<SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Does anyone has an idea, how to trigger any update so that the list returns only actually connected users?

Kind regards,

Lionel

LionelMartz

Found solution.

I could execute the code given in How To: Remove orphaned keyset tables in Oracle  with the same command. But I had to exchagne the "sde". by the current user schema that I am working with. Only problem is the user rights needed.