Can't Kill Procedure

[lesstjm]

I ran a procedure from unix this morning. Then I did a control C to kill it. It kept going. Then I did a kill which supposedly killed the process, it doesn't show up anymore on ps -ef. The procedure is still running. I went into oracle and tried to kill the session and it said it is marked for kill but it won't kill it. How do I kill it without rebooting the database?

[pipo]

you need to kill -9 the parent process of the one you previously killed (SPID in v$process)

[marist89]

The session is probably being rolled back. When it rolls back, it will die.

[lesstjm]

How do I determine the parent process? I don't think it is rolling back anything. The procedure was just reading records from a table and writing them to an os file.

[pipo]

if you look at process column in v$session, you'll notice that it can be of 2 kinds :
1) a process number (an integer) : that means that the connection was made locally on the server
2) something like 193:2 meaning that Net8 was used.

if the connection is not local (case #2), and if you lose control of it (can happen if improperly killed, or if client program crashes ...), then you might ending with a process consumming CPU, who has lost his father process, and keeps on working doing nothing ... the only way I found to get rid of that kind of processes is to kill the parent process. to locate him, you'll need to :

ps -eaf | grep oracle$ORACLE_SID

that will give you all the connections, just keep the ones with (LOCAL=NO), and one of these processes is the one you must kill. look for CPU consumption, and you should find one process taking a LOT of CPU, check that this one is not a normal process (a normal process appears in the sPID column of v$process), and if you find your boy : kill -9 him !!

hope I was clear, and please apologize my english