How to kill a session that is updating or inserting too heavily
416803Mar 13 2004 — edited Mar 15 2004Hi everyone, it is the first time I try the forum, hopping it will work
I am installing a brand new Oracle 9i system with a standby database. In the past I had a major problem that I would like to get ride off on my new system.
Here is the problem: A user wrote new codes that, in certain circumstance, begin to update a table heavily in a never-ending loop. I have then a process that warn me that the Archive partition is growing too fast, but it is often too late, I dont have time to kill the session before the archive directory get full, my script then delete archived log since I dont want the DB to stop.
The big problem with that is that I have to rebuild my standby database. In addition, I am often not able to kill the session, the session is marked to kill, but continue to generate redo log. I then kill the shared server, this work but I could kill other sessions.
I looked at what Oracle is offering and didnt see nothing that could directly do the job. User profiles do not talk about write restriction and UNDO_POOL of the resource manager seam to apply to a whole Resource Consumer Group.
Do someone already faced this problem and have a good solution for that? It will surely be something like monitoring v$session and killing the process on Oracle or Unix (to be sure it really stops)
Thanks a lot,
Patrick