Re: Killing an user from the user

feei Shun,
Some sessions dont go off so easily.
They will be marked as killed and will be cleared on later by PMON or SMON.
If the killed session has done a big transaction, you can see the rollback of the transaction activity by using this query. (How much is being rolled back etc).

select ktuxeusn,ktuxeslt,ktuxesqn,ktuxesiz from x$ktuxe
where ktuxecfl='DEAD'
/

select pid,state,undoblocksdone
from v$fast_start_servers
/

select usn,state,undoblockstotal,undoblocksdone,cputime from v$fast_start_transactions
/

set feedback on

Sometimes the session may be doing a large sort and hogging up a lot of CPU.
Then i find it better to kill the server background process relating to that session.

After killing the session at Oracle level using alter session, you can use this query to find and kill the background server process associated with the dead session.

col cprogram form a30 trunc head "Client|Program" col sprogram form a30 trunc head "Server|Program" col sid form 9999
col pid form 9999
col process head "Client|Process|ID" form a10 col spid head "Oracle|Background|ProcessID" form 99999 select a.sid,b.pid,a.program cprogram,a.process,b.program sprogram,b.spid
from v$session a,v$process b
where a.paddr(+)=b.addr
and b.addr not in(
select paddr
from v$bgprocess)
and b.addr not in(
select paddr
from v$dispatcher)
and b.addr not in(
select paddr
from v$shared_server)
and a.sid is null
and b.program <> 'PSEUDO'
/

regards
Srivenu Received on Fri Mar 05 2004 - 05:15:28 CST