Query per eseguire il KILL di una sessione per SID

.

Nel caso in cui abbiamo la necessità di eseguire il kill di tutte le sessioni con un determinato SID, con la seguente query dinamica,
costruiremo il comando da lanciare per eseguire il kill di tutte le sessioni su tutte le istanze del RAC da una sola istanza.
Fare massima attenzione della provenienza della sessione che dobbiamo uccidere, in un RAC il SID può essere uguale su tutte e due le istanze.
Il SERIAL con il SID identificano con precisione la sessione a noi interessata.

Kill session per SID:

select 'alter system disconnect session '''||SID||','||SERIAL#||',@'||INST_ID||''' immediate;'   from gv$session where SID=&sid;

Query per eseguire il KILL di tutte le sessioni di un UTENTE su tutte le istanze del RAC

.

Nel caso in cui abbiamo la necessità di eseguire il kill di tutte le sessioni Oracle di un determinato utente, con la seguente query dinamica,
costruiremo il comando da lanciare per eseguire il kill di tutte le sessioni su tutte le istanze del RAC da una sola istanza.

Kill sessions di un determinato utente:

select  'alter system kill session ''' || sid || ',' || serial# || ','||'@'|| INST_ID || ''' immediate;' 
from gv$session where USERNAME = '&USERNAME';

Query per identificare quale sessione sta determinando il LOCK

.

Con la query Blocking LOCK, riusciamo ad individuare la sessione che sta bloccando altre sessioni. Queste situazioni di LOCK, in alcuni casi potrebbe determinare grossi colli di bottiglia e quindi forti rallentamenti se non il blocco del database. Con la seguente query troverete quale sessione sta determinando i forti rallentamenti o il blocco del database in breve tempo.

SELECT bs.sid BSID, count(ws.sid) as NumWait 
FROM v$lock hk, v$session bs, v$lock wk, v$session ws 
WHERE hk.BLOCK = 1 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 
AND wk.id2(+) = hk.id2 AND hk.sid = bs.sid(+) 
AND wk.sid = ws.sid(+) AND bs.sid != ws.sid group by bs.sid 
order by 2 desc;

Query per individuare la sessione che sta bloccando altre sessioni

.

Con la query Blocking LOCK, riusciamo ad individuare la sessione che sta bloccando altre sessioni. Queste situazioni di LOCK, in alcuni casi potrebbe determinare grossi colli di bottiglia e quindi forti rallentamenti se non il blocco del database. Con la seguente query troverete quale sessione sta determinando i rallentamenti o il blocco del database in breve tempo.

select a.inst_id as WInst ,a.sid as WSid, a.serial# as WSer, a.sql_id as WSql , a.seconds_in_wait as WSec,
 b.sid as BSid , b.serial# as BSer , a.event as WEvent , b.event as BEvent, b.sql_id as BSql
from gv$session a , gv$session b
where a.BLOCKING_SESSION_STATUS='VALID' and b.inst_id=a.BLOCKING_INSTANCE and b.sid=a.BLOCKING_SESSION;

Query per vedere i dettagli di una sessione Oracle di un utente

.

Nel caso abbiamo la necessità di controllare le sessioni di utente Oracle,
con la seguente query,
ricaviamo molte informazioni richieste.

Le prime righe riguardano la formattazione delle tabelle,
per avere una visuale accettabile.

I campi che andremo a selezionare sono:

LOGON TIME: indica l’orariov in cui è iniziata la sessione
STATUS – Lo stato della sessione se ACTIVE o INACTIVE.
IST_ID – Nel caso di un RAC (Real Application Cluster), il numero dell’istanza dove è presente la sessione.
SQL_ID – Il codice identificativo della query.
PROCESS – Il processo del sistema operativo.
PROGRAM – Il client che state utilizzando (Toad, SQL Developer)
SCHEMANAME – L’utente con è partita la sessione Oracle.
SID – Il codice identificativo della sessione Oracle.
SERIAL# – Il codice seriale univoco di una sessione Oracle.
SPID – Processo del sistema operativo legato alla sessione Oracle.
OSUSER – L’utente del sistema operativo con cui è partita la sessione Oracle.
MACHINE – Il nome del client da dove parte la sessione Oracle.
TERMINAL – Il nome del sistema operativo da dove parte la sessione Oracle.
LAST_CALL_ET – L’orario in cui la sessione ha cambiato stato da ACTIVE a INACTIVE e vice versa.
USERNAME – Nome dell’utente Oracle.
COMMAND – Il comando che è stato eseguito sul database Oracle.

