Oracle Live

11/04/2016

QUERY – Distribución servicios

Filed under: Queries — mogukiller @ 11:24 pm
Tags:

Informacion de las sesiones de la instancia agrupadas por servicio y tipo de conexion.

/* EJ. SALIDA
                                               I1         I2           I1         I2
Username        Service Name         |  Dedicated  Dedicated |     Shared     Shared
=============== ==================== = ========== ========== = ========== ==========
JEORA2          PJEHM1_MOTOR_JEHM    |         13         14 |          0          0
OGG_TRAILS_JEHM SYS$USERS            |          0          7 |          0          0
JEORA2          PJEHM1_ONLINE_JEHM   |          7          5 |          0          0
PUBLIC          PJEHM1STB_DGB        |          0          2 |          0          0
SYS             SYS$USERS            |         11         12 |          0          0
JE              PJEHM1_ONLINE_JEHM   |          7          8 |          0          0
UOG_JEHM1       PJEHM1_oggserv       |          0          6 |          0          0
JE              PJEHM1_MOTOR_JEHM    |         54         58 |          0          0
OGG_DBFS_JEHM   SYS$USERS            |          0          5 |          0          0
                                       ---------- ----------   ---------- ----------
Total                                          92        117            0          0
*/

SET UNDERLINE =
set HEADS '/'
COL USERNAME HEADING 'Username' FOR A15
COL SERVICE_NAME HEADING 'Service Name' FOR A20
COL separador HEADING '|' FOR A1
COL I1_Dedicated HEADING 'I1/Dedicated'
COL I2_Dedicated HEADING 'I2/Dedicated'
COL I1_Shared HEADING 'I1/Shared'
COL I2_Shared HEADING 'I2/Shared'
COMPUTE SUM LABEL Total OF I1_Dedicated I2_Dedicated ON REPORT
COMPUTE SUM LABEL Total OF I1_Shared  I2_Shared ON REPORT
BREAK ON REPORT
SELECT 
	USERNAME,
	SERVICE_NAME,
	'|' as separador,
	SUM( CASE WHEN INST_ID=1 AND SERVER ='DEDICATED' THEN 1 ELSE 0 END) AS I1_Dedicated,
	SUM( CASE WHEN INST_ID=2 AND SERVER ='DEDICATED' THEN 1 ELSE 0 END) AS I2_Dedicated,
	'|' as separador,
	SUM( CASE WHEN INST_ID=1 AND (SERVER ='SHARED' OR SERVER ='NONE') THEN 1 ELSE 0 END) AS I1_Shared,
	SUM( CASE WHEN INST_ID=2 AND (SERVER ='SHARED' OR SERVER ='NONE') THEN 1 ELSE 0 END) AS I2_Shared
FROM( SELECT INST_ID, USERNAME, SERVICE_NAME, SERVER
FROM gv$session
WHERE USERNAME IS NOT NULL)  
GROUP BY USERNAME, SERVICE_NAME
/

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: