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_at_tax.state.vt.us
to kill the user's session.

HTH

• Original Message -----

From: Linda Hagedorn <Linda_at_pets.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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_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
Received on Mon Jun 12 2000 - 08:17:17 CDT