Re: How to kill? (Full rollback segment)

From: R.Schierbeek <bytelife_at_worldonline.nl>
Date: 1997/08/02
Message-ID: <01bc9f1b$83b5e2c0$8982f1c3@worldonline>#1/1

Hello Hjal,
An alter system kill session '&UserID' kills the session and does a rollback. This may take minutes for a large transaction !

Njål A. Ekern <n.a.ekern_at_usit.uio.no> schreef in artikel <33E1A062.6FF9_at_usit.uio.no>...

> I suppose I can do a 'shutdown immediate' to solve my problem, but I
> hope there is another way around...
>
> A user has a transaction that's stuck because of a full
> rollback-segment.
> ->1: Are there anything like a 'Kill transaction'-statement?

rem KILL a user
rem Shows 1 or all users (return=all) and kills a user defined by SID,serial#.
col C1 for A10 head UserID
set verify off
prompt Enter a username (return=all)
select SID,serial#,SID||','||serial# C1,username,status from v$session
where username like upper('&username%') /
prompt Now kill the user: (return = kill no users) alter system kill session '&UserID'
/
select SID,serial#,username,status
from v$session
where username = upper('&username')
/
undefine username

> I tried to kill the users session, but the kill statement waits for the
> transaction to finish. (ORA-00031: session marked for kill)
> ->2: Are there anything like a 'Kill session immediate'-statement, that
> will roll back the transaction?
>
> I selected the Process attribute from the v$session view, my plan was to
> kill the users unix session.
> This statement returned 9022:01, but using ps -ef in Unix I couldn't
> find such a process.
> Are there any way to kill the user from the Unix-prompt?

Yes, you can kill the user from the Unix-prompt using this to get the ID:

select  S.SID
       ,S.username
       ,S.OSuser
       ,initcap(S.status) Status
       ,S.terminal
       ,initcap(A.Name) Command
       ,S.process
       ,P.SPID "UnixID"                 -- <<<<
from    v$SESSION S
       ,v$PROCESS P
       ,sys.AUDIT_ACTIONS A
where S.username is not null

and A.action = S.command
and P.ADDR = S.PADDR

UNLESS you use the MTServer. Then you get the MTS unix ID.

>
> Are there any way to tell the transaction to continue, but using
> rollback segment R03 for the rest of the transaction?

No way, sorry.

Good luck !

---

Roelof Schierbeek, DBA
The Hague, Holland
email: bytelife@worldonline.nl http://www.worldonline.nl/~bytelife Received on Sat Aug 02 1997 - 00:00:00 CDT