| 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
This is a multi-part message in MIME format.
------=_NextPart_000_0007_01C033A2.D53FCA60 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Yong,
In fact the join on serial# was provided by Oracle Technical support.
Attached the file for reference. I am going to open another tar and
request Oracle Co. to clear this misleading.
The OS process number generated by your join makes more sense for me. Sep 1 is the database started time which also supposed to be the application started time. Then, the accumulated time is even larger than the smon/pmon time. It is continuously growing. It is scaring me.
SQL> select spid, sid, a.serial#, b.username
2 from v$session a, v$process b
where a.serial#=9 and a.paddr=b.addr; 3
SPID SID SERIAL# USERNAME --------- ---------- ---------- --------------- 1343 172 9 oracle
$ ps -ef|grep 1343
oracle 1343 1 12 Sep 01 ? 28233:41 oracleDEEP (LOCAL=NO)
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of yong huang
Sent: Wednesday, October 11, 2000 4:32 PM
To: Multiple recipients of list ORACLE-L
Subject: 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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: yong321_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------=_NextPart_000_0007_01C033A2.D53FCA60 Content-Type: text/plain; name="session.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="session.txt" From: Aaron Divel [adivel_at_us.oracle.com] Sent: Tuesday, October 10, 2000 10:51 AM To: lwang_at_messagemedia.com Cc: adivel_at_us.oracle.com Subject: TAR:13184289.600 - KILL SESSION NOT CHANGING STATUS IN V$SESSION Article-ID: <Note:1048571.6> Circulation: REVIEWED (EXTERNAL)<Help:KRSTATUS.REVIEWED> Folder: client.Dev2000.Forms.V4 <Topics:3844.0> Topic: Draft Articles <Articles:3844.0..0> Title: JAMMED DATABASE, FIND AND KILL THE CORRECT OS PID Document-Type: PROBLEM Impact: LOW Skill-Level: CASUAL Server-Version: 08.00.04.XX Updated-Date: 30-MAY-2000 20:40:31 References: =20 Shared-Refs: =20 Authors: BRAGSDAL.US Attachments: NONE Content-Type: TEXT/PLAIN Products: 45/SF40 (05.00.15.XX);=20 Platforms: 453; =20 ************************************************************* This article is being delivered in Draft form and may contain errors. Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article. ************************************************************* Problem Description ------------------- You are trying to kill an operating system process ID (PID) for a = specific=20 Oracle job, but you are unsure of the exact process number to kill. You = asked=20 how to find the desired PID number. =20 Solution Description -------------------- Run the following select statement to find the desired process ID (PID) = and=20 username:=20 =20 select spid, sid, a.serial#, b.username=20 from v$session a, v$process b=20 where a.serial# =3D b.serial#;=20 =20 Following is an example of what the query returns:=20 =20 SPID SID SERIAL# USERNAME=20 ---- --- ------- --------=20 11526 1 1 absmith=20 11547 3 1 absmith=20 11551 6 1 absmith=20 =20 Explanation ----------- The SPID is the actual operating system process identifier. =20 =20 The SID is Oracle's session identifier.=20 =20 The SERIAL# is Oracle's session serial number used to identify a = session's=20 objects.=20 =20 After the operating system PID has been killed, type the following (it = is=20 faster than waiting for PMON to clean up the terminated process):=20Received on Wed Oct 11 2000 - 17:46:41 CDT
 |
 |