Please include the syntax also if possible to help others
Pichaimani Balasubramanian, July 20, 2001 - 1:19 am UTC
that works from the console
eric givler, April 25, 2003 - 8:05 pm UTC
Orakill works from the server, but not through a terminal services session. The error reported here matches the same error we got when running the client.
Looking for a more elegant solution than orakill
Matt, February 23, 2004 - 9:43 pm UTC
Win2K SP 4; 9.2.0.4
Here is my problem:
1) Running a data load (a bulk insert) from sqlplus
2) The window is killed (using 'x' in corner of window)
3) Data Load is re-started and encounters ORA-54
4) Investigation reveals the previous session is still 'active' and has locks out on a few tables. Also showsql indicates an insert in in progress.
5) Carry out alter system kill... and session in now status 'KILLED'
6) Leave this for 1 hour to see if PMON will clean up - it does not.
What are my options here?
* I can bounce the DB - would rather not
* I can use orakill - I can, but it makes me nervous (:o))
* I can enable dead client detection (DCD)
Anything else?
What is a "good" setting of EXPIRE_TIME for DCD - 60 seconds?
I am just looking for a "clean" fix for this annoying problem.
Can you please explain why this occurs?
February 24, 2004 - 6:34 am UTC
pmon did clean up.
it'll happen "slowly", and it'll take a while as well -- and it could be cleaned up totally and still be in the killed state (waiting for the client which is never coming back. tcp/ip, in particular tcp/ip implementations on windows, do not necessarily tell us "client is gone").
did you check to see if the session still had locks or just if the session "still exists".
You might consider using dbms_job to avoid the fragile client environment. Instead of running that long running thing interactively, schedule it as a job.
Response to previous post
Matt, February 24, 2004 - 5:19 pm UTC
) Running a data load (a bulk insert) from sqlplus
2) The window is killed (using 'x' in corner of window)
3) Data Load is re-started and encounters ORA-54
4) Investigation reveals the previous session is still 'active' and has locks
out on a few tables. Also showsql indicates an insert in in progress.
5) Carry out alter system kill... and session in now status 'KILLED'
6) Leave this for 1 hour to see if PMON will clean up - it does not.
What are my options here?
* I can bounce the DB - would rather not
* I can use orakill - I can, but it makes me nervous (:o))
* I can enable dead client detection (DCD)
Anything else?
What is a "good" setting of EXPIRE_TIME for DCD - 60 seconds?
I am just looking for a "clean" fix for this annoying problem.
Can you please explain why this occurs?
Followup:
pmon did clean up.
[Mat - It did not. I understand that PMON will clear the "KILLED" session and all locks. The session was still visible as "KILLED" and the locks were still active after 1 hour. This is a long time (but on a rather quiet development system)
it'll happen "slowly", and it'll take a while as well -- and it could be cleaned
up totally and still be in the killed state (waiting for the client which is
never coming back. tcp/ip, in particular tcp/ip implementations on windows, do
not necessarily tell us "client is gone").
did you check to see if the session still had locks or just if the session
"still exists".
[Mat - Yes, the locks and session were still there]
You might consider using dbms_job to avoid the fragile client environment.
Instead of running that long running thing interactively, schedule it as a job.
[Mat - This sounds like an approach, however I am using a set of sqlplus scripts for a data load (DDL and anonymous PL/SQL ), I don't think it is straighforward to dbms_job these scripts
I am more concerned about the set of java processes that will be connecting to the DB. If I understand this issue correctly then if (for some reason) one of these processes does "unexpectedly" then some session/locks could be left around for a significant amount of time.
so, is DCD the reolution? If so, is an EXPIRE_TIME of 60 seconds a "bad" value? (Obviously this depends and it *will* be tested etc in our configuration - just want your gut feel as to whether this is too small)
]
Cheers,
February 24, 2004 - 9:04 pm UTC
DCD, 60 seconds or even less should be more than sufficient.
the network traffic with dcd is very tiny.
Document on Orakill
Gururaj Kulkarni, February 24, 2004 - 10:37 pm UTC
Tom,
Could you give me a link on Orakill ? I would like to read about Orakill.
Thanks
-Gururaj
February 25, 2004 - 8:40 am UTC
metalink.oracle.com