Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:oracle_sql_querys:sessions

This is an old revision of the document!

Table of Contents

ORACLE SQL QUERIES : Sessions

SECCIÓN : Sesiones

Listar numero de sesiones

SELECT	rpad(c.name||':',11)||
	rpad(' current logons='||(to_number(b.sessions_current)),20)||
	'cumulative logons='||rpad(substr(a.value,1,10),10)||
	'highwater mark='||b.sessions_highwater Information
FROM
   v$sysstat a,
   v$license b,
   v$database c
WHERE
   a.name = 'logons cumulative' ;

Usuarios conectados

col ID format a15;
col USERNAME FORMAT A30 ;
col OSUSER FORMAT A40 ;
COL MACHINE FORMAT A20 WORD WRAPPED;
COL "SID,SERIAL" FORMAT A15;
SELECT USERNAME, 
        SID || ',' || SERIAL# "SID,SERIAL", 
        STATUS, 
        OSUSER, 
        MACHINE, 
        SQL_ID, 
        LAST_CALL_ET "Last Activity"
FROM v$session
WHERE username IS NOT NULL
ORDER BY STATUS DESC, last_call_et DESC
/

Numero de sesiones por usuario

COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
COLUMN num_of_sessions FORMAT 99999999999 ;
SELECT ses.username username,
        COUNT(*) num_of_sessions
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
AND ses.username IS NOT NULL
GROUP BY ses.username
ORDER BY num_of_sessions
/

Listar sesiones

COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
col client_info format a40 ;
COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid AS OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      SES.PROGRAM PROGRAM,
      SES.CLIENT_INFO
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
ORDER BY SES.MACHINE
/

Con usuario inicializado: 

COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid AS OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      SES.PROGRAM PROGRAM,
      SES.CLIENT_INFO
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
AND ses.username IS NOT NULL
ORDER BY SES.MACHINE
/

Más: 

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a80
SELECT s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
ORDER BY to_number(p.spid)
/

Listar sesiones ACTIVAS

Con status=ACTIVE y SIN sqltext 

SET linesize 280;
COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
col OSUSER format a30 ;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.STATUS='ACTIVE'
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/
CLEAR COLUMNS ;

Con status=ACTIVE 

SET linesize 280;
COLUMN SID FORMAT 99999 ;
COLUMN SERIAL FORMAT 999999 ;
COLUMN OSPID FORMAT A5;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A20 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
COLUMN P1TEXT FORMAT A20 WORD_WRAPPED;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT SES.SID SID,
      SES.SERIAL# SERIAL,
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.P1TEXT P1TEXT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS,
      SQL.SQL_TEXT QUERY
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.STATUS='ACTIVE'
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/
CLEAR COLUMNS ;

Mediante SQLID: <code sql> SET linesize 280; COLUMN SIDSERIAL FORMAT A20 ; COLUMN OSPID FORMAT A7; COLUMN SQLID FORMAT A15 WORDWRAPPED ; COLUMN USERNAME FORMAT A20 WORDWRAPPED ; COLUMN HOSTNAME FORMAT A40 WORDWRAPPED ; COLUMN PROGRAM FORMAT A20 WORDWRAPPED ; COLUMN EVENT FORMAT A60 WORDWRAPPED ; COLUMN P1TEXT FORMAT A20 WORDWRAPPED; COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ; COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ; COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORDWRAPPED ; SELECT ses.sid || ',' || ses.serial# “SIDSERIAL”, 

    p.spid OSPID,
    SES.SQL_ID SQLID,
    SES.USERNAME USERNAME,
    SES.MACHINE HOSTNAME,
    lpad(SES.PROGRAM,15) PROGRAM,
    LPAD(WA.EVENT,30) EVENT,
    WA.P1TEXT P1TEXT,
    WA.WAIT_CLASS WAITCLASS,
    WA.SECONDS_IN_WAIT WAITINGSECS,
    SQL.SQL_TEXT QUERY

FROM V$SESSION SES, V$SQLAREA SQL, V$SESSIONWAIT WA, V$PROCESS P WHERE SES.SQLID IS NOT NULL AND SES.SQLID=SQL.SQLID AND SES.SID=WA.SID AND SES.paddr = p.addr / </code>

Tiempo desde la última actividad

SELECT username, MACHINE, last_call_et seconds, STATUS
FROM v$session
WHERE username IS NOT NULL
ORDER BY last_call_et
/

Listar sesiones de usuario

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USUARIO';

Matar sesion

ALTER system KILL SESSION 'SID,SERIAL' ;

Matar todas las sesiones de un usuario

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE ; ' 
FROM V$SESSION 
WHERE USERNAME IN ('USERNAME1', 'USERNAMEn')

Bloqueos / locks

HARD LOCKS

  • v$lock based:
    Human readable plain mode 
    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
/
    		 
    SELECT L1.SID, L2.SID
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 ;
  • v$session based: <code sql> col wait_class format a40 col event format a60

SELECT 

  sid || '.' || serial# sid_serial,
  username,
  osuser,
  wait_class_id,
  wait_class#,
  wait_class,
  event

FROM 

  v$session

WHERE 

      state = 'WAITING'
  AND
      wait_class = 'Concurrency';

</code>

Bloqueos de usuario (Soft locks)

Formateo: 

COL USERNAME FORMAT A23
COL OSUser FORMAT A23
COL Machine FORMAT A33
COL mode_held FORMAT A23
COL mode_requested FORMAT A23
COL lock_type format A25
COL lock_id1 format A10
COL lock_id2 format a10
COL object_name format a33
col object_type format a20
  • Tx enqueue ready2kill: <code sql> COLUMN SIDSERIAL FORMAT A15 ; SELECT lk.sid || ',' || se.serial# as SIDSERIAL, se.username, se.OSUser, se.Machine, DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) locktype, DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TOCHAR(lk.lmode)) modeheld, DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TOCHAR(lk.request)) moderequested, TOCHAR(lk.id1) lockid1, TOCHAR(lk.id2) lock_id2, DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block, se.lockwait FROM v$lock lk, v$session se WHERE lk.type = 'TX' AND lk.SID = se.SID ; </code>
  • Transaction enqueue (original): <code sql> SELECT lk.SID, se.username, se.OSUser, se.Machine, DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) locktype, DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TOCHAR(lk.lmode)) modeheld, DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TOCHAR(lk.request)) moderequested, TOCHAR(lk.id1) lockid1, TOCHAR(lk.id2) lock_id2, DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block, se.lockwait FROM v$lock lk, v$session se WHERE lk.type = 'TX' AND lk.SID = se.SID ; </code>
  • DML enqueue & User supplied: <code sql> SELECT lk.SID, se.username, se.OSUser, se.Machine, DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) locktype, DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TOCHAR (lk.lmode)) modeheld, DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TOCHAR (lk.request)) moderequested, TOCHAR (lk.id1) lockid1, TOCHAR (lk.id2) lockid2, ob.owner, ob.objecttype, ob.objectname, DECODE(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block, se.lockwait FROM v$lock lk, dbaobjects ob, v$session se WHERE lk.TYPE IN ('TM','UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id ; </code>

Sid,SERIAL# de los bloqueantes primarios

Sesiones que inician la cadena de bloqueos: 

SELECT SID || ',' || SERIAL#
FROM V$SESSION
WHERE SID IN (
    SELECT l1.sid
    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
    AND l1.id1 NOT IN (
        SELECT l2.sid
        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
        )
    )
/

Bloqueadores

Extrictamente bloqueadores (sesiones que bloquean otras): 

SELECT * FROM DBA_BLOCKERS ;

lock de DDLS

Mirar objetos que se están accediendo: 

col object format a50 ;
col TYPE format a20 ;
col owner format a20 ;
SELECT * FROM V$ACCESS WHERE TYPE LIKE 'TYPE'  ;

Y el propio bloqueo en sí: 

COL SESSION_ID FORMAT 999999;
COL OWNER FORMAT A20;
COL NAME FORMAT A30;
COL TYPE FORMAT A20;
SELECT * FROM DBA_DDL_LOCKS ;

lock de DML

Casi lo mismo que el anterior: 

COL SESSION_ID FORMAT 999999;
COL OWNER FORMAT A20;
COL NAME FORMAT A30;
COL TYPE FORMAT A20;
SELECT * FROM DBA_DML_LOCKS ;

Consumo por sesiones

SELECT TO_CHAR(m.END_TIME,(''DD-MM-YYYY HH24:MI:SS'')) e_dtm, m.intsize_csec/100 ints, s.username usr, m.session_id sid, m.session_serial_num ssn,
	ROUND(m.cpu) cpu100, m.physical_reads prds, m.logical_reads lrds, m.pga_memory pga, m.hard_parses hp, m.soft_parses sp, m.physical_read_pct prp,
	m.logical_read_pct lrp
FROM v$sessmetric m, v$session s
WHERE (m.physical_reads > 100 OR m.cpu > 100 OR m.logical_reads > 100) AND m.session_id = s.sid  AND m.session_serial_num = s.serial#
ORDER BY m.physical_reads DESC, m.cpu DESC, m.logical_reads DESC;

A partir del SID, podemos saber qué query están lanzando mediante: 

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
AND c.sid = &SID
ORDER BY  c.sid, d.piece;

QUERYS activas

SET feedback off
SET serveroutput ON SIZE 9999
COLUMN username format a20
COLUMN sql_text format a55 word_wrapped
BEGIN
  FOR x IN
   (SELECT username||'('||sid||','||serial#||') ospid = '|| process || ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') CURRENT_TIME,
    sql_address,
    sql_hash_value
   FROM v$session
   WHERE STATUS = 'ACTIVE'
   AND rawtohex(sql_address) <> '00'
   AND username IS NOT NULL ) loop
   FOR y IN (SELECT sql_text
   FROM v$sqlarea
   WHERE address = x.sql_address ) loop
   IF ( y.sql_text NOT LIKE '%listener.get_cmd%' AND
    y.sql_text NOT LIKE '%RAWTOHEX(SQL_ADDRESS)%' ) THEN
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   END IF;
  END loop;
 END loop;
END;
/
dba/oracle/oracle_sql_querys/sessions.1644577947.txt.gz · Last modified: 2022/02/11 11:12 by dodger