Differences

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

Link to this comparison view

oracle:oracle_sql_querys [2018/11/14 12:05]
dodger created
oracle:oracle_sql_querys [2018/11/14 12:14] (current)
dodger
Line 6: Line 6:
  
  
-======SECCIÓN : Objetos====== 
- 
-===== Compilar ===== 
- 
-==== Compilar un objeto ==== 
-<code sql>​ALTER <​OBJECT_TYPE>​ [schema.]package_name COMPILE ; 
-</​code>​ 
-  * Paquete: 
-<code sql>​ALTER PACKAGE [schema.]package_name COMPILE [DEBUG PACKAGE|SPECIFICATION|BODY];</​code>​ 
-  * Trigger: 
-<code sql>​ALTER TRIGGER [schema.]package_name COMPILE ; 
-</​code>​ 
- 
-==== Compilar todos los objetos invalidos ==== 
- 
-See: [[Recompile_it_all|Recompile all invalid objects inside database]] 
- 
- 
-===== DBMS_METADATA ===== 
- 
-==== Tuning de la salida de DBMS_METADATA ==== 
-Excluir las claúsulas de storage: 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'​STORAGE',​false);​ 
-</​code>​ 
-Excluir ​ 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'​SEGMENT_ATTRIBUTES',​TRUE);​ 
-</​code>​ 
-Que escriba el terminador de objeto (muy recomendado):​ 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'​SQLTERMINATOR',​true);​ 
-</​code>​ 
-No Incluir fk's: 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'​REF_CONSTRAINTS',​false);​ 
-</​code>​ 
-No incluir constraints:​ 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'​CONSTRAINTS',​false);​ 
-</​code>​ 
- 
-==== Obtener metadatos ==== 
-<code sql> 
-SELECT DBMS_METADATA.GET_DDL('​OBJECT_TYPE','​OBJECT_NAME','​SCHEMANAME'​) FROM DUAL; 
-</​code>​ 
- 
-Lista de nombre de objetos soportados por [[http://​docs.oracle.com/​cd/​B19306_01/​appdev.102/​b14258/​d_metada.htm#​BGBIEDIA|DBMS_METADATA]]. 
- 
-==== Obtener metadatos de dependencias ==== 
-<code sql> 
-SELECT DBMS_METADATA.GET_DEPENDENT_DDL('​OBJECT_TYPE','​OBJECT_NAME','​SCHEMANAME'​) FROM DUAL; 
-</​code>​ 
- 
- 
-==== Usar source$ para obtener metadatos ==== 
- 
-Buscar OBJ# (object identifier):​ 
-<code sql> 
-col name format a40 
-col subname format a40 
- 
-SELECT obj#, 
-  dataobj#, 
-  name, 
-  subname, 
-  CTIME , 
-  MTIME, 
-  STIME , 
-  STATUS 
-FROM obj$ 
-WHERE name LIKE '​%OBJECT_NAME%';​ 
-</​code>​ 
- 
-Buscar SOURCE: 
-<code sql> 
-SELECT * 
-FROM source$ 
-WHERE OBJ# IN 
-  (  
-  SELECT obj# FROM obj$ WHERE name LIKE '​%OBJECT_NAME%'​ 
-  ) ; 
-</​code>​ 
- 
- 
- 
-=====Performance views de oracle===== 
-<code sql>​select table_name from dict where table_name like '​V$%'​ ; 
-</​code>​ 
- 
-<code sql> 
-select name from V$FIXED_TABLE ; 
-</​code>​ 
- 
- 
-=====Tipos de objetos===== 
-No existe ninguna tabla que determine el nombre de los objetos, se pueden obtener (más o menos así): 
-<code sql> 
-SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ; 
-</​code>​ 
- 
-=====Objetos por tipo===== 
-Cualquier owner: 
-<code sql> 
-SELECT OBJECT_NAME,​ OWNER, OBJECT_TYPE 
-FROM SYS.ALL_OBJECTS 
-WHERE OBJECT_TYPE LIKE '​OBJECT_TYPE'​ 
-; 
-</​code>​ 
-Con un owner determinado:​ 
-<code sql> 
-SELECT OBJECT_NAME,​ OWNER, OBJECT_TYPE 
-FROM SYS.ALL_OBJECTS 
-WHERE OBJECT_TYPE LIKE '​OBJECT_TYPE'​ 
-AND OWNER LIKE '​OWNER'​ 
-; 
-</​code>​ 
- 
-Varios tipos 
-<code sql> 
-SELECT ​  * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('​FUNCTION','​PROCEDURE','​PACKAGE'​) ; 
-</​code>​ 
- 
-=====Tablas===== 
-====Logado como un usuario==== 
-Se pueden listar: 
-| NOMBRE DE LA TABLA | Descripción | 
-| dba_tables | objetos accesibles siendo un dba (todo) | 
-| user_tables | tablas accesibles por el usuario que estamos usando | 
-| all_tables | todo | 
- 
-<code sql>​select TABLE_NAME FROM all_tables ;</​code>​ 
-====Desde sys==== 
-<code sql>​select OBJECT_NAME from DBA_OBJECTS where OBJECT_TYPE='​TABLE'​ and OWNER like '​%username%';</​code>​ 
- 
-====Query al dict==== 
-<code sql> 
-SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '​%EXAMPLE%'​ ; 
-</​code>​ 
- 
-=====Ver índices de una tabla===== 
-<code sql> 
-select index_name from dba_indexes where table_name='​tablename'; ​ 
-</​code>​ 
-Y la descripción del índice: 
-<code sql> 
-select DBMS_METADATA.GET_DDL('​INDEX','​INDEXNAME','​SCHEMANAME'​) from DUAL; 
-</​code>​ 
- 
-O una descripción rápida: 
-<code sql> 
-COL INDEX_OWNER ​ FORMAT A30 ;      ​ 
-COL INDEX_NAME ​ FORMAT A30 ;        
-COL TABLE_OWNER ​ FORMAT A30 ;      ​ 
-COL TABLE_NAME ​ FORMAT A30 ;        
-COL COLUMN_NAME ​ FORMAT A30 ;      ​ 
-COL COLUMN_POSITION ​ FORMAT 9999999 
-COL COLUMN_LENGTH ​ FORMAT 9999999 
-COL CHAR_LENGTH ​ FORMAT 9999999 
-COL DESCEND ​ FORMAT A30 ;          ​ 
-SELECT * FROM ALL_IND_COLUMNS WHERE OWNER='<​USERNAME>'​ AND TABLE_NAME='<​TNAME>'​ ; 
-</​code>​ 
- 
-===== Columnas de las tablas ===== 
-<code sql> 
-COL OWNER FORMAT A30; 
-COL TABLE_NAME FORMAT A30; 
-COL COLUMN_NAME FORMAT A30; 
-COL DATA_TYPE FORMAT A50; 
-SELECT OWNER, ​ 
-        TABLE_NAME, ​ 
-        COLUMN_NAME, ​ 
-        DATA_TYPE, ​ 
-        DATA_LENGTH ​ 
-FROM ALL_TAB_COLUMNS ​ 
-WHERE OWNER = '<​USERNAME>'​ 
-ORDER BY OWNER,​TABLE_NAME ; 
-</​code>​ 
- 
-===== Tablas particionadas ===== 
-==== Listar tablas particionadas de los usuarios ==== 
-<code sql> 
-SELECT TABLE_OWNER,​ TABLE_NAME ​ 
-FROM  ALL_TAB_PARTITIONS ​ 
-WHERE TABLE_OWNER NOT IN ('​SYS',​ '​SYSTEM'​) ​ 
-GROUP BY TABLE_OWNER,​ TABLE_NAME ; 
-</​code>​ 
- 
-Más info: 
-<code sql> 
-col table_owner format a20 
-col COMPRESS_FOR format a30 
- 
-SELECT 
-TABLE_OWNER,​ 
-TABLE_NAME, 
-PARTITION_NAME,​ 
-PARTITION_POSITION,​ 
-TABLESPACE_NAME,​ 
-LOGGING, 
-COMPRESSION,​ 
-COMPRESS_FOR,​ 
-AVG_ROW_LEN 
-FROM DBA_TAB_PARTITIONS ​ 
-WHERE TABLE_OWNER NOT IN ('​SYS',​ '​SYSTEM'​) 
-order by PARTITION_NAME 
-/ 
-</​code>​ 
-==== Numero de rows de cada partición ==== 
-<code sql> 
-COL TABLE_OWNER FORMAT A20; 
-COL TABLE_NAME FORMAT A20; 
-SELECT TABLE_OWNER,​ TABLE_NAME, PARTITION_POSITION,​ NUM_ROWS 
-FROM ALL_TAB_PARTITIONS 
-WHERE TABLE_OWNER NOT IN ('​SYS',​ '​SYSTEM'​) 
-AND NUM_ROWS IS NOT NULL 
-ORDER BY PARTITION_POSITION DESC, TABLE_NAME ASC ; 
-</​code>​ 
- 
- 
- 
-=====Jerarquía de tablas===== 
-dead :-/ 
-  * [[https://​netfiles.uiuc.edu/​jstrode/​www/​oradd/​dict_catagories.html]] 
- 
-Encontrar Tablas Maestras/​padres/​primarias (tablas SIN fk's y referenciadas por otras tablas): 
-<code sql> 
-SELECT TABLE_NAME 
-FROM dba_CONSTRAINTS 
-WHERE owner='<​USERNAME>'​ 
-AND CONSTRAINT_NAME IN ( 
-    SELECT R_CONSTRAINT_NAME 
-    FROM dba_CONSTRAINTS 
-    WHERE owner='<​USERNAME>'​ 
-    AND CONSTRAINT_TYPE='​R'​ 
-    ) 
-AND NOT TABLE_NAME IN ( 
-    SELECT TABLE_NAME 
-    FROM dba_CONSTRAINTS 
-    WHERE owner='<​USERNAME>'​ 
-    AND CONSTRAINT_TYPE='​R'​ 
-    ) 
-ORDER BY TABLE_NAME 
-/ 
-</​code>​ 
-=====Dependencias entre objetos===== 
- 
- 
-==== Query genérica para buscar deps ==== 
-<code sql> 
-COL NAME FORMAT A35 
-COL OWNER FORMAT A30 
-COL TYPE FORMAT A30 
-COL REFERENCED_OWNER FORMAT A30 
-COL REFERENCED_TYPE FORMAT A30 
-COL DEPENDENCY_TYPE FORMAT A20 
-COL REFERENCED_NAME FORMAT A33 
-select ​ 
-   ​owner, ​ 
-   ​type, ​ 
-   name, 
-   ​referenced_owner, ​ 
-   ​referenced_type, ​ 
-   ​referenced_name 
-from  
-   ​dba_dependencies 
-where  
-   ​((owner like upper('&​1'​) and name like upper('&​2'​)) ​ 
-   or 
-   ​(referenced_owner like upper('&​1'​) and referenced_name like upper('&​2'​) )) 
-   ​and ​ 
-   ​referenced_owner != '​SYS'​ 
-   ​and ​ 
-   ​referenced_type != '​NON-EXISTENT'​ 
-order by  
-   ​owner,​ type, name; 
-</​code>​ 
- 
-==== Using utldtree.sql ==== 
-Ejecutar: 
-<​code>​ 
-@${ORACLE_HOME}/​rdbms/​admin/​utldtree.sql 
-</​code>​ 
- 
-Después ejecutar para el objetos que queramos, el procedure: 
-<code sql> 
-EXEC deptree_fill('​object_type',​ '​object_owner',​ '​object_name'​);​ 
-</​code>​ 
-y ver el resultado: 
-<code sql> 
-select * from ideptree; 
-</​code>​ 
- 
-==== dba_dependencies.sql==== 
- 
-[[http://​www.freelists.org/​post/​oracle-l/​Shared-Pool-causing-packages-to-automatically-go-invalid,​4|Link]] 
- 
- 
-//The attached SQL file will show all dependencies for an object, 
-both forwards and backwards through the heirarchy of objects 
-in DBA_DEPENDENCIES,​ along with the LAST_DDL_TIME of all children.// 
- 
-//The LAST_DDL_TIME should help track down why an object became invalid.// 
- 
-//I'm fairly sure this query will not work in 8i. :(// 
- 
-//You can circumvent that by logging into some account other than SYS 
-and creating a table DBA_DEPENDENCIES. ​ (it doesn'​t work in 8i due 
-to queries on complex views and '​connect by' not working)// 
- 
-''​create table scott.dba_dependencies 
-as 
-select * from sys.dba_dependencies ;''​ 
- 
- 
- 
- 
-<code sql> 
--- 
-Jared Still 
-Certifiable Oracle DBA and Part Time Perl Evangelist 
--- dba_dependencies.sql 
--- jkstill@xxxxxxxxx 
--- base query from Jacques Kilchoer 
--- 11/07/2006 - jkstill - added no_merge hints 
---   ​encapsulated into inline view 
---   added '​level'​ 
---   ​display child DDL time 
--- 
--- call on the command line: 
---   ​@dba_dependencies <​OWNER>​ <​OBJECT>​ 
--- if not on the command line, user will be 
--- prompted for values 
- 
- 
-@clears 
-@columns 
- 
-prompt Dependencies for Owner?: 
- 
-col cowner noprint new_value uowner 
-set term off feed off 
-select upper('&​1'​) cowner from dual; 
-set term on feed on 
- 
-prompt Dependencies for Object?: 
- 
-col cobject noprint new_value uobject 
-set term off feed off 
-select upper('&​2'​) cobject from dual; 
-set term on feed on 
- 
-set line 142 pages 60 
- 
-column display_parent format a58 
-column display_child format a58 
-column referenced_owner noprint 
-column referenced_object noprint 
-column referenced_type noprint 
-column owner noprint 
-column object noprint 
-column type noprint 
-column last_ddl_time format a22 head 'CHILD DDL TIME' 
- 
-undef 1 2 
- 
-with dependencies as ( 
-        -- top down through the heirarchy 
-        select /*+ no_merge */ 
-                referenced_type || ' "'​ || referenced_owner || '"​."'​ || 
-                referenced_name || '"'​ as parent, 
-                type || ' "'​ || owner || '"​."'​ || name || '"'​ as child, 
-                level hlevel, 
-                referenced_owner,​ referenced_name,​ referenced_type,​ 
-                owner, name, type 
-        from dba_dependencies 
-        start with 
-                referenced_owner = '&&​uowner'​ 
-                and referenced_name = '&&​uobject'​ 
-        connect by 
-                referenced_owner = prior owner 
-                and referenced_name = prior name 
-                and referenced_type = prior type 
-        union 
-        -- bottom up through the heirarchy 
-        select /*+ no_merge */ 
-                referenced_type || ' "'​ || referenced_owner || '"​."'​ || 
-                referenced_name || '"'​ as parent, 
-                type || ' "'​ || owner || '"​."'​ || name || '"'​ as child, 
-                level hlevel, 
-                referenced_owner,​ referenced_name,​ referenced_type,​ 
-                owner, name, type 
-        from dba_dependencies 
-        start with 
-                owner = '&&​uowner'​ 
-                and name = '&&​uobject'​ 
-        connect by 
-                owner = prior referenced_owner 
-                and name = prior referenced_name 
-                and type = prior referenced_type 
-        order by 1, 2 
-) 
-select lpad(' ',​2*d.hlevel,'​ ') || d.parent display_parent,​ d.child ​ 
-display_child,​ o.last_ddl_time ​ 
-from dependencies d, dba_objects o 
-where o.owner = d.owner 
-and o.object_type = d.type 
-and d.name = o.object_name 
-order by parent, child 
-/ 
-</​code>​ 
- 
-==== Listar foreign keys de una tabla ==== 
- 
-<code sql> 
-COL OWNER FORMAT A30 
-COL R_OWNER FORMAT A30 
-COL CONSTRAINT_NAME FORMAT A50 
-COL R_CONSTRAINT_NAME FORMAT A50 
- 
-SELECT OWNER, 
-        CONSTRAINT_NAME,​ 
-        CONSTRAINT_TYPE,​ 
-        TABLE_NAME, 
-        R_OWNER, 
-        R_CONSTRAINT_NAME,​ 
-        STATUS 
-FROM USER_CONSTRAINTS ​ 
-WHERE TABLE_NAME LIKE '​%TABLE_NAME%'​ 
-; 
-</​code>​ 
- 
- 
- 
-==== More info ==== 
-  * [[http://​docs.oracle.com/​cd/​B28359_01/​server.111/​b28318/​dependencies.htm]] 
-  * [[http://​www.dba-oracle.com/​d_dba_dependencies.htm]] 
- 
- 
- 
-======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>​ 
 ======SECCIÓN : QUERYS====== ======SECCIÓN : QUERYS======
  
  • oracle/oracle_sql_querys.txt
  • Last modified: 2018/11/14 12:14
  • by dodger