Per estrapolare queste informazioni dal database Oracle, abbiamo messo in relazione tre tabelle:

GV$SESSION
GV$PROCESS
DBA_USERS

Inoltre nella WHERE condition,
per prima cosa abbiamo messo in relazione le viste
V$PROCESS e
V$SESSION attraverso la JOIN
P.ADDR=S.PADDR
e messo in relazione la vista V$SESSION
con la vista DBA_USERS attraverso la JOIN S.USER#=U.USER_ID.


Infine dopo la relazione delle tabelle V$PROCESS V$SESSION e DBA_USERS, per controllare le informazioni che abbiamo selezionato di un determinato utente,
gli passiamo la condizione
S.USER#=il nome dell’utente.

Sessioni per utente:

Set lines 300
Set pages 60
Col LAST_CALL for a18
Col LOGON_TIME for a18
Col schemaname for a16
Col command for a24
Col machine for a16
Col action for a12
Col username for a16
Col osuser for a16
Col terminal for a16
select to_char(S.logon_time,'DD-MON-YYYY hh24.mi.ss') LOGON_TIME, s.status,s.inst_id,s.sql_id, s.process,s.program, s.schemaname,
s.sid, s.serial#, p.spid, s.osuser, S.machine, S.terminal,to_char(sysdate -(LAST_CALL_ET/86400),'DD-MON-YY hh24:mi:ss') LAST_CALL , 
U.username, S.ACTION, decode(S.command,0, 'No command in progress.', 1, 'CREATE TABLE',2, 'INSERT', 3, 'SELECT', 4, 'CREATE CLUSTER', 5, 'ALTER CLUSTER', 6, 'UPDATE', 7, 'DELETE') COMMAND
 from gv$session S, dba_users U, gv$process P
where P.ADDR = S.PADDR and 
      S.user# = U.user_id and 
      s.schemaname='&user'
order by LOGON_TIME;



  

 

Query per vedere il numero utilizzato, numero massimo e percentuale di utilizzo di sessioni, processi, transazioni e cursori Oracle

.

Questa procedura è a tutti gli effetti un programma PL/SQL con la funzione di estrapolare il numero di processi, il numero di sessioni, il numero di transazioni e il numero di cursori aperti.

Inoltre è possibile vedere la soglia massima e la percentuale di utilizzo.

L’output della procedura Sessioni processi cursori e transazioni

TAG   PARAMETER                                                        CURRENT MAXIMUM USAGE%
----- ---------------------------------------------------------------- ------- ------- ------
PSTAT open_cursors                                                          33     300     11
PSTAT processes                                                             52     300     17
PSTAT sessions                                                              33     472      6
PSTAT transactions                                                           0     519      0

Numero di sessioni processi cursori e transazioni:

