RE: how to stop a killed session bothering me ?

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Tue, 07 Aug 2001 11:52:01 -0700
Message-ID: <F001.003632F3.20010807114321@fatcity.com>

Doing a shutdown abort is hardly dangerous.

Granted, it is not as clean as shutdown normal or shutdown abort, but it is not dangerous.

All it does is severe the connections. When the instance comes up, it goes through and starts rolling back looking through rbs and redo logs.

I look at shutdown abort lightly, I perfer not to do it and I avoid it, but if I need to do it, I do it without hesitation and without concern I am in danger.

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----
Sent: Tuesday, August 07, 2001 9: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: Christopher Spence
  INET: cspence_at_FuelSpot.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 - 13:52:01 CDT