El por que saber como encontrar procesos y sesiones resulta util cunado estos quedan colgados en los procesos de la base y otros usuarios desean hacer uso de objetos bloqueados por otras sesiones, Para poder liberar estos objetos de los procesos colgados es necesario matar las sesiones.
La problemática se produjo cuando al intentar borrar un usuario utilizando la sentencia :
DROP USER usuario1 CASCADE;
Y a continuación creamos el mismo usuario eliminado con la sentencia
CREATE USER usuario1
IDENTIFIED BY password
DEFAULT TABLESPACE tbs_1
ACCOUNT UNLOCK;
Nos retorna errores ORA-00604 y ORA-00054.
Aparentemente el mensaje indica que otra sesión esta ocupando ese objeto por lo cual la transacción no puede realizarse.
SOLUCIÓN
Existen varios pasos para solventar esta acción, realizar en el orden que se encuentran a continuación es lo recomendable para no afectar a la base ni realizar acciones innecesarias.
Nos conectamos como sys / as sysdba a la intancia de base de datos por toad. Ir al utilitario buscador de sesiones e identificar el usuario que esta ocupando el objeto. Damos clic derecho sobre el id sesión y poner matar sesión.
Esperamos unos minutos y Re-intentar ejecutando el script.
Si el problema persiste tenemos que realizar esta acción de forma manual ya que, aunque el toad indica que la sesión ya no esta activa existen procesos colgados en la base de datos que no permiten el desbloqueo efectivo del objeto. Para ello ejecutamos el siguiente query en un editor de SQL del Toad o directamente en SQLPlus* como sysdba.
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;
Esta sentencia nos permite identificar el id de sesión y el número de serial de los procesos ejecutados, con esos dos valores, enviando como parámetro podemos ejecutar la sentencia ALTER para matar la sesión.desde Oracle.
ALTER SYSTEM KILL SESSION '<id>, <serial#>';
Esperamos unos minutos y Re-intentar ejecutando el script.
Si el problema persiste y pude darse el caso que así sea, se debe matar el proceso de sistema operativo que esta ejecutando el bloqueo al objeto como root, para ello nos sirve el valor arrojado en la query anterior que es el SPID, con este valor podemos matar el proceso de sistema operativo utilizando el comando kill.
Nota: Dejar como ultima opción esta ya que, el matar el proceso de sistema operativo que no sea, puede afectar la integridad y consistencia de la base de datos.
[oracle@localhost ~]$ ps -ef|grep <spid>
root 11942 11928 0 11:57:57 pts/3 0:00 grep <spid>
[oracle@localhost ~]$ kill -9 <spid>