set verify off
set termout off
set feedback off
set echo off
set serveroutput on size 999999
set linesize 132
set termout on
declare
MISSING_PARAMETER exception;
MISSING_STAT_OPENCURS exception;
--
MaxIdxParameter integer;
type parameter_name is record (
name varchar2(64),
value integer);
type parameter_list_array is table of parameter_name index by binary_integer;
parameter_lst parameter_list_array;
type parameter_type is record (
current_value integer);
type parameter_array is table of parameter_type index by binary_integer;
parameter_stat parameter_array;
--
function LoadParameterList return number is
cursor parameter_list is
select 1 parameter_id, name, to_number(value) value from v$parameter where name = 'open_cursors'
union all
select 2, name, to_number(value) from v$parameter where name = 'processes'
union all
select 3, name, to_number(value) from v$parameter where name = 'sessions'
union all
select 4, name, to_number(value) from v$parameter where name = 'transactions';
begin
MaxIdxParameter := 1;
for rec_parameter_list in parameter_list loop
if rec_parameter_list.parameter_id = MaxIdxParameter then
parameter_lst(MaxIdxParameter).name := rec_parameter_list.name;
parameter_lst(MaxIdxParameter).value := rec_parameter_list.value;
MaxIdxParameter := MaxIdxParameter + 1;
else
return MaxIdxParameter;
end if;
end loop;
MaxIdxParameter := MaxIdxParameter - 1;
return 0;
end LoadParameterList;
--
procedure LoadParameterStat is
stat_id number;
cursor parameter_list (p_stat in number) is
select 1 parameter_id, max(count(*)) current_value from v$open_cursor group by sid
union all
select 2, count(*) current_value from v$process
union all
select 3, count(*) current_value from v$session
union all
select 4, count(*) current_value from v$transaction;
begin
begin select statistic# into stat_id from v$statname where name = 'opened cursors current';
exception when no_data_found then raise MISSING_STAT_OPENCURS;
end;
for rec_parameter_list in parameter_list(stat_id) loop
parameter_stat(rec_parameter_list.parameter_id).current_value := rec_parameter_list.current_value;
end loop;
end LoadParameterStat;
--
procedure OutputStats is
ParameterId integer;
PctUsage number;
begin
dbms_output.put(rpad('TAG',5));
dbms_output.put(' '||rpad('PARAMETER',64));
dbms_output.put(' '||rpad('CURRENT',7));
dbms_output.put(' '||rpad('MAXIMUM',7));
dbms_output.put(' '||rpad('USAGE%',6));
dbms_output.new_line;
dbms_output.put(rpad('-',5,'-'));
dbms_output.put(' '||rpad('-',64,'-'));
dbms_output.put(' '||rpad('-',7,'-'));
dbms_output.put(' '||rpad('-',7,'-'));
dbms_output.put(' '||rpad('-',6,'-'));
dbms_output.new_line;
for ParameterId in 1..MaxIdxParameter loop
dbms_output.put(rpad('PSTAT',5));
dbms_output.put(' '||rpad(parameter_lst(ParameterId).name,64));
PctUsage := trunc(100*parameter_stat(ParameterId).current_value/parameter_lst(ParameterId).value);
dbms_output.put(' '||lpad(parameter_stat(ParameterId).current_value,7));
dbms_output.put(' '||lpad(parameter_lst(ParameterId).value,7));
dbms_output.put(' '||lpad(PctUsage,6));
dbms_output.new_line;
end loop;
end OutputStats;
begin
-----------------------------------------------------
-- catalogazione parametri sistema
-----------------------------------------------------
if LoadParameterList != 0 then raise MISSING_PARAMETER;
end if;
LoadParameterStat;
-----------------------------------------------------
-- output statistiche
-----------------------------------------------------
OutputStats;
exception
when MISSING_PARAMETER then raise_application_error(-20000,'missing parameter id='||MaxIdxParameter);
when MISSING_STAT_OPENCURS then raise_application_error(-20001,'missing statistic: "opened cursors current"');
end;
/

Query per vedere le sessioni detentrici di LOCK

.

Con la query Sessioni detentrici di lock, riusciamo ad individuare la sessione che sta bloccando altre sessioni. Queste situazioni di lock, in alcuni casi potrebbe determinare grossi colli di bottiglia e quindi il blocco del database. Con la seguente query troverete quale sessione sta determinando il blocco del database in breve tempo.

select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking 
from v$lock l1, v$lock l2 
where l1.block =1 and 
      l2.request > 0 and 
      l1.id1=l2.id1 and 
      l1.id2=l2.id2;

Query per vedere le sessioni Oracle presenti da una determinata data

.

Come vedere le sessioni Oracle antecedenti una determinata data o ora:

Con l’articolo Query per vedere le sessioni Oracle presenti da una determinata data, possiamo controllare le sessioni Oracle antecedenti una determinata data o ora, utilizzando la seguente query.
Le prime righe riguardano la formattazione delle tabelle, per avere una visuale accettabile.
I campi che andremo a selezionare sono:

LOGON_TIME – Indica l’orario in cui è iniziata la sessione.
STATUS – Lo stato della sessione se ACTIVE o INACTIVE.
INST_ID – Nel caso di un RAC (Real Application Cluster), il numero dell’istanza dove è presente la sessione.
SQL_ID – Il codice identificativo della query.
PROCESS – Il processo del sistema operativo.
PROGRAM – Il client che state utilizzando.
SCHEMANAME – L’utente con è partita la sessione Oracle.
SID – Il codice identificativo della sessione Oracle.
SERIAL# – Il codice seriale univoco di una sessione Oracle.
SPID – Processo del sistema operativo legato alla sessione Oracle.
OSUSER – L’utente del sistema operativo con cui è partita la sessione Oracle.
MACHINE – Il nome del client da dove parte la sessione Oracle.
TERMINAL – Il nome del sistema operativo da dove parte la sessione Oracle.
LAST_CALL_ET – L’orario in cui la sessione ha cambiato stato da ACTIVE a INACTIVE e vice versa.
USERNAME – Nome dell’utente Oracle.
COMMAND – Il comando che è stato eseguito sul database Oracle.


Per estrapolare queste informazioni dal database Oracle, abbiamo messo in relazione tre tabelle:

GV$SESSION
GV$PROCESS
DBA_USERS

Nella WHERE condition,
per prima cosa abbiamo messo in relazione le viste
V$PROCESS e
V$SESSION attraverso la JOIN P.ADDR=S.PADDR.

