RE: how to stop a killed session bothering me ?

From: HAWKINS, JAMES W [IT/1000] <james.w.hawkins_at_pharmacia.com>
Date: Tue, 07 Aug 2001 06:50:38 -0700
Message-ID: <F001.00362A45.20010807065051@fatcity.com>

If you did a "shutdown abort", wouldn't you still have to wait for the rollback to happen during instance recovery on the startup? I've never tried this - I usually just suffer through the rollback.

Jim

---

Jim Hawkins
Oracle Database Administrator
Data Management Center of Expertise

Pharmacia Corporation
800 North Lindbergh Blvd.
St. Louis, Missouri 63167
(314) 694-4417

james.w.hawkins_at_pharmacia.com

-----Original Message-----
Sent: Tuesday, August 07, 2001 8:21 AM
To: Multiple recipients of list ORACLE-L

You should wait for PMON ending its job of rollback the transaction of that killed session. Since one of the releases of 8i that duty is a new one for the SMON. You have few options. If you are in a hurry, you could do shutdown abort. First issue an alter system checkpoint; just in case a maybe possible recover problem later. You see that it is a little risky. With a shutdown immediate, you might wait a lot. Even that you could afford it because of the dangerous of the abort option. The other is to issue through svrmgrl or sqlplus depending on the release an

oradebug wakeup <orapid of PMON or SMON depending on the release>

However there is a little chance that with this you could hurry the PMON for the rollback of the transaction.

You can see how much work the PMON or SMON have to do through v$transaction:

select b.sid,a.used_ublk from v$transaction a,v$session b
where addr=taddr and b.status='KILLED';

Regards.

• Andrey Bronfin <andreyb_at_elrontelesoft.com> wrote:
  > Dear all !
  > I have killed a session while it was populating a
  > big table.
  > Now the session is marked KILLED in v$session.
  > It's serial# is keeping being incremented , so i
  > assume that a rollback (of
  > rows inserted into that big table by that session)
  > is going on there in the
  > background.
  > This rollback runs for several hours already
  > preventing me from DMLing that
  > big table.
  > Is there a way to stop that rollback / drop /
  > truncate that table ?
  > Thanks a lot in advance .
  > Andrey.
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Andrey Bronfin
  > INET: andreyb_at_elrontelesoft.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).
  

  ---

Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062

---

Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christian Trassens
  INET: ctrassens_at_yahoo.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: HAWKINS, JAMES W [IT/1000]
  INET: james.w.hawkins_at_pharmacia.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).

Received on Tue Aug 07 2001 - 08:50:38 CDT