Archivo de la categoría: DEBUN_SQL

SQL – Encapsulado de sentencias complejas

En muchas ocasiones el acceso a un conjunto de datos almacenado en base de datos requiere de una consulta especialmente compleja. En los casos en que se debe permitir acceso a sistemas externos a estos datos, la solución ideal pasa por implementar una capa de negocio intermedia entre la aplicación consumidora de la información y la base de datos, sea mediante servicios web, un data-grid u otra componente que permita abstraer la complejidad de la obtención de los datos para facilitarla al sistema externo.
Hay casos en que esto no es posible y por requerimientos de arquitectura en el sistema, las aplicaciones externas deben acceder directamente a la base de datos para obtener la información. En estos supuestos, es buena idea implementar una interfaz para la obtención de los datos, una API que permita crear una caja negra sobre la implementación real de la obtención de los datos. Con ello se consigue:
  • Las modificación de las estructuras subyacentes de la base de datos no afectan a la integración con el sistema externo al mantenerse la interfaz de obtención de datos.
  • Permite mantener el control de cómo se obtienen los datos, lo que minimiza la posibilidad de errores al estar centralizada en una única implementación.
  • Ante la aparición de errores, se minimiza el tiempo necesario para la corrección, ya que estaría localizada en un único punto: La implementación de la interfaz.
En este post se propone un posible mecanismo para construir estas interfaces de acceso aplicado a bases de datos Oracle.

Seguir leyendo SQL – Encapsulado de sentencias complejas

SQL – Expresiones de Tablas Comunes (CTE)

Las Expresiones de Tabla Común, en inglés Common Table Expressions (CTE), se pueden definir cómo la especificación de un conjunto de resultados temporales que se obtienen a través de una subconsulta determinada. El ámbito de aplicación de este conjunto de datos queda restringido a una ejecución concreta de la instrucción SQL en la que se encuentra definida (SELECT, INSERT, UPDATE o DELETE), momento a partir del cual, dichos resultados son eliminados del contexto de ejecución. Dicho de una manera un tanto simplista y haciendo un símil con conceptos aplicables a la programación imperativa, sería cómo la definición de una subrutina local, en el sentido que permite definir un «código», en este caso una consulta que devuelve un conjunto de resultados determinados, asignarlo a un identificador determinado y usarlo cómo referencia dentro de otras partes de la consulta principal.

Algunas de las ventajas que proporciona su utilización son:

  • Permite evitar la reevaluación de una subconsulta que se ejecute múltiples veces dentro de la consulta principal
  • Simplifica la escritura y legibilidad de consultas complejas en las que se realicen definiciones de tablas derivadas (definidas dentro de la clausula FROM cómo una subconsulta) en uno o múltiples niveles
  • Permite substituir la definición de vistas globales cuando sólo están restringidas a una sola consulta, ayudando a no «ensuciar» el espacio global de nombres con definiciones innecesarias
  • Permite realizar operaciones de agrupación o condicionales sobre datos derivados de operaciones escalares o no deterministas. En este caso se podrá definir el cálculo de las operaciones requeridas dentro de la consulta CTE y posteriormente realizar las agrupaciones necesarias en la consulta principal o en otra CTE como si de valores de una tabla corriente se tratara

Seguir leyendo SQL – Expresiones de Tablas Comunes (CTE)

Función CONVERT en BBDD Oracle 10g

La función CONVERT permite convertir un carácter de un conjunto específico de caracteres a otro carácter de otro conjunto específico de caracteres.

En el caso concreto de la aplicación en la que se está trabajando se desea realizar una consulta sobre una tabla concreta para recuperar una descripción que contenga la palabra avión, como caso de ejemplo. Las descripciones a recuperar son las siguientes

  • Auxiliares de vuelo y camareros de avión, barco y tren                         
  • Mecánicos y ajustadores de motores de avión

mediante la siguiente consulta

SELECT 
                des.des_dcol
FROM 
               TABLA_DESCRIPCIONES des
WHERE 
               UPPER(des.des_dcol) LIKE UPPER(‘%avión%’));

No encuentra ningún resultado dado que realiza la consulta estrictamente con acento y aunque existe en la tabla no lo retorna correctamente.

Aquí es dónde entra la utilización de la función CONVERT. La siguiente consulta busca las descripciones que contengan el valor %avión% mostrando el resultado correctamente.

SELECT 
                des.des_dcol
FROM 
               TABLA_DESCRIPCIONES des
