Re: Problem Killing a session!

"Yanick Hudon" <yhudon_at_sml.qc.ca> wrote in message news:Nfkv8.18154$je5.182263_at_nnrp1.uunet.ca...
> Hi to all,
>
> I have a big problem and don't know how to fix it. I know that a
> solution consist to dismount the database, but i can't because it's in
> production.
>
> I wanted to delete about 48 000 records in a table, after a few moment
> with no response, i decided to press the keys CTRL-C end logout
> from my NT4 workstation.
>
> Here, we've got the real problem: The session i had is still in the
> system, i wanted to kill it and after a while the system came back with
the
> prompt.
> When we're looking in the table dba_rollback_segs, the resource used
are
> still there. We wanted to put those rollback segments associated
> with the session we wanted to kill offline, but no changes.
>
> When we ask for the status in the dba_rollback_segs table the system
> issue PENDING_OFFLINE.
>
> It's like if the session to be killed wait for the rollback segments
to
> finish and this one never finish. Now i'm turning in infinite loop
> like the system.
>
> By the way, we're using Oracle 8.0.3.0.0 on VMS.
>
> Any helps would be appreciated.
> Thanks in advance!
>
>

Oracle 8.0.3 has , of course, been desupported already in the previous millenium and it was desupported because of the numerous issues.

You need to identify the process id of the process serving your session by the query
select spid
from v$session s, v$process p
where p.addr = s.paddr
and s.sid = <affected sid> (to be identified from v$transaction) Now you can use the stop VMS dcl command to kill of the session.

> It's like if the session to be killed wait for the rollback segments
to
> finish and this one never finish. Now i'm turning in infinite loop
> like the system.

Not true. The offline pending is because your session doesn't complete, and doesn't release the rollback segment not the other way around. Actually by running this statement you made your problems bigger. Next time, please
a) don't press control-c and logout, instead press ctrl-t to see whether it is still doing anything, and I'm sure it is, you are just too impatient, or your server is too slow.
b) kill the session from a different terminal immediately.

--
Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address