Conceived this procedure in Visual studio 2008, runs in a separate thread:

void Run() { string oradb = "Data Source=" + Alias.Text + ";" + "User Id=" + Login.Text + ";" + "Password=" + Password.Text + ";"; foring = 0; //--- OracleCommand cmd=null; OracleDataReader dr=null; string str; var ink = FindWindow(null, "Alert locks");//Получаем дескриптор окна do { try { conn = new OracleConnection(oradb); // C# conn.Open(); cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT COUNT(id1) d FROM V$LOCK WHERE request > 0"; cmd.CommandType = CommandType.Text; dr = cmd.ExecuteReader(); dr.Read(); str = dr["d"].ToString(); if ((str != "0") && (foring == 0)) { SetWindowPos(ink, (IntPtr)HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE);//Включает свойство TOPMOST - Дескриптору окну MessageBox(ink, "ATTENTION!!! LOCKS FOUND!!!", "Alert locks message", MB_ICONSTOP | MB_TOPMOST); SetWindowPos(ink, (IntPtr)HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE);//Отключает свойство TOPMOST - Дескриптору окну Thread.Sleep(300); }; } catch (OracleException ex) // catches only Oracle errors { switch (ex.Number) { case 03113: MessageBox(ink, "Lost connection to the database.", "Alert locks message", 0); break; default: MessageBox(ink, "Database error: " + ex.Message.ToString(), "Alert locks message", 0); break; } } catch (Exception ex) // catches any error not previously caught { MessageBox(ink, ex.Message.ToString(), "Alert locks message", 0); } finally { dr.Dispose(); cmd.Dispose(); conn.Dispose(); } } while (foring == 0); }

I decided to constantly monitor the request (I know that you can write differently, this is a test version). As soon as there is a result, a message will be displayed, after which it is closed, monitoring will continue (you can stop it only through a button on the form so that the cycle is not infinite). Actually, after checking what will happen when a session is killed, I found that after the exception was triggered and the next iterations passed, the session turned into 5 sessions (only the sides are different). The question is why and how to get rid of it?

Ps I know about the work of the provider with the pool, and why Open and Dispose and how they can be replaced, but since I am studying recently, maybe I didn’t take something into account -_-

  • Why are you doing ExecuteReader - if the call to ExecuteScalar is much easier? - Pavel Mayorov
  • @Pavel Mayorov, firstly, I haven’t found Kodil for a long time, and secondly, I learned about Oracle Data Provider not so long ago, so I tried the example specified by Oracle itself. - Questioner

2 answers 2

You forgot that the variables dr , cmd and conn can be empty (null) - and then when you try to call Dispose, NRE occurs (I advise you to look at the question What is a NullReferenceException, and how can I fix the code? ).

The second problem is that these variables are initialized only 1 time - and if an error occurred, then on the next. iteration in this variable will be the value that was assigned there at the last iteration.

You need

  1. move declarations or initialization of variables to the beginning of the cycle,
  2. Before calling Dispose, check variables for null.

Alternatively, you can rewrite the code to use the using construct instead of try-finally.

PS I hope the foring field is declared as volatile , otherwise there may be problems with optimization.

It is even better to use the CancellationToken to send a signal to stop.

  • Better, just entered the CancellationToken in the 4th framework, and in 2008 only 3.5 - Inquiring

Answering the question, I found out that it was in the default connection pool settings, or rather in the parameters Min Pool Size=1 и Incr Pool Size=5 . Thus, when killing one current session, when creating a new one, their number grew by 5. Since the default is Min Pool Size=1 , at least one session is always maintained in the pool. If Min Pool Size=0 automatic sessions will not be created (otherwise, even when the pool is cleared, the minimum number of sessions will be automatically created), and when Incr Pool Size=1; not 5 new sessions will be created, but one.