WHERE 
               UPPER(CONVERT(des.des_dcol, ‘US7ASCII‘)) 
               LIKE UPPER(CONVERT(‘%avión%’, ‘US7ASCII‘));

Las descripciones recuperadas son las esperadas como se ha comentado con anterioridad.

  • Auxiliares de vuelo y camareros de avión, barco y tren                         
  • Mecánicos y ajustadores de motores de avión

La clave se encuentra en el parámetro que se le pasa, US7ASCII, correspondiente una de las codificaciones de caracteres comunes (ASCII US 7-bit) de las que puede gestionar la función..

Hasta aquí la prueba de concepto de esta función de Oracle. Para un mayor detalle de la misma se puede acceder a la siguiente ruta.

 

Quick Reference: Oracle DML and DDL Statements

Just a quick reference of DML and DDL Oracle Statements and some links to visit.

DML Statements

Main Site: Oracle DML Statements

Insert
INSERT INTO table_name (list_of_columns)
VALUES (list_of_values);

Update
UPDATE table_name
SET column_name = value [, column_name = value]…
[ WHERE condition ];

Delete
DELETE FROM table_name
[ WHERE condition ];

DDL Statements

Main Site:  DDL Oracle Statements

Create
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],

column_n datatype [ NULL | NOT NULL ] );

Alter
ALTER TABLE table_name
ADD column_name column-definition;

Drop
DROP [schema_name].TABLE table_name
[ CASCADE CONSTRAINTS ] [ PURGE ];

Extra

Some sites which contains interesting information about the last topics: 1, 2, 3.

Consultas SQL sobre las vistas del diccionario de Oracle

 

Se añaden consultas para recuperar información sobre el diccionario de Oracle. La mayoría han funcionado correctamente para  la versión 10.1.0.2.0 de Oracle Database 10g release 1. Su documentación se puede encontrar en este enlace.

Consulta Oracle SQL sobre la vista que muestra el estado de la base de datos

    SELECT * FROM v$instance;

Consulta Oracle SQL que muestra si la base de datos está abierta

SELECT status FROM v$instance;

Consulta Oracle SQL sobre la vista que muestra los parámetros generales de Oracle

    SELECT * FROM v$system_parameter;

Consulta Oracle SQL para conocer la Versión de Oracle

    SELECT value FROM v$system_parameter where name = ‘compatible’;

Consulta Oracle SQL para conocer la Ubicación y nombre del fichero spfile

    SELECT value FROM v$system_parameter where name = ‘spfile’

Consulta Oracle SQL para conocer la Ubicación y número de ficheros de control

    SELECT value FROM v$system_parameter where name = ‘control_files’;

Consulta Oracle SQL para conocer el Nombre de la base de datos

    SELECT value FROM v$system_parameter where name = ‘db_name’;

Consulta Oracle SQL sobre la vista que muestra las conexiones actuales a Oracle. Para visualizarla es necesario entrar con privilegios de administrador

    SELECT osuser, username, machine, program
FROM v$session
ORDER BY osuser;

Consulta Oracle SQL para matar una sesión Oracle

    SELECT sid, serial# FROM v$session where username='<usuario>’;
    ALTER SYSTEM kill session ‘<sid, serial>’;

Consulta Oracle SQL que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión

    SELECT program Aplicacion, count(program) Numero_Sesiones
FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones desc;

Consulta Oracle SQL que muestra los usuarios de Oracle conectados y el número de sesiones por usuario

    SELECT username Usuario_Oracle, count(username) Numero_Sesiones
FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones desc;

Consulta Oracle SQL que muestra propietarios de objetos y número de objetos por propietario

    SELECT owner, count(owner) Numero
FROM dba_objects
GROUP BY owner;

Consulta Oracle SQL sobre el Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos)

    SELECT * FROM dictionary;

Consulta Oracle SQL que muestra los datos de una tabla especificada

    SELECT * FROM ALL_ALL_TABLES where upper(table_name) like ‘%<cadena_texto>%’;

Consulta Oracle SQL que muestra las descripciones de los campos de una tabla especificada

    SELECT * FROM ALL_COL_COMMENTS where upper(table_name) like ‘%<cadena_texto>%’;

Consulta Oracle SQL para conocer las tablas propiedad del usuario actual

    SELECT * FROM user_tables;

Consulta Oracle SQL para conocer todos los objetos propiedad del usuario conectado a Oracle

    SELECT * FROM user_catalog;

Consulta Oracle SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos

    SELECT t.tablespace_name «Tablespace», t.status «Estado»,
ROUND(MAX(d.bytes)/1024/1024,2) «MB Tamaño»,
ROUND((MAX(d.bytes)/1024/1024) –
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) «MB Usados»,
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) «MB Libres»,
t.pct_increase «% incremento»,
SUBSTR(d.file_name,1,80) «Fichero de datos»
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status
ORDER BY 1,3 DESC;

Consulta Oracle SQL para conocer los productos Oracle instalados y la versión

    SELECT * FROM product_component_version;

Consulta Oracle SQL para conocer los roles y privilegios por roles

SELECT * FROM role_sys_privs;

Consulta Oracle SQL para conocer las reglas de integridad y columna a la que afectan

SELECT constraint_name, column_name FROM sys.all_cons_columns;

Consulta Oracle SQL para conocer las tablas de las que es propietario un usuario

    SELECT table_owner, table_name FROM sys.all_synonyms where table_owner like ‘<usuario>’;

Variante: Consulta Oracle SQL más efectiva

    SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE ‘HR’;

Parámetros de Oracle, valor actual y su descripción

    SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, ‘DEFERRED’,
‘TRUE’, ‘FALSE’) ISSYS_MODIFIABLE, decode(v.isDefault, ‘TRUE’, ‘YES’,
‘FALSE’, ‘NO’) «DEFAULT», DECODE(ISSES_MODIFIABLE, ‘IMMEDIATE’,
‘YES’,’FALSE’, ‘NO’, ‘DEFERRED’, ‘NO’, ‘YES’) SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, ‘IMMEDIATE’, ‘YES’, ‘FALSE’, ‘NO’,
‘DEFERRED’, ‘YES’,’YES’) SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like ‘nls%’ 

    ORDER BY 1;

Consulta Oracle SQL que muestra los usuarios de Oracle y datos suyos
(fecha de creación, estado, id, nombre, tablespace temporal,…)

    SELECT * FROM dba_users;

Consulta Oracle SQL para conocer tablespaces y propietarios de los mismos

    SELECT owner, decode(partition_name, null, segment_name,
segment_name || ‘:’ || partition_name) name,
segment_type, tablespace_name,bytes,initial_extent,
next_extent, PCT_INCREASE, extents, max_extents
FROM dba_segments
Where 1=1 AND extents > 1 

    ORDER BY 9 desc, 3;
Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó

    SELECT distinct 
    vs.sql_text, vs.sharable_mem,
vs.persistent_mem, vs.runtime_mem, vs.sorts,
vs.executions, vs.parse_calls, vs.module,
vs.buffer_gets, vs.disk_reads, vs.version_count,
vs.users_opening, vs.loads,
to_char(to_date(

       vs.first_load_time, ‘YYYY-MM-DD/HH24:MI:SS’),’MM/DD HH24:MI:SS’) first_load_time,
rawtohex(vs.address) address, vs.hash_value hash_value ,
rows_processed , vs.command_type, vs.parsing_user_id ,
OPTIMIZER_MODE , au.USERNAME parseuser
FROM v$sqlarea vs , all_users au
where (parsing_user_id != 0) AND
(au.user_id(+)=vs.parsing_user_id)
AND (executions >= 1) ORDER BY buffer_gets/executions desc;

Consulta Oracle SQL para conocer todos los tablespaces

    SELECT * FROM V$TABLESPACE;

Consulta Oracle SQL para conocer la memoria Share_Pool libre y usada

SELECT name, to_number(value) bytes
FROM v$parameter where name =’shared_pool_size’
union all
SELECT name,bytes
FROM v$sgastat where pool = ‘shared pool’ AND name = ‘free memory’;

Cursores abiertos por usuario

SELECT b.sid, a.username, b.value Cursores_Abiertos
FROM v$session a,
v$sesstat b,
v$statname c
where c.name in (‘opened cursors current’)
AND b.statistic# = c.statistic#
AND a.sid = b.sid
AND a.username is not null
AND b.value >0
ORDER BY 3;

Consulta Oracle SQL para conocer los aciertos de la caché (no debería superar el 1 por ciento)

SELECT sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
FROM v$librarycache
where namespace in (‘TABLE/PROCEDURE’, ‘SQL AREA’, ‘BODY’, ‘TRIGGER’);

Sentencias SQL completas ejecutadas con un texto determinado en el 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 upper(d.sql_text) like ‘%WHERE <nombre_campo> LIKE%’
ORDER BY c.sid, d.piece;

Una sentencia SQL concreta (filtrado por sid)

    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 sid = 105
ORDER BY c.sid, d.piece;

Consulta Oracle SQL para conocer el tamaño ocupado por la base de datos

    SELECT sum(BYTES)/1024/1024 MB FROM DBA_EXTENTS;

Consulta Oracle SQL para conocer el tamaño de los ficheros de datos de la base de datos

    SELECT sum(bytes)/1024/1024 MB FROM dba_data_files;

Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta sin incluir los índices de la misma

    SELECT sum(bytes)/1024/1024 MB FROM user_segments
where segment_type=’TABLE’ AND segment_name='<nombre_tabla>’;

Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta incluyendo los índices de la misma

SELECT sum(bytes)/1024/1024 Table_Allocation_MB FROM user_segments
where segment_type in (‘TABLE’,’INDEX’) AND
(segment_name='<nombre_tabla>’ OR segment_name in
(SELECT index_name FROM user_indexes where table_name='<nombre_tabla>’));

Consulta Oracle SQL para conocer el tamaño ocupado por una columna de una tabla

    SELECT sum(vsize(‘<nombre_columna’))/1024/1024 MB 
    FROM <nombre_tabla>;

Consulta Oracle SQL para conocer el espacio ocupado por usuario

    SELECT owner, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
GROUP BY owner;

Consulta Oracle SQL para conocer el espacio ocupado por los diferentes segmentos
(tablas, índices, undo, rollback, cluster, …)

    SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
GROUP BY SEGMENT_TYPE;

Consulta Oracle SQL para obtener todas las funciones de Oracle: NVL, ABS, LTRIM, …

    SELECT distinct object_name
FROM all_arguments
WHERE package_name = ‘STANDARD’
ORDER BY object_name;

Consulta Oracle SQL para conocer el espacio ocupado por todos los objetos de la base de datos,
muestra los objetos que más ocupan primero

    SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
GROUP BY SEGMENT_NAME
ORDER BY 2 desc;

Consulta Oracle SQL para recuperar los indices de una tabla específica

    SELECT  index_name, index_type, table_owner, table_name, table_type
FROM user_indexes
WHERE table_name = ‘<nom_taula>’;

Consulta Oracle SQL para obtener todas las tablas que utilizan un campo concreto

    SELECT  table_name
FROM all_tab_columns
WHERE column_name = ‘<nom_columna>’;

Una variante sobre la consulta incluyendo el propietario y un tipo de dato específico:

    SELECT  table_name
FROM all_tab_columns
WHERE column_name = ‘<nom_columna>’ and
data_type = ‘NVARCHAR2’    and
owner = «<schema_owner>
ORDER BY table_name;

Cálculo del tamaño de una BBDD Oracle

  • Incluye el tamaño de los archivos de datos en la búsqueda

El tamaño total incluye tablas, campos, procedimientos almacenados y otros objetos de la base de datos.

Calcula el tamaño de la vista «dba_data_files»:

SELECT SUM(bytes)/1024/1024/1024 data_size FROM dba_data_files;

 

  • Calcula el tamaño de los archivos temporales

Estos conservan datos durante el proceso pero no es un almacenamiento permanente.

Calcula el tamaño del archivo temporal:

SELECT NVL(SUM(bytes),0)/1024/1024/1024 temp_size FROM dba_temp_files;

 

  • Obtener el tamaño del redo log

Esto almacena cualquier cambio en la base de datos antes de ser aplicado en los datos actuales de la base de datos.

Esto ofrece una manera de almacenar la base de datos en su estado orignal previo a una consulta diseñada para modificar cualquier información.

SELECT SUM(bytes)/1024/1024/1024 redo_size FROM sys.v_$log;

 

  • Tamaño del archivo de control usado por Oracle utilizando la vista V$CONTROLFILE

Esta vista se utiliza para obtener información del esquema de la base de datos i de los objetos contenidos en la misma.

Para obtener el tamaño del archivo de control hace falta ejecutar:

SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size 
FROM v$controlfile;

 

  • Combinar las anteriores consultas para obtener el tamaño de la base de datos

Resultado obtenido el tamaño total de la base de datos en gigabytes:

SELECT d.data_size, t.temp_size, r.redo_size
FROM  ( SELECT NVL(bytes)/1024/1024/1024 data_size FROM dba_data_files) d,
( SELECT NVL(sum(bytes),0)/1024/1024/1024 temp_size FROM dba_temp_files ) t,
( SELECT SUM(bytes)/1024/1024/1024 redo_size FROM sys.v_$log ) r;