Message-Id: <10526.108639@fatcity.com> From: "Ruth Gramolini" Date: Mon, 12 Jun 2000 09:17:17 -0400 Subject: Re: hang/wait problem 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@tax.state.vt.us to kill the user's session. HTH ----- Original Message ----- From: Linda Hagedorn To: Multiple recipients of list ORACLE-L Sent: Friday, June 09, 2000 4:06 PM Subject: hang/wait problem > 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@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@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