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