Discussion :

[Z3phur]

bonjour,

je suis sous oracle 10gR2 et mon problème est le suivant :

j'ai des sessions que j'ai killé mais qui demeure toujours bloquante, je ne retrouve pas le spid sous mon serveur windows, comment puis je faire pour éliminer ces sessions bloquantes?

Merci d'avance pour vos réponses.

[Z3phur]

Re,

j'ai essayé de faire un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

oradebug wakeup pid

(pmon)

mais rien ne se passe,

quelqu'un connait-il la façon d'enlever les sessions phantom qui ont un verrou bloquant?

Merci d'avance

[scheu]

As-tu l'erreur suivante quand tu essaies de killer ta session ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORA-00030: User session ID does not exist

As-tu lancé un gros traitement (genre import) que tu as killé avant la fin ?
Si ta session apparaît encore dans la vue v$session mais que le process OS n'existe plus, je pense que tu n'as pas d'autre choix que d'attendre que le PMON ne supprime lui-même la session (ou de redémarrer la base, mais je ne suis pas sur que le shutdown immediate sera immédiat ), pmon "rollbacke" toutes les modifs qui ont été faites (et plus tu as attendu avant de killer, plus le retour arrière est long), j'ai déjà eu ce problème (j'avais voulu faire un shutdown immediate mais ça avait quand-même pris une plombe donc je te conseille juste d'attendre), c'est décrit dans la note Metalink suivante :

Subject: RECEIVE ORA-30 WHEN TRY TO KILL SESSION
Doc ID: Note:1011386.6 Type: PROBLEM
Last Revision Date: 29-JUN-2007 Status: PUBLISHED


Problem Description
===================

You may receive an ORA-30 if you try to kill a session with the command
ALTER SYSTEM KILL SESSION (sid,serial#);

You may notice one of these symptoms:

The serial# keeps increasing or changing.
The session still has locks on an object.


Problem Explanation
===================

Why would you want to issue that command?

1. The process no longer exists at the os level, but does show up as active in
v$session.

2. The user reboots the client machine without logging off, leaving a shadow
process.

3. That session is holding onto a lock that needs to be released.


ORA-00030, 00000, "user session ID does not exist"
// *Cause: The user session id no longer exists, probably because the
// session was logged out.
// *Action: Use a valid session ID.

Solution Description
====================

The reason you cannot kill the session is because PMON is already trying to
delete it. This is indicated by the fact that the serial number keeps
changing. When PMON attempts to cleanup a dead session, it will increase the
serial number.

The problem is that PMON may take a long time to clean up the process. If the
process was doing a very large transaction at the time it aborted, then PMON
has to rollback the large transaction.

When PMON finds a dead process, it will try to clean it up. If it makes any
progress, i.e. if it manages to free at least some of the process's resource,
it will repeatedly keep trying to delete the process. When it finally gets to
the point where it can't free up any of the process's resource(i.e. there are
no more free buffers), it will print a message to the trace file and try to
delete that process once a second. The problem is not that PMON is not trying
hard enough to delete the process. The problem is the lack of resources needed
to remove the process. If there are not enough buffers, then the removal of
the process is delayed. This is a free buffer problem in the data cache.

PMON can take anywhere from 5 minutes to over 24 hours to clean up a job. The
impact is that often times the process being cleaned up is holding locks that
prevents others from performing certain operations.

Presently the only workaround is to wait for PMON to clean up the job or to
shutdown and startup the db.


A list of bugs filed on this:

Bug 258727 PMON TRYING TO CLEAN UP PROCESS KILLED AT O/S INCREMENTING SERIAL
NUMBER

Since the session had been running a PL/SQL program, the session may have
been rolling back transactions which caused it to take so long to disappear
from the monitor session.


Bug 192228 DEAD PROCESS IS NOT RELEASING ITS LOCKS

A process has died for some unknown reason (the client could have switched his
computer off). Then the locks just hung around forever. The shadow process is
no longer there. There is a session ID and a serial# in lock monitor (v$lock),
but no corresponding entry in monitor session (v$session). Tried to alter
system kill session and received an error saying that the session did not
exist. The only workaround is to do a shutdown abort to release the locks.


Bug 232809/186423 PMON FAILED TO DELETE A PROCESS, ORA-30 WHEN KILLING THE
SESSION/GETS ORA 30 WHEN TRYING TO KILL A SESSION WHOSE SESSIONID KEEPS
CHANGING

When PMON attempts to cleanup a dead session, it will bump the serial number.
You cannot kill the session because PMON is already trying to delete it.
If, however, the database is doing really performing poorly or nothing is
being cleaned up, try a shutdown abort.


Bug 200455/186423 GETTING ORA-30 WHEN TRYING TO KILL SESSION THAT SHOWS UP IN
V$SESSION

V$process and v$session indicates that the session id, os#, user, and table
being locked are staying the same but the serial# is changing. PMON trace
files indicate that it is having difficulties killing the process. The os
process was killed. The fact that the serial number keeps changing indicates
that the session is indeed being reused. The 'alter system kill session'
command takes a session id as well as a session serial #; if the serial #
does not match the current serial #, an ORA-30 is the result. Serial #'s
change when the session logs off and someone logs in and uses the same session

[scheu]

Ayant de nouveau eu le même problème récemment, si tu veux avoir une estimation de quand tout sera rollbacké, tu peux regarder la vue v$transaction. En actualisant plusieurs fois, la colonne used_ublk doit diminuer pour la session qui est en train d'être rollbackée, et quand cette colonne arrive à zéro (ça peut être très long) c'est qu'en théorie c'est fini