Suppression d'une session Oracle bloquée

Pour supprimer une session Oracle bloquée, on se connecte à la base concernée avec le compte SYSTEM (ou SYS).
On obtient la liste des sessions ouverte en exécutant la requête suivante :
SET linesize 140
COL username FORMAT A20
COL osuser FORMAT A20
COL status FORMAT A10
COL program FORMAT A40
COL connexion FORMAT A24
COL identifiant FORMAT A20
SELECT username, osuser, status, program, TO_CHAR(logon_time, 'DD/MM/YYYY HH24:MI') "connexion", sid | | ',' || serial# "identifiant" FROM v$session ;
Exemple :
SQL> SET linesize 140
SQL> COL username FORMAT A20
SQL> COL osuser FORMAT A20
SQL> COL status FORMAT A10
SQL> COL program FORMAT A40
SQL> COL connexion FORMAT A24
SQL> COL identifiant FORMAT A20
SQL> SELECT username, osuser, status, program, TO_CHAR(logon_time, 'DD/MM/YYYY HH24:MI') "connexion"
, sid | | ',' || serial# "identifiant" FROM v$session ;

USERNAME             OSUSER               STATUS     PROGRAM                                  connexion                identifiant
-------------------- -------------------- ---------- ---------------------------------------- ------
                     oracle               ACTIVE     oracle@rdpciteurms086 (PMON)             05/10/2008 13:05         1,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (DBW0)             05/10/2008 13:05         2,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (LGWR)             05/10/2008 13:05         3,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (CKPT)             05/10/2008 13:05         4,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (SMON)             05/10/2008 13:05         5,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (RECO)             05/10/2008 13:05         6,1
                                          ACTIVE                                              16/09/2009 12:37         11,18285
DBSNMP               oracle               INACTIVE   dbsnmp@rdpciteurms086 (TNS V1-V3)        16/09/2009 06:00         12
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:37         14,30146
                                          ACTIVE                                              16/09/2009 12:37         15,23625
ECTORSIEGE           NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:37         17,1545

USERNAME             OSUSER               STATUS     PROGRAM                                  connexion                identifiant
-------------------- -------------------- ---------- ---------------------------------------- ------
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:37         21,28158
SYSTEM               oracle               INACTIVE   dbsnmp@rdpciteurms086 (TNS V1-V3)        16/09/2009 06:00         22
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:36         26,39590
DOTATION             roureb               INACTIVE   dotation.exe                             16/09/2009 09:23         43,24365
ETMS2001             malabrec             INACTIVE   toad.exe                                 16/09/2009 08:54         45,19751
ETMS2001             FCNRDP-ECTORADMIN    INACTIVE   w3wp.exe                                 16/09/2009 12:38         66,63361
ETMS2001             champione            ACTIVE     sqlplusw.exe                             16/09/2009 12:38         67,60526
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:38         70,19109
ECTORSIEGE           NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:37         72,40033
SECTOADMIN           MOTTEC               INACTIVE   adv.exe                                  16/09/2009 10:12         84,1809
ETMS2001             laured04             INACTIVE   toad.exe                                 16/09/2009 10:31         85,63849

USERNAME             OSUSER               STATUS     PROGRAM                                  connexion                identifiant
-------------------- -------------------- ---------- ---------------------------------------- ------
FOCUS                champione            INACTIVE   efi.exe                                  16/09/2009 10:08         87,50140
                                          ACTIVE                                              16/09/2009 12:37         90,37138
SYS                  oracle               ACTIVE     sqlplus@rdpciteurms086 (TNS V1-V3)       16/09/2009 08:34         91,2
                                          ACTIVE                                              16/09/2009 12:37         94,20275
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:33         101,2830
ECTORSIEGE           NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:38         109,19034
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:35         119,14301
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:27         130,4109
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:34         131,27293
GESPRO               laured04             INACTIVE   ectorpb.exe                              16/09/2009 09:42         137,27453

32 ligne(s) sélectionnée(s).

SQL> 
Une fois que la session à supprimer est identifiée, on relève la valeur du champ "identifiant" pour cette session et on la supprime avec la commande suivante :
ALTER SYSTEM KILL SESSION 'identifiant' ;
Exemple :
SQL> ALTER SYSTEM KILL SESSION '87,50140' ;

Système modifié.

SQL> SELECT username,osuser,status,program,to_char(LOGON_TIME,'DD/MM/YYYY HH24:MI') "CONNEXION", SID
||','||SERIAL# "IDENTIFIANT" from v$session;

USERNAME             OSUSER               STATUS     PROGRAM                                  CONNEXION                IDENTIFIANT
-------------------- -------------------- ---------- ---------------------------------------- ------
                     oracle               ACTIVE     oracle@rdpciteurms086 (PMON)             05/10/2008 13:05         1,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (DBW0)             05/10/2008 13:05         2,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (LGWR)             05/10/2008 13:05         3,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (CKPT)             05/10/2008 13:05         4,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (SMON)             05/10/2008 13:05         5,1
                     oracle               ACTIVE     oracle@rdpciteurms086 (RECO)             05/10/2008 13:05         6,1
                                          ACTIVE                                              16/09/2009 12:43         11,18309
DBSNMP               oracle               INACTIVE   dbsnmp@rdpciteurms086 (TNS V1-V3)        16/09/2009 06:00         12
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:37         14,30146
                                          ACTIVE                                              16/09/2009 12:43         15,23649
SYSTEM               oracle               INACTIVE   dbsnmp@rdpciteurms086 (TNS V1-V3)        16/09/2009 06:00         22

USERNAME             OSUSER               STATUS     PROGRAM                                  CONNEXION                IDENTIFIANT
-------------------- -------------------- ---------- ---------------------------------------- ------
ETMS2001             FCNRDP-ECTORADMIN    INACTIVE   w3wp.exe                                 16/09/2009 12:44         26,39597
DOTATION             roureb               INACTIVE   dotation.exe                             16/09/2009 09:23         43,24365
ETMS2001             malabrec             INACTIVE   toad.exe                                 16/09/2009 08:54         45,19751
ETMS2001             champione            ACTIVE     sqlplusw.exe                             16/09/2009 12:38         67,60526
ECTORSIEGE           NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:39         70,19125
SECTOADMIN           MOTTEC               INACTIVE   adv.exe                                  16/09/2009 10:12         84,1809
ETMS2001             laured04             INACTIVE   toad.exe                                 16/09/2009 10:31         85,63849
FOCUS                champione            KILLED     efi.exe                                  16/09/2009 10:08         87,50140
                                          ACTIVE                                              16/09/2009 12:43         90,37162
SYS                  oracle               ACTIVE     sqlplus@rdpciteurms086 (TNS V1-V3)       16/09/2009 08:34         91,2
                                          ACTIVE                                              16/09/2009 12:43         94,20299

USERNAME             OSUSER               STATUS     PROGRAM                                  CONNEXION                IDENTIFIANT
-------------------- -------------------- ---------- ---------------------------------------- ------
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:33         101,2830
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:38         106,42948
ECEDC                NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:40         109,19062
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:44         119,14303
ETMS2001             NETWORK?SERVICE      INACTIVE   w3wp.exe                                 16/09/2009 12:27         130,4109
GESPRO               laured04             INACTIVE   ectorpb.exe                              16/09/2009 09:42         137,27453

28 ligne(s) sélectionnée(s).

SQL>