Re: problem connecting to db.

On Jan 6, 5:52 am, "Mr. X." <no_spam_please_at_nospam_please.com> wrote:
> I don't know if this is relevant, but :
> A specific program's memory-use is getting larger and larger
> every day, (I don't have the program's code) .
> So every day at night, I do :
> rerun the service with above problem, and kill its oracle-session
> (There is no other choice, since the process is black-box,
> and I have to kill its session, because there will be more sever problems if
> not).
>
> The code :
> alter system kill session ...
> (That's the only thing I do for killing that session, in VB 6.0)
>
> Do you know a better way of killing the session ?
> (By the ctime is saw on v$lock,  there are about 20 rows,
> and some of them are 20 days long, and may be irrelevant ...
> also one of the rows seems to be at the exact time since killing the last
> session yesterday -
> I think that ctime count the time in seconds from time the process was
> running).
>
> I can do some code for tracing (once for 5 minutes) the v$lock,
> but what do you advice me to do first ?
>
> I didn't see any rows in v$lock that occurs at the same time I cannot
> connect to the database
> (the first problem I've described).
> Does v$lock is related to the first problem I have described (failed to
> connect to db) ?
>
> Any clue ?
>
> Thanks :)

I am a bit confused by the following:
"The code :
alter system kill session ...
(That's the only thing I do for killing that session, in VB 6.0)"

Is that the code that you execute when you want to kill the black box program's session, or is that the code that you use in your VB 6 program just before that VB 6 program closes?

If you are using that SQL statement just before the VB 6 program closes, I would suggest a different approach:   OraCon.Close
  Set OraCon = Nothing

Oracle's PMON will automatically clean up after sessions terminate unexpectedly - for example, if you force the black box application to terminate using Task Manager or PSKILL, but it does not happen immediately.

If the program is written using ADO and VB 6, and it is making use of transactions (OraCon.BeginTrans, OraCon.CommitTrans) and for some reason (such as an untrapped error), causes a subroutine/function to prematurely terminate before the OraCon.CommitTrans or OraCon.RollbackTrans is hit, that transaction remains active, and the next OraCon.BeginTrans becomes a child transaction of the previous OraCon.BeginTrans. This means that rows that were altered between the initial OraCon.BeginTrans and the untrapped error will never be committed, and will continue to show entries in V$LOCK until the session terminates (or in some cases until the client is restarted... this is one of the reasons why it is generally a bad idea to run client applications on the database server).

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Jan 06 2008 - 08:19:48 CST