| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle sessions and OS processes
Hi, Linda,
You shouldn't join v$session and v$process on serial#, which is totally different serial numbers in these two views. The column to join on is v$session.paddr and v$process.addr.
If you're sure the runaway process should be killed and you can't do that in v$session, it's safe to kill it at OS level, including kill -9. If you don't, you'll notice that this process may indeed be doing a lot useless work by truss -p the_OS_PID (use strace or trace instead of truss if on other UNIX). Killing it on command line doesn't mean that v$session won't show it any more. In fact it may still show it as ACTIVE even though you still can't ALTER SYSTEM KILL it. I'm not very sure why but I suspect it's waiting for PMON to clean this v$session entry. You can also try the svrmgrl command oradebug wakeup <PMON_PID> to wake up PMON. I don't know if this is Oracle-supported.
Yong Huang
yong321_at_yahoo.com
you wrote:
Hi,
I believe the session '172, 9' was hanging since 21-SEP. But when I tried to
kill session, I go ORA-00030: user session ID does not exist
Oracle Technical support said that only two choices:
1. Wait for pmonto clean it. (Since the session could not be marked as
killed,
I do not believe pmon is doing anything for this) 2. Boune database (This is not a choice for a 24/7 shop.)
Does anybody know how Oracle processes related to the OS processes? Is it too dangerous to kill the OS processes (19732,28381,23335)?
select spid, sid, a.serial#, b.username
from v$session a, v$process b
where a.serial# = b.serial# and a.serial#=9;
SPID SID SERIAL# USERNAME --------- ---------- ---------- ---------------
19732 172 9 oracle 29381 172 9 oracle 23335 172 9 oracle
$ ps -ef |grep 19732
oracle 19732 1 0 Oct 07 ? 0:05 oracleDEEP (LOCAL=NO)
$ ps -ef |grep 29381
oracle 29381 1 0 08:34:30 ? 0:01 oracleDEEP (LOCAL=NO)
$ ps -ef |grep 23335
oracle 23335 1 0 Oct 09 ? 0:01 oracleDEEP (LOCAL=NO)
SELECT s.SID,s.serial#,start_time,current_time
from v$session_longops l,v$session s
where s.status='ACTIVE' and (sofar < totalwork) and l.sid=s.sid;
SID SERIAL# START_TIME CURRENT_TIME
---------- ---------- -------------------- --------------------
172 9 21-SEP-2000 13:45:24 21-SEP-2000 13:45:24
172 9 21-SEP-2000 10:02:17 21-SEP-2000 10:02:17
select sid,serial#,LAST_CALL_ET,ROW_WAIT_ROW#,status from v$session
where sid=172 and serial#=9;
SID SERIAL# LAST_CALL_ET ROW_WAIT_ROW# STATUS
---------- ---------- ------------ -------------
--------
172 9 1706983 99
ACTIVE
SQL> alter system kill session '172,9';
Thanks.
Linda
 |
 |