Inoltre abbiamo messo in relazione la vista V$SESSION
con la vista DBA_USERS
attraverso la JOIN
S.USER#=U.USER_ID.

Infine, dopo la relazione delle tabelle V$PROCESS,
V$SESSION e
DBA_USERS,
per controllare le informazioni che abbiamo selezionato antecedenti un determinata data,
gli passiamo la condizione to_char(S.logon_time,’DD-MM-YY HH24:MI’)
con il formato indicato nella funzione to_char.

Esempio:
to_char(S.logon_time,’DD-MM-YY HH24:MI’) < ’15-01-17 12:30′

Set lines 300
Set pages 60
Col LAST_CALL for a18
Col LOGON_TIME for a18
Col schemaname for a16
Col command for a24
Col machine for a16
Col action for a12
Col username for a16
Col osuser for a16
Col terminal for a16
select to_char(S.logon_time,'DD-MON-YY hh24:mi:ss') LOGON_TIME, s.status, s.inst_id, 
s.sql_id, s.process,s.program, s.schemaname, s.sid, s.serial#, p.spid, p.pid, s.osuser, S.machine, S.terminal,to_char(sysdate -(LAST_CALL_ET/86400),'DD-MON-YY hh24:mi:ss') LAST_CALL , U.username, S.ACTION, decode(S.command,0, 'No command in progress.', 1, 'CREATE TABLE',2, 'INSERT', 3, 'SELECT', 4, 'CREATE CLUSTER', 5, 'ALTER CLUSTER', 6, 'UPDATE', 7, 'DELETE') COMMAND from gv$session S, dba_users U, gv$process P where P.ADDR = S.PADDR and S.user# = U.user_id and to_char(s.logon_time,'DD-MM-YY HH24:MI') < '&data';

Query per vedere le sessioni RMAN

.

Con l’articolo Query per vedere le sessioni RMAN, abbiamo la possibilità di verificare se il backup RMAN è running o pure no.

Nel caso ci segnalano potenziali problemi con il backup, verifichiamo se l’orario di schedulazione del backup è congruente con l’orario di accesso delle sessioni RMAN sul database. Se gli orari non sono allineati, possiamo segnalare che il backup è bloccato.

I campi che andremo a selezionare sono:

LAST_CALL_ET – L’orario in cui la sessione ha cambiato stato da ACTIVE a INACTIVE e vice versa.
STATUS – Lo stato della sessione se ACTIVE o INACTIVE.
PROCESS – Il processo del sistema operativo.
PROGRAM – Il software client da dove è partita la sessione Oracle.
SCHEMANAME – L’utente con cui è partita la sessione Oracle.
SID – Il Serial ID della sessione Oracle.
SERIAL# – Il codice seriale univoco di una sessione Oracle.
SPID – Processo del sistema operativo legato alla sessione Oracle.
OSUSER – L’utente del sistema operativo con cui è partita la sessione Oracle.
MACHINE – Il nome della macchina client da dove è partita la sessione Oracle.
TERMINAL – Il nome del sistema operativo da dove è partita la sessione Oracle.
LOGON_TIME – Indica l’orario in cui è iniziata la sessione.

Per estrapolare queste informazioni dal database Oracle, abbiamo messo in relazione tre tabelle:

GV$SESSION
GV$PROCESS
DBA_USERS

Nella WHERE condition, per prima cosa abbiamo messo in relazione le viste V$PROCESS e V$SESSION attraverso la join P.ADDR=S.PADDR
e messo in relazione la vista V$SESSION con la vista DBA_USERS attraverso la join S.USER#=U.USER_ID.
Inoltre dopo la relazione delle tabelle V$PROCESS V$SESSION e DBA_USERS per controllare le sessioni RMAN gli passiamo la condizione S.PROGRAM LIKE ‘%rman%’.

Sessioni RMAN appese:

Set lines 200
Set pages 60
select to_char(sysdate -(LAST_CALL_ET/86400),'DD-MON-YY hh24:mi:ss') LAST_CALL, s.status, s.process,s.program, s.schemaname,
s.sid, s.serial#, p.spid, s.osuser, S.machine, S.terminal, to_char(S.logon_time,'DD-MM-YYYY hh24.mi.ss') LOGON_TIME 
from gv$session S,
dba_users U,
gv$process P
where P.ADDR = S.PADDR
and S.user# = U.user_id
and s.type ='USER'
and s.username is not null
and s.program like '%rman%';

Wordpress Social Share Plugin powered by Ultimatelysocial