Differences

This shows you the differences between two versions of the page.

--- oracle:oracle_sql_querys:sessions [2018/11/14 11:14] – created dodger
+++ dba:oracle:oracle_sql_querys:sessions [2023/01/31 08:15] (current) – removed dodger
@@ Line 1: / Line 1: @@
-====== ORACLE SQL QUERIES : Sessions ======
-======SECCIÓN : Sesiones======
-=====Listar numero de sesiones=====
-<code sql>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' ;
-</code>
-===== Usuarios conectados =====
-<code sql>
-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
-/
-</code>
-===== Numero de sesiones por usuario =====
-<code sql>
-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
-/
-</code>
-=====Listar sesiones=====
-<code sql>
-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
-/
-</code>
-Con usuario inicializado:
-<code sql>
-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
-/
-</code>
-Más:
-<code sql>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)
-/
-</code>
-=====Listar sesiones ACTIVAS=====
-Con status=ACTIVE y SIN sqltext
-<code sql>
-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 ;
-</code>
-Con status=ACTIVE
-<code sql>
-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 ;
-</code>
-Mediante SQL_ID:
-<code sql>
-SET linesize 280;
-COLUMN SID_SERIAL FORMAT A20 ;
-COLUMN OSPID FORMAT A7;
-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 || ',' || ses.serial# "SID_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.SQL_ID IS NOT NULL
-AND SES.SQL_ID=SQL.SQL_ID
-AND SES.SID=WA.SID
-AND SES.paddr = p.addr
-/
-</code>
-===== Tiempo desde la última actividad =====
-<code sql>
-SELECT username, MACHINE, last_call_et seconds, STATUS
-FROM v$session
-WHERE username IS NOT NULL
-ORDER BY last_call_et
-/
-</code>
-=====Listar sesiones de usuario=====
-<code sql>select SID,SERIAL# from V$SESSION where USERNAME='USUARIO';</code>
-=====Matar sesion=====
-<code sql>alter system kill session 'SID,SERIAL' ;
-</code>
-=====Matar todas las sesiones de un usuario=====
-<code sql>
-select 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE ; '
-FROM V$SESSION
-WHERE USERNAME IN ('USERNAME1', 'USERNAMEn')
-</code>
-=====Bloqueos / locks=====
-==== HARD LOCKS ====
-  * **v$lock** based:
-^ Human readable ^ plain mode ^
-| <code sql>
-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
-/
-</code> | <code sql>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 ;
-</code> |
-  * **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:
-<code sql>
-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
-</code>
-  * Tx enqueue ready2kill:
-<code sql>
-COLUMN SID_SERIAL FORMAT A15 ;
-SELECT
-    lk.sid || ',' || se.serial# as SID_SERIAL,
-  se.username,
-  se.OSUser,
-  se.Machine,
-  DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
-  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
-  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
-  TO_CHAR(lk.id1) lock_id1,
-  TO_CHAR(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) lock_type,
-  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
-  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
-  TO_CHAR(lk.id1) lock_id1,
-  TO_CHAR(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) lock_type,
-  DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held,
-  DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested,
-  TO_CHAR (lk.id1) lock_id1,
-  TO_CHAR (lk.id2) lock_id2,
-  ob.owner,
-  ob.object_type,
-  ob.object_name,
-  DECODE(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block,
-  se.lockwait
-FROM
-  v$lock lk,
-  dba_objects 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:
-<code sql>
-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
-        )
-    )
-/
-</code>
-==== Bloqueadores ====
-Extrictamente bloqueadores (sesiones que bloquean otras):
-<code sql>SELECT * from DBA_BLOCKERS ;
-</code>
-==== lock de DDLS ====
-Mirar objetos que se están accediendo:
-<code sql>
-col object format a50 ;
-col type format a20 ;
-col owner format a20 ;
-select * from V$ACCESS where type LIKE 'TYPE'  ;
-</code>
-Y el propio bloqueo en sí:
-<code sql>
-COL SESSION_ID FORMAT 999999;
-COL OWNER FORMAT A20;
-COL NAME FORMAT A30;
-COL TYPE FORMAT A20;
-SELECT * FROM DBA_DDL_LOCKS ;
-</code>
-==== lock de DML ====
-Casi lo mismo que el anterior:
-<code sql>
-COL SESSION_ID FORMAT 999999;
-COL OWNER FORMAT A20;
-COL NAME FORMAT A30;
-COL TYPE FORMAT A20;
-SELECT * FROM DBA_DML_LOCKS ;
-</code>
-==== Links bloqueos====
-  * http://dbamohsin.wordpress.com/2011/07/19/t-sql-session-locks-blocks-waits/
-=====Consumo por sesiones=====
-<code sql>
-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;
-</code>
-A partir del *SID*, podemos saber qué _query_ están lanzando mediante:
-<code sql>
-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;
-</code>
-===== QUERYS activas =====
-<code sql>
-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;
-/
-</code>