| 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
--0-1189641421-960824658=:638
Content-Type: text/plain; charset=us-ascii
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 also 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 tried 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 -- now, 100 FREE prints! --0-1189641421-960824658=:638 Content-Type: text/html; charset=us-ascii <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 HELP command for other information (like subscribing). <BR></BLOCKQUOTE><BR><p><br><hr size=1><b>Do You Yahoo!?</b><br>Received on Mon Jun 12 2000 - 10:44:18 CDT
 |
 |