Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: hang/wait problem

RE: hang/wait problem

From: <Colin.Shaw_at_phs.com>
Date: Mon, 12 Jun 2000 19:12:53 -0700
Message-Id: <10526.108822@fatcity.com>


This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFD4DC.E57E0C40
Content-Type: text/plain;
 charset=windows-1252
Content-Transfer-Encoding: 7bit

Re:
>V$PROCESS no longer holds the row for the session in question after the
"kill session."  

Actually, it "sort of" does. When you kill a session, the row is removed from V$PROCESS but the V$SESSION row now points to PID 1, the PSEUDO process until the V$SESSION row is completely removed, i.e. once the client has ACK'd the session delete.  

Colin.  

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Monday, June 12, 2000 9:52 AM
To: Multiple recipients of list ORACLE-L Subject: Re: hang/wait problem

I see this happen frequently. You kill a session from within oracle and it doesn't go away. If the platform is unix, you might want to try to kill the server process too. To get the PID use this query....

select SPID from v$process p, v$session s where s.sid = '<SID_TO_KILL>' and p.addr = s.p_addr;

You need to run this quest before trying to kill the session though. V$PROCESS no longer holds the row for the session in question after the "kill session." The only other ways to clean up "marked for kill" sessions is to a) boucne the instance, or b) wait it out.    

Ruth Gramolini wrote:

Try looking at v$lock and get the SID of the user who is causing the trouble from this and check v$session for the serial# or this user using the SID. Then you can issue this sql:
alter system kill session 'SID,SEREAL#';Ruth B. Gramolini ORACLE & DB2 DBA
VT Dept. of Taxes
ph# 802.828.5708
fax# 802.828..3754
rgramolini_at_tax.state.vt.us
to kill the user's session.

HTH

To: Multiple recipients of list ORACLE-L Sent: Friday, June 09, 2000 4:06 PM

> Hello,
>
> I'm having a hang/wait problem with one of my systems, and this is the
> information I've gleaned. I want to alter a table nologging:
>
> SVRMGR> alter table bvadmin.MR_purchased_items nologging ;
>
> alter table bvadmin.MR_purchased_items nologging
>
> *
>! !
> ORA-00054: resource busy and acquire with NOWAIT specified
>
>
> I'm trying to kill the user I believe has the table:
> 11 rows selected.
>
> SVRMGR> select * from v$session ;
>
> SADDR SID SERIAL# AUDSID PADDR USER# USERNAME
>
> -------- ---------- ---------- ---------- -------- ----------
> ---------------
> AB1C6328 1 1 0 AB1A1A04 0
>
> AB1C6B54 2 1 0 AB1A1CF4 0
>
> AB1C7380 3 1 0 AB1A1FE4 0
>
> AB1C7BAC 4 1 0 AB1A22D4 0
>
> AB1C83D8 5 1 0 AB1A25C4 0
>
> AB1C8C04 6 1 0 AB1A28B4 0
>
> AB1C9430 7 68 0 AB1A2BA4 0
>
> AB1CA488 9 348 1943952 AB1A3474 41 BVADMIN
> <-------
> AB1CACB4 10 15 0 AB1A2E94 0
>
> AB1CC538 13 8768 0 AB1A3764 0 SYS
>
> 10 rows selected.
>
>
> Oracle says it's marked to kill, and has been for 15+ minutes (won't go
> away). I'm al! ! so seeing this message in the alert log:
>
> Wed Jun 7 12:11:55 2000
>
> ORACLE Instance PETS - Can not allocate log, archival required
>
> Wed Jun 7 12:11:55 2000
>
> ARCH: Connecting to console port...
>
> Thread 1 cannot allocate new log, sequence 6
>
> All online logs needed archiving
>
> Current log# 5 seq# 5 mem# 0:
> /opt/oracle/app/oracle/oradata/PETS/log1/redoPEg
> Current log# 5 seq# 5 mem# 1:
> /opt/oracle/app/oracle/oradata/PETS/log2/redoPEg
> Fri Jun 9 10:02:58 2000
>
>
> Archiving is turned off (SVRMGR> ALTER SYSTEM ARCHIVE LOG STOP;ORA-0250:
> archiver not started .
>
> Is this sufficient information to tell if these messages are related, and
> can you recommend a course of action to 1) kill the user, and 2) add
another
> log or turn on archiving, which would aleiviate this problem?
>
> I tr! ! ied to shutdown immediate, but it hung for 20+ minutes before I
> cancelled the shutdown.
>
> Any information is appreciated.
>
> Best regards, and thanks for the help.
>
> Linda
>
> --
> Author: Linda Hagedorn
> INET: Linda_at_pets.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).
>

-- 
Author: Ruth Gramolini
INET: rgramolini_at_tax.state.vt.us

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). 






  _____  

Do You Yahoo!?
Yahoo! Photos <http://photos.yahoo.com/>  -- now, 100 FREE prints!


------_=_NextPart_001_01BFD4DC.E57E0C40
Content-Type: text/html; 
 charset=windows-1252
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">


<META content="MSHTML 5.00.2314.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#800000 face="Bookman Old Style" size=2><SPAN 
class=618545901-13062000>Re:</SPAN></FONT></DIV>
<DIV><FONT color=#800000 face="Bookman Old Style" size=2><SPAN 
class=618545901-13062000>&gt;V$PROCESS no longer holds the row for the session 
in question after the "kill session." </SPAN></FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT color=#800000 face="Bookman Old Style" size=2><SPAN 
class=618545901-13062000>Actually, it "sort of" does.&nbsp; When you kill a 
session, the row is removed from V$PROCESS but the V$SESSION row now points to 
PID 1, the PSEUDO process until the V$SESSION row is completely removed, i.e. 
once the client has ACK'd the session delete.</SPAN></FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT color=#800000 face="Bookman Old Style" size=2><SPAN 
class=618545901-13062000>Colin.</SPAN></FONT></DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE>
  <DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma 
  size=2>-----Original Message-----<BR><B>From:</B> Chuck Hamilton 
  [mailto:chuck_hamilton_at_yahoo.com]<BR><B>Sent:</B> Monday, June 12, 2000 9:52 
  AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> Re: 
  hang/wait problem<BR><BR></DIV></FONT>
  <P>I see this happen frequently. You kill a session from within oracle and it 
  doesn't go&nbsp;away. If the platform is unix, you might want to try to kill 
  the server process too. To get the PID use this query....</P>
  <P>select SPID&nbsp;from v$process p, v$session s<BR>where s.sid = 
  '&lt;SID_TO_KILL&gt;' and p.addr = s.p_addr;</P>
  <P>You need to run this quest before trying to kill the session though. 
  V$PROCESS no longer holds the row for the session in question after the "kill 
  session." The only other ways to clean up "marked for kill"&nbsp;sessions is 
  to a) boucne the&nbsp;instance, or b) wait it out.<BR>&nbsp;&nbsp; </P>
  <P><BR><B><I>Ruth Gramolini <RGRAMOLINI_at_TAX.STATE.VT.US></I></B>wrote: <BR>
  <BLOCKQUOTE 
  style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px"><BR>Try 
    looking at v$lock and get the SID of the user who is causing the<BR>trouble 
    from this and check v$session for the serial# or this user using<BR>the SID. 
    Then you can issue this sql:<BR>alter system kill session 'SID,SEREAL#';Ruth 
    B. Gramolini<BR>ORACLE &amp; DB2 DBA<BR>VT Dept. of Taxes<BR>ph# 
    802.828.5708<BR>fax# 802.828..3754<BR>rgramolini_at_tax.state.vt.us<BR>to kill 
    the user's session.<BR><BR>HTH<BR><BR>----- Original Message 
    -----<BR><BR>To: Multiple recipients of list ORACLE-L 
    <ORACLE-L_at_FATCITY.COM><BR>Sent: Friday, June 09, 2000 4:06 
    PM<BR><BR><BR>&gt; Hello,<BR>&gt;<BR>&gt; I'm having a hang/wait problem 
    with one of my systems, and this is the<BR>&gt; information I've gleaned. I 
    want to alter a table nologging:<BR>&gt;<BR>&gt; SVRMGR&gt; alter table 
    bvadmin.MR_purchased_items nologging ;<BR>&gt;<BR>&gt; alter table 
    bvadmin.MR_purchased_items nologging<BR>&gt;<BR>&gt; *<BR>&gt;! ! <BR>&gt; 
    ORA-00054: resource busy and acquire with NOWAIT 
    specified<BR>&gt;<BR>&gt;<BR>&gt; I'm trying to kill the user I believe has 
    the table:<BR>&gt; 11 rows selected.<BR>&gt;<BR>&gt; SVRMGR&gt; select * 
    from v$session ;<BR>&gt;<BR>&gt; SADDR SID SERIAL# AUDSID PADDR USER# 
    USERNAME<BR>&gt;<BR>&gt; -------- ---------- ---------- ---------- -------- 
    ----------<BR>&gt; ---------------<BR>&gt; AB1C6328 1 1 0 AB1A1A04 
    0<BR>&gt;<BR>&gt; AB1C6B54 2 1 0 AB1A1CF4 0<BR>&gt;<BR>&gt; AB1C7380 3 1 0 
    AB1A1FE4 0<BR>&gt;<BR>&gt; AB1C7BAC 4 1 0 AB1A22D4 0<BR>&gt;<BR>&gt; 
    AB1C83D8 5 1 0 AB1A25C4 0<BR>&gt;<BR>&gt; AB1C8C04 6 1 0 AB1A28B4 
    0<BR>&gt;<BR>&gt; AB1C9430 7 68 0 AB1A2BA4 0<BR>&gt;<BR>&gt; AB1CA488 9 348 
    1943952 AB1A3474 41 BVADMIN<BR>&gt; &lt;-------<BR>&gt; AB1CACB4 10 15 0 
    AB1A2E94 0<BR>&gt;<BR>&gt; AB1CC538 13 8768 0 AB1A3764 0 SYS<BR>&gt;<BR>&gt; 
    10 rows selected.<BR>&gt;<BR>&gt;<BR>&gt; Oracle says it's marked to kill, 
    and has been for 15+ minutes (won't go<BR>&gt; away). I'm al! ! so seeing 
    this message in the alert log:<BR>&gt;<BR>&gt; Wed Jun 7 12:11:55 
    2000<BR>&gt;<BR>&gt; ORACLE Instance PETS - Can not allocate log, archival 
    required<BR>&gt;<BR>&gt; Wed Jun 7 12:11:55 2000<BR>&gt;<BR>&gt; ARCH: 
    Connecting to console port...<BR>&gt;<BR>&gt; Thread 1 cannot allocate new 
    log, sequence 6<BR>&gt;<BR>&gt; All online logs needed 
    archiving<BR>&gt;<BR>&gt; Current log# 5 seq# 5 mem# 0:<BR>&gt; 
    /opt/oracle/app/oracle/oradata/PETS/log1/redoPEg<BR>&gt; Current log# 5 seq# 
    5 mem# 1:<BR>&gt; /opt/oracle/app/oracle/oradata/PETS/log2/redoPEg<BR>&gt; 
    Fri Jun 9 10:02:58 2000<BR>&gt;<BR>&gt;<BR>&gt; Archiving is turned off 
    (SVRMGR&gt; ALTER SYSTEM ARCHIVE LOG STOP;ORA-0250:<BR>&gt; archiver not 
    started .<BR>&gt;<BR>&gt; Is this sufficient information to tell if these 
    messages are related, and<BR>&gt; can you recommend a course of action to 1) 
    kill the user, and 2) add<BR>another<BR>&gt; log or turn on archiving, which 
    would aleiviate this problem?<BR>&gt;<BR>&gt; I tr! ! ied to shutdown 
    immediate, but it hung for 20+ minutes before I<BR>&gt; cancelled the 
    shutdown.<BR>&gt;<BR>&gt; Any information is appreciated.<BR>&gt;<BR>&gt; 
    Best regards, and thanks for the help.<BR>&gt;<BR>&gt; Linda<BR>&gt;<BR>&gt; 
    --<BR>&gt; Author: Linda Hagedorn<BR>&gt; INET: 
    Linda_at_pets.com<BR>&gt;<BR>&gt; Fat City Network Services -- (858) 538-5051 
    FAX: (858) 538-5051<BR>&gt; San Diego, California -- Public Internet access 
    / Mailing Lists<BR>&gt; 
    --------------------------------------------------------------------<BR>&gt; 
    To REMOVE yourself from this mailing list, send an E-Mail message<BR>&gt; 
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>&gt; 
    the message BODY, include a line containing: UNSUB ORACLE-L<BR>&gt; (or the 
    name of mailing list you want to be removed from). You may<BR>&gt; also send 
    the HELP command for other information (like subscribing).<BR>&gt;<BR><BR>-- 
    <BR>Author: Ruth Gramolini<BR>INET: rgramolini_at_tax.state.vt.us<BR><BR>Fat ! 
    ! City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, 
    California -- Public Internet access / Mailing 
    Lists<BR>--------------------------------------------------------------------<BR>To 
    REMOVE yourself from this mailing list, send an E-Mail message<BR>to: 
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the 
    message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of 
    mailing list you want to be removed from). You may<BR>also send the HELP 
Received on Mon Jun 12 2000 - 21:12:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US