Ver conexiones Activas a la BD
select sid, serial#, username, status from v$session;
select count (*)from v$session;
Matar la sesiones de un usuario en oracle
En muchas ocasiones tenemos la necesidad de matar una o varias sesiones de un usuario en una base de datos Oracle 11g, las sesiones de usuario las tenemos
que matar desde Oracle y no desde el Sistema Operativo, aunque podemos realizarlo desde el Sistema Operativo si no lograrmos eliminarlas desde Oracle.
Lo primero que vamos a hacer es identificar la sesión de usuario que tenemos que matar, para ello utilizaremos la siguiente Query:
SQL> SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
La salida será algo parecido ha esto:
USERNAME SID SERIAL# SPID
----------- ---- ------- ------
TUUSUARIO_1 108 3917 56061
TUUSUARIO_2 158 58 51062
TUUSUARIO_3 41 4647 14649
Matar sesiones a nivel de Sistema Operativo
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.username ='NOM_ESQUEMA';
El select anterior nos devuelve el nombre de usuario, el número de proceso Oracle, el número serial del proceso Oracle y el PID del proceso Oracle en el Sistema Operativo.
Una vez tengamos identificada la sesión de usuario que queremos matar utilizamos la siguiente instrucción desdeSqlPlus.
SQL> ALTER SYSTEM KILL SESSION 'SID, SERIAL#';
Ejemplo:
SQL> ALTER SYSTEM KILL SESSION '108, 3917';
Es posible que la sesión no muera de forma inmediata ya que puede que se este realizando alguna transacción, esto dependera de lo que estubierá realizando el usuario.
A veces puede ocurrir que la sesión nunca muera por que se haya quedado zombi, en ese caso tendremos que matarla desde el promt del Sistema Operativo, para ello utilizaremos el PID que tenemos en la columna SPID.
Ejemplo para Unix/Linux
kill -9 56061
Ver la Conexiones a la BD en oracle
SQL>select osuser, username, machine, program from v$session order by osuser;
Consultar y Generar kill Sessions Bloquantes
SQL> select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;' from gv$session s where username = 'NOM_SESSION'
SQL> Select /*+ rule */
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status,
'alter system kill session '''||a.sid||','||a.serial#||''' immediate;'
from
v$session a,
v$sort_usage b
where
a.saddr = b.session_addr
and b.blocks > 128
order by
b.tablespace, b.segfile#, b.segblk#,
b.blocks;
SQL de mayor tiempo de ejecucion
SQL>SELECT SQL_TEXT, EXECUTIONS FROM V$SQLSTATS WHERE CPU_TIME> 200.000;
SQL>SELECT * FROM V$SQLSTATS WHERE CPU_TIME> 200.000
Conexiones de una maquina
SELECT * FROM V$SESSION WHERE MACHINE='nom_maquina' AND LOGON_TIME > SYSDATE-1;
SELECT * FROM V$LOCK WHERE BLOCK > 0;
SELECT SID,SEQ#,EVENT,WAIT_TIME,STATE FROM V$SESSION_WAIT;
Objetos Invalidos
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects
where OWNER not in ('SYS','SYSTEM') and status = 'INVALID'
order by OWNER,OBJECT_TYPE,OBJECT_NAME;
Matar sessiones inalidas
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status = 'INVALID' order by owner, object_name;
Ejemplos:
Ver nombre equipo que se conecta con el usuario de nombre NOM_USER
select MACHINE from gv$session s where username = 'NOM_USER';
ver sesiones del usuario NOM_USER en estado INACTIVO
select * from v$session s where username = 'NOM_USER' AND STATUS='INACTIVE';
select USERNAME from v$session s where STATUS='INACTIVE';
Script matar sessiónes del usuario NOM_USER en estado INACTIVO
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;' from v$session s where username = 'NOM_USER' AND STATUS='INACTIVE';
ver sesiones estado INACTIVO mayores a 500 segundos
select * from gv$session where STATUS='INACTIVE' AND SECONDS_IN_WAIT > 500;
select USERNAME, MACHINE,SECONDS_IN_WAIT, STATUS from v$session where STATUS='INACTIVE' AND SECONDS_IN_WAIT > 1200;
Script matar sesiones de estado INACTIVE mayores a 12000 segundos
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;' from v$session s where STATUS='INACTIVE' AND SECONDS_IN_WAIT > 12000;
Consultar Todos los Usuarios y los que han expirado
SELECT * FROM dba_users;
SELECT EXPIRY_DATE, USERNAME, ACCOUNT_STATUS FROM DBA_USERS;
SELECT EXPIRY_DATE, USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE ACCOUNT_STATUS LIKE'%EXPIRED%';
Cambiar el Passwrd de un Usuario
ALTER USER system IDENTIFIED BY "******";
ALTER USER system ACCOUNT UNLOCK;
Nota si por perfile no permite reutilizar el password entonces hay que cambiarlo de perfil
ALTER USER nom_users PROFILE DEFAULT;
Obtener sessiones INACTIVAS y su respectivo SQL
select * from v$session s;
select * from v$process p;
select * from v$sqlarea a;
select s.SID, s.SERIAL#, SPID,s.USERNAME,s.STATUS, s.MACHINE, S.PROGRAM, a.sql_text
from v$session s, v$process p, v$sqlarea a
where (s.paddr=p.addr and s.sql_id=a.sql_id and s.STATUS='INACTIVE');
En el tiempo los SID cambian ya que los usuarios se conectan persistente mente así
que lo mejor es tomar este scrip:
select s.SID, s.SERIAL#, SPID,s.USERNAME,s.STATUS, s.MACHINE, s.PROGRAM, a.sql_text
from v$session s, v$process p, v$sqlarea a
where (s.paddr=p.addr and s.PREV_SQL_ID=a.sql_id and s.STATUS='INACTIVE');
Identificar sessiones Bloqueantes
SELECT S.SID, S.SERIAL#, P.SPID FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.SID IN (SELECT SESSION_ID FROM V$LOCKED_OBJECT);
Identificar sessiones Bloqueantes y crear script para matarlas
SELECT 'ALTER SYSTEM kill SESSION '''||S.SID||','||S.SERIAL#||''' IMMEDIATE;' FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.SID IN (SELECT SESSION_ID FROM V$LOCKED_OBJECT);
Rastrear sessiones Bloqueantes y crear script para matarlas
SELECT S.USERNAME,S.MACHINE,S.PROGRAM, S.SID, S.SERIAL#, P.SPID,Q.SQL_TEXT FROM V$SESSION S, V$PROCESS P, V$SQL Q WHERE S.PADDR = P.ADDR AND S.SID IN (SELECT SESSION_ID FROM V$LOCKED_OBJECT) AND S.SQL_ID=Q.SQL_ID;
SELECT * FROM V$SESSION;
SELECT * FROM V$PROCESS;
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM V$SQL;
Sessiones bloqueantes
SELECT
decode(L.TYPE,'TM','TABLE','TX','Record(s)') TYPE_LOCK,
decode(L.REQUEST,0,'NO','YES') WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
Q.SQL_TEXT,
concat(' ',s.PROGRAM) PROGRAM,
O.OWNER OWNER,
'ALTER SYSTEM kill SESSION '''||S.SID||','||S.SERIAL#||''' IMMEDIATE;',
S.SID,
S.SERIAL#
FROM v$lock l,dba_objects O,v$session S, V$SQL Q
WHERE
l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND l.TYPE in ('TM','TX')
AND s.SQL_ID=Q.SQL_ID;
INTENTOS FALLIDOS DE CONEXION
Identificar Perfil del usuario:
SQL>select profile from dba_users where username='USER';
PROFILE
------------------------------
DEFAULT
Listar los limites y recursos del profile Obtenido DEFAULT
SQL> select resource_name, limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10----------->> Modificar a UNLIMITED
PASSWORD_LIFE_TIME 180
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 filas seleccionadas.
SQL>
Modificar el parámetro failed_login_attempts
SQL> alter profile DEFAULT limit failed_login_attempts unlimited;
Perfil modificado.
SQL> select resource_name, limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED--------> Mdificado
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME 180
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 filas seleccionadas.
SQL>