Es bastante habitual si se trabaja con bases de datos que a menudo se tenga que realizar alguna tarea de creación o alteración de estructuras, análisis, recompilación, etc. sobre objetos de la base de datos. Para ello se suele crear un script con numerosas sentencias DDL, en las que la mayoría de las veces lo único que cambia es el nombre del objeto a tratar.

Crear sentencia Oracle SQL que genera sentencias Oracle

En estos casos puede ahorrarnos mucho trabajo la utilización del diccionario de la base de datos para construir estas sentencias dinámicamente. Pondremos como ejemplo la creación de un nuevo campo para almacenar la fecha de creación de los registros en todas las tablas de un esquema de una base de datos ORACLE. Para ello utilizaríamos la siguiente sentencia de SQL Oracle:

SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ADD FECHA_CREACION DATE DEFAULT SYSDATE;' 
FROM ALL_TABLES WHERE OWNER ='HR'; 

El resultado de esta sentencia SQL Oracle sería algo como esto, las sentencias SQL que queremos utilizar en realidad:

ALTER TABLE HR.DEPARTMENTS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.EMPLOYEES ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.JOB_HISTORY ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.JOBS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.LOCATIONS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.REGIONS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.COUNTRIES ADD FECHA_CREACION DATE DEFAULT SYSDATE;

Crear script con Oracle SQL para ejecutar sentencias SQL

Ahora sólo restaría guardar estas sentencias en un script y ejecutarlo, o lanzarlas directamente desde la aplicación que utilicemos para interactuar con nuestra base de datos.

Para el que tenga que (o prefiera) trabajar desde un terminal o linea de comandos, la manera de hacer esto mismo con SQLPLUS sería la siguiente:

SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL C:\campo_auditoria.sql
SQL> SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ADD FECHA_CREACION DATE DEFAULT SYSDATE;'
     FROM ALL_TABLES WHERE OWNER ='HR';
SQL> SPOOL OFF;
SQL> SET FEEDBACK ON
SQL> SET HEADING ON 

Y finalmente ejecutar el script generado, aunque es recomendable una revisión previa de las sentencias generadas:

 SQL> @C:\campo_auditoria.sql 

Con este script de Oracle SQL se crea una tabla DIM_TIEMPO y se rellena con los valores comprendidos entre las fechas que se indiquen en las variables FechaDesde y FechaHasta. Puede ser muy útil para la creación de la tabla de tiempo de cualquier Data Warehouse.

Esta es la versión para una base de datos Oracle, con Oracle SQL, que se suma a las que han creado anteriormente il_masacratore y Dario Bernabeu para Microsoft SQL Server y Oracle MySQL en sus respectivos blogs:

Estructura de la Dimensión Tiempo y Script de carga para Ms SQL Server

Estructura de la Dimensión Tiempo y Procedure de carga para MySQL

Como cada uno se adaptará el formato de las fechas al que más le convenga, aprovecho para adjuntar esta tabla de ayuda obtenida de Oradev. Contiene descripciones de la sintaxis que se puede utilizar en las máscaras de formato de fechas de las funciones TO_CHAR y TO_DATE de Oracle:

Para hacer un insert con Oracle SQL desde una tabla de una base de datos TablaBD1 a otra base de datos TablaBD2 a través de un dblink debería haber dos maneras:
 

1- Crear el database link en la base de datos origen BD1 y hacer el insert hacia la tabla de la base de datos destino BD2 a través de este dblink

SQL en la base de datos Oracle BD1:

CREATE [PUBLIC] DATABASE LINK BD1toBD2_dblink
CONNECT TO usuario2
IDENTIFIED BY password2
USING 'BD2';

INSERT INTO TablaBD2@BD1toBD2_dblink
(SELECT * FROM TablaBD1);

* Para simplificar suponemos que las tablas tienen la misma estructura
 

2- Crear el database link en la base de datos destino BD2 y hacer el insert seleccionando los registros de la tabla de la base de datos origen BD1 a través de este dblink:

SQL en la base de datos Oracle BD2:

CREATE [PUBLIC] DATABASE LINK BD2toBD1_dblink
CONNECT TO usuario1
IDENTIFIED BY password1
USING 'BD1';

INSERT INTO TablaBD2
(SELECT * FROM TablaBD1@BD2toBD1_dblink);

Yo hasta ahora he utilizado siempre el SQL de la segunda opción para enlazar bases de datos Oracle, supongo que porque parece más simple utilizar un dblink para seleccionar datos de una tabla de una base de datos remota que para insertarlos remotamente. Pues parece que lo mejor es buscar las cosas simples porque he podido comprobar que la primera opción no funciona, por lo menos a mi.

He probado a hacerlo creando con SQL un dblink entre dos bases de datos Oracle 10g y la inserción no se realizaba. Lo curioso es que la sentencia SQL, en lugar de devolverme un error me devolvía un mensaje de '0 registros insertados', cuando haciendo la SELECT sí que obtenía registros.

Yo he llegado a la conclusión de que la opción de insertar datos directamente en una tabla de una base de datos Oracle remota a través de un database link no funciona, hay que hacer una SELECT desde la base de datos 'destino'. Para simplificar, la @ ha de estar en la tabla de la sentencia SQL de SELECT, no en la de INSERT.

Si alguien tiene una explicación mejor le agradecería que lo comentara, porque el tema me parece bastante curioso.

En Oracle SQL, se pueden optimizar ejecuciones batch que hagan un volcado masivo de datos en una misma tabla usando sentencias de insert o update por registro dentro de un bloque. Se pueden optimizar con el uso de parámetros (si el cliente lo permite) o si usamos odbc con bind variables.

Recordemos los pasos que sigue Oracle para procesar una consulta:

1. Validación Sintáctica
2. Validación Semántica
3. Optimización
4. Generación del QEP (Query Execution Plan)
5. Ejecución del QEP (Query Execution Plan)

Las sentencias SQL pueden recoger los parámetros por valor (where salario>1000) o una vez compilada la sentencia haciendo uso de Bind Variables (where salario>:b1). La ventaja de la segunda opción es que Oracle compila un única vez la sentencia y reutiliza el código compilado para cada uno de los valores para los parámetros. Pero hay que ir con ojo ya que en este segundo caso, Oracle no puede calcular el grado de selectividad de una consulta y, en su lugar, aplica un grado de selectividad por defecto (asociado a cada tipo de operación), lo cual puede dar lugar a decisiones "equivocadas".

Por lo tanto, trabajando por costes es desaconsejable el uso de Bind Variables, salvo que trabajemos con sentencias Oracle SQL que se van a ejecutar repetidas veces y que no ofrezcan muchas dudas en cuanto a los posibles planes de acceso que puede generar.

En casos probados como el siguiente el tiempo de ejecución se reduce hasta en un 90% en una inserción de 100.000 registros...

SQL para Creación de la tabla necesaria para la prueba:

create table PRUEBA
(
 NUM number(22),
 TEXTO varchar(100)
);

SQL de Carga con parámetros por valor:

declare
 v_i number;
begin
 loop
 INSERT INTO PRUEBA VALUES (3, '50'); 
 v_i := v_i + 1;
 exit when v_i > 1000000;
 end loop;
rollback;
end;

SQL de Carga con parámetros usando bind variables

declare
 v_i number;
begin
 loop
 execute immediate 'INSERT INTO PRUEBA VALUES (:x, :y)' using 3, '50'; 
 v_i := v_i + 1;
 exit when v_i > 1000000;
 end loop;
rollback;
end;

Adjunto los resultados de la prueba realizada antes de aplicar cambios en producción (un viernes por la tarde )

Revisamos brevemente en este post la funcionalidad flashback query que aporta el gestor de BBDD de Oracle desde su versión 9i.

Básicamente se trata de un tipo de sql de Oracle que accede a datos que existían en la base de datos en un momento anterior, pero que en el momento en el que se ejecuta la sql pueden no existir o haber sufrido modificaciones. Para ello, Oracle utiliza los datos que quedan disponibles durante un tiempo en el segmento de UNDO. Este segmento, como es sabido, almacena los datos anteriores a una serie de modificaciones. Se utiliza para asegurar la consistencia en la lectura de una consulta previa a la confirmación de las modificaciones (commit) y pueden ser utilizados en una posible recuperación (rollback).

La sentencia de Oracle SQL flashback query nos permite ver datos de la tabla que han sido borrados o modificados. Ejecutando una flashback query accedemos a datos de una foto de datos consistentes en un punto determinado, especificando para ello la hora del sistema o bien el número de cambio del sistema (SCN). La base de datos debe estar configurada para trabajar en  Automatic Undo Management (AUM). Para ello revisar los siguientes parámetros de la base de datos Oracle:

undo_management = auto
undo_tablespace = UNDOTBS001   (tablespace que alberga el segmento de undo)
undo_retention = 3600 (tiempo en segundos que tenemos retenido el dato en el segmento de undo)

Hay que tener en cuenta respecto al parámetro undo_retention que si el tablespace de UNDO no es lo suficientemente grande como para mantener ese tiempo todas las transacciones, el gestor de base de datos las va a sobreescribir. Igualmente, considerar que para poder ejecutar el comando flashback query de Oracle SQL debemos tener permisos sobre el package BDMS_FLASHBACK. Para ello:

 sys> grant execute on dbms_flashback to usuario1;

Veamos un ejemplo. Supongamos que queremos realizar una consulta SQL sobre una tabla que contiene facturas de clientes y queremos acceder a los datos de un cliente que previamente hemos borrado:

sys> select to char(sysdate, ‘dd-mm-yyyy hh24:mi’) fecha_sistema from dual;
fecha_sistema
-------------------------
12-09-2011 12:05

sys> delete from t_facturas where cod_cliente = ‘00125’;
4 registros borrados

sys> commit;

Media hora después ejecutamos:

sys> select to char (sysdate, ‘dd-mm-yyyy hh24:mi’) fecha_sistema from dual
fecha_sistema
-------------------------
12-09-2011 12:35
sys>  exec dbms_flashback.enable_at_time (to_date('12-09-2011 12:05, 'DD-MM-YYYY HH24:MI'));

sys> select to char (sysdate, ‘dd-mm-yyyy hh24:mi’) fecha_sistema from dual
fecha_sistema
-------------------------
12-09-2011 12:35
sys> exec dbms_flashback.enable_at_time (to_date(’12-09-2011 12:05, ‘DD-MM-YYYY HH24:MI’)); 
Procedimiento PL/SQL terminado correctamente.

sys> select * from t_facturas where cod_cliente = ‘00125’;
……
……
……
…… 
4 registros seleccionados.

sys> execute dbms_flasback.disable; 

sys> select count(*) from t_facturas where cod_cliente = ‘00125’; 
count(*) 
------ 
0 

Se puede obtener lo mismo accediendo por el número de cambio SCN:

sys> select dbms_flashback.get_system_change_number from dual; 
GET_SYSTEM_CHANGE_NUMBER 
------------------------ 
1307125 

sys> exec dbms_flashback.enable_at_system_change_number(1307125); 
Procedimiento PL/SQL terminado correctamente

Existe también la posibilidad de emplear la sentencia ‘select ... as of...’: 

sys> select * from t_facturas where cod_cliente = ‘00125’ as of timestamp to_timestamp (’12-09-2011 12:05', ‘DD-MM-YYYY HH24:MI’);
sys> select * from t_facturas where cod_cliente = ‘00125’ as of scn 1307125; 

Hay que tener en cuenta que mientras la sesión está en el modo Flashback Query, solo podemos ejecutar sentencias SELECT. Las sentencias SQL de actualización (insert, delete y update) no están permitidas.

En el trabajo diario con esta opción es útil el uso de tablas temporales para trabajar con los datos recuperados:

sys> create table t_facturas_ant as      (select * from t_facturas       where cod_cliente = ‘00125’       as of timestamp to_timestamp (’12-09-2011 12:05, ‘DD-MM-YYYY HH24:MI’);

Otras opciones Flashback Query

A continuación listamos algunas funcionalidades que aporta Oracle relacionadas con operaciones Flashback Query:

• Flashback Version Query: Acceso al histórico de cambios de una tabla.
• Flashback Transaction Query: Acceso al histórico de cambios de una transacción determinada.
• Flashback Table: Acceso a datos anteriores, pero para una única tabla.
• Flashback Drop: Recuperar una tabla borrada (‘papelera reciclaje’).
• Flashback Database: Permite dejar la BBDD tal y como se encontraba en un tiempo pasado. Similar a restaurar un backup, pero con las limitaciones temporales de los procesos flashback, aunque mucho más rápido que recuperar la copia del backup.  Es necesario tener el modo flashback activado, así como la flash recovery area.

Algunas de estas opciones requieren que el gestor sea 'Enterprise Edition'.

Se trata, como se ha dicho, de una sentencia de Oracle SQL bastante útil, con múltiples opciones de “rebobinado” y que nos puede sacar de más de un apuro.

www.datademy.es

¿Quién no tiene su chuleta de consultillas útiles que se suelen utilizar en el día a día, y en nuestras aventuras y desventuras con la base de datos?

Listado de consultas SQL de Oracle, la mayoría sobre las vistas del diccionario de Oracle, para obtener información sobre objetos de la base de datos propios o a los que tiene permisos de acceso si se trata de un usuario normal, y sobre objetos de cualquier usuario si el usuario es DBA.

La idea es crear un pequeño un repositorio para poder consultar desde cualquier lugar para facilitarnos la vida, o sacarnos de algún que otro apuro.

Consultas para usuarios de Oracle

Consultas que se pueden hacer a nivel de usuario sin privilegios de administrador, suelen ir también sobre el diccionario de datos de Oracle, pero retornan datos de los objetos a los que el usuario tiene acceso

-- Consulta Oracle SQL sobre el Diccionario de datos 
-- Con esta vista se consultan los nombres de las tablas o vistas del diccionario a las que el usuario tiene acceso, útil para saber dónde buscar información sobre objetos a los que el usuario tiene acceso

select * from dictionary where table_name like '%TABLES%'

-- Consulta Oracle SQL que muestra los metadatos de la/s tabla/s especificada/s por nombre
-- (en este caso todas las tablas que lleven la cadena "XXX")

select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'

-- Consulta Oracle SQL que muestra las descripciones de los campos de una tabla especificada 
   (en este caso todas las tablas que lleven la cadena "XXX")

select * from ALL_COL_COMMENTS where upper(table_name) like '%XXX%'

-- 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 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, en este caso "xxx"

SELECT table_owner, table_name from sys.all_synonyms where table_owner like '%xxx%'

-- Consulta Oracle SQL como la anterior, pero de otra forma más efectiva 
   (tablas de las que es propietario un usuario)

SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE '%HR%'

-- 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='NOMBRETABLA'

-- 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='NOMBRETABLA' or segment_name in
(select index_name from user_indexes where table_name='NOMBRETABLA'))

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

select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA-- 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

Consultas para DBAs de Oracle

Consultas sobre vistas de sistema, normalmente requieren permisos de DBA o administrador

-- 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

-- 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

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 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 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 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 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 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 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 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

Libros de Administración Oracle (DBA) y PL/SQL

¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.

Hasta el momento desconozco exactamente como o donde se detalla cada tipo de error en la ejecución de un informe de reporting services de Microsoft SQL Server. He tratado con derivados de falta de permisos, procesados incompletos de cubos pero hasta ahora ningun #Error en una celda por que sí.

El error en cuestión me aparece en la ejecución de un pequeño informe que tira de un origen de datos ODBC contra una base de datos Oracle donde se muestran totales (sumas, no porcentajes) y me ha sorprendido mucho la falta de detalle sobre el error que se produce. Para más dificultad, encima es en una combinación de parámetros concreta (las n ejecuciones anteriores han funcionado) y no en toda la columna sino en una celda. Además arrastra todo subtotal o total en el que se incluya...

Después de mirar el log del servidor de Reporting Services, después de comprobar la consulta en un cliente externo con la misma, tras pensar mal del formato me da por comprobar en el diseñador de informes el origen de datos, me hago una prueba con parámetros al tuntun y bien, pero al poner los valores problemáticos consigo que se al menos aparezca el error:

Pues nada, encontrado esto ya estoy más feliz ya que el error es que ado.net+oracle+odbc no lleva muy bien los números de 38 digitos...

Para esto podemos hacer un workaround que consiste en usar en la consulta la función trunc(número, decimales) de Oracle SQL para que nos trunque el valor en su parte decimal donde nosotros decidamos.

Con la versión 9.7 de DB2 LUW, IBM hace un guiño a todos los DBA's de Oracle, mucho más numerosos en el mercado que los DBA's de DB2.

Para ello, en la versión 9.7 de DB2 LUW ha introducido modos de compatibilidad de Oracle que permiten realizar tareas en DB2 con la facilidad y conocimiento que tienen los DBA de Oracle. Sin embargo, es importante conocer la traslación de terminología entre Oracle y DB2 si tienes la intención de meterte en el mundo IBM DB2.

En este primer artículo sobre equivalencias entre IBM DB2 y Oracle, relaciono una serie de elementos para que esa introducción sea sencilla y se pueda leer la documentación de IBM DB2 fácilmente. Entre ellos, terminología general de estas bases de datos, versiones, utilidades y vistas.

Espero que os sea útil.

Oscar Paredes

IT Manager
Oracle DBA

oscar.paredes@dataprix.com 

www.db2util.es

Supongamos que queremos actualizar en nuestra base de datos ORACLE el campo de costes de la tabla de hechos FAC_TABLE con el coste unitario de nuestra tabla de COSTES.

Con Oracle SQL podemos hacerlo de dos maneras:

• Consulta Lenta, pero si es para pocos datos o para lanzarlo esporádicamente nos puede valer

update FAC_TABLE ft set COSTE_UNITARIO =
   (select distinct COSTE_UNITARIO
    from COSTES ct
    where (ft.id_articulo = ct.id_articulo); 

• La mejor manera es esta, y el rendimimento es óptimo si tiene constraints)

UPDATE (SELECT ft.COSTE_UNITARIO AS old_coste,
               ct.COSTE_UNITARIO AS new_coste 
        FROM FAC_TABLE ft INNER JOIN COSTES ct ON ft.id_articulo = ct.id_articulo)
       ) SET old_coste = new_coste;

Para que esta segunda opción funcione necesitamos tener UNIQUE or PRIMARY KEY constraint en ct.id_articulo.

Si no tienes esta constraint, puedes utilizar el hint /*+BYPASS_UJVC*/ después de la palabra UPDATE (bypass update join view constraint).

El rendimiento aumenta si tenemos la constraint pero aún sin ella debe correr mucho más que la primera opción.

Espero que os ayude.

Héctor Minguet.

Como las cargas de un Data warehouse se realizan de manera periódica, y además es habitual la creación de tablas agregadas para mejorar la eficiencia y tiempo de respuesta de nuestros informes, un recurso de optimización física que puede aportar grandes mejoras es la utilización de vistas materializadas.

Qué es una vista materializada 

La vista materializada no es más que una vista, definida con una sentencia SQL de Oracle, de la que además de almacenar su definición, se almacenan los datos que retorna, realizando una carga inicial y después cada cierto tiempo un refresco de los mismos.

Así, si tenemos un Datawarehouse que se actualiza diariamente, podríamos utilizar vistas materializadas para ir actualizando tablas intermedias que alimenten nuestros esquemas de DWH, o directamente para implementar tablas agregadas que se refrescarán a partir de nuestras tablas base. 

La creación de este tipo de vistas con Oracle SQL no es tan compleja como puede parecer, lo más importante es tener claro cada cuánto tiempo queremos actualizar la información de las vistas, y qué método de refresco utilizar.

También tendremos que asegurarnos de que nuestra licencia de base de datos nos permite utilizarlas (ha de ser una versión Enterprise).

Sintaxis básica de Oracle SQL para la creación de una vista materializada

CREATE MATERIALIZED VIEW mi_vista_materializada
 [TABLESPACE mi_tablespace]
 [BUILD {IMMEDIATE | DEFERRED}] 
 [REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicio] NEXT fecha_intervalo } | 
          {COMPLETE | FAST | FORCE} ] 
 [{ENABLE|DISABLE} QUERY REWRITE] AS 
     SELECT t1.campo1, t2.campo2 
     FROM mi_tabla1 t1 , mi_tabla2 t2 
     WHERE t1.campo_fk = t2.campo_pk AND …

Comentarios sobre las diferentes opciones de la sentencia Oracle SQL de creación de la vista:

• Carga de datos en la vista

BUILD IMMEDIATE:
  Los datos de la vista se cargan en el mismo momento de la creación

BUILD DEFERRED:
  Sólo se crea la definición, los datos se cargarán más adelante. Para realizar esta carga se puede utilizar la función REFRESH del package DBMS_MVIEW:
      begin
         dbms_mview.refresh('mi_vista_materializada');
      end; 

• Método y temporalidad del refresco de los datos

 Cada cuánto tiempo se refrescarán:

REFRESH ON COMMIT:
  Cada vez que se haga un commit en los objetos origin definidos en la select 

REFRESH ON DEMAND:
  Como con la opción DEFERRED del BUILD, se utilizarán los procedures REFRESH, REFRESH_ALL_MVIEWS o REFRESH_DEPENDENT del package DBMS_MVIEW 

REFRESH [START WITH fecha_inicio] NEXT fecha_intervalo:
  START WITH indica la fecha del primer refresco (fecha_inicio suele ser un SYSDATE)
                  NEXT indica cada cuánto tiempo se actualizará (fecha_intervalo podría ser SYSDATE +1 para realizar el refresco una vez al día)

• De qué manera se refrescarán

REFRESH COMPLETE:
El refresco se hará de todos los datos de la vista materializada, la recreará completamente cada vez que se lance el refresco

REFRESH FAST:
El refresco será incremental, es la opción más recomendable, lo de fast ya da una idea del porqué.
Este tipo de refresco tiene bastantes restricciones según el tipo de vista que se esté creando.
Se pueden consultar en General Restrictions on Fast Refresh de la documentación oficial de Oracle

Una de las cosas importantes a tener en cuenta es que para poder utilizar este método casi siempre es necesario haber creado antes un LOG de la Vista materializada, indicando los campos clave en los que se basará el mantenimiento de la vista.

Se utiliza la instrucción de Oracle SQL "CREATE MATERIALIZED VIEW LOG ON":

   CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen      
   WITH PRIMARY KEY      
   INCLUDING NEW VALUES; 

REFRESH FORCE:
Con esta opción se indica que si es posible se utilice el metodo FAST, y si no el COMPLETE. 

Para saber si una vista materializada puede utilizar el método FAST, el package DBMS_MVIEW proporciona el procedure EXPLAIN_MVIEW 

• Activación de la reescritura de consultas para optimizar el Data warehouse

ENABLE QUERY REWRITE:
Se permite a la base de datos la reescritura de consultas

DISABLE QUERY REWRITE:
Se desactiva la reescritura de consultas

La opción QUERY REWRITE es la que más vamos a utilizar si queremos las vistas materializadas para optimizar nuestro Data warehouse.
Esta opción permite crear tablas agregadas en forma de vistas materializadas, y que cuando se lance una SELECT la base de datos pueda reescribirla para consultar la tabla o vista que vaya a devolver los datos solicitados en menos tiempo, todo de manera totalmente transparente al usuario

Lo único que hay que hacer es crear las tablas agregadas como vistas materializadas con QUERY REWRITE habilitado.

Ejemplos de vistas materializadas de Oracle

Son muchas combinaciones, pero la sentencia final no es tan compleja.

Primer paso de la ETL de un Data warehouse

Si quisiéramos crear con SQL de Oracle una vista materializada de una tabla que se refresque un día a la semana, y de manera incremental haríamos lo siguiente: 

CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen
WITH PRIMARY KEY INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mi_vista_materializada
REFRESH FAST NEXT SYSDATE + 7 AS
   SELECT campo1, campo2, campo8
   FROM mi_tabla_origen
   WHERE campo2 > 5000;

Esta vista podría servirnos para alimentar la carga de un Data Mart que se realizara semanalmente. Podríamos programarla para que se refrescara justo antes del inicio del proceso de carga, o como primer paso en la ETL, y ya tendríamos los datos necesarios actualizados, e independientes del origen de datos (no tendríamos que molestar más al operacional). Otra ventaja a tener en cuenta es que si hay algún problema con el acceso a los datos de origen, si no los hemos eliminado, en la vista materializada aún tendremos los datos del último refresco, con lo que aunque el refresco fallara no nos encontraríamos un error que truncara la carga de nuestro Data Warehouse, o una tabla vacía.

Por supuesto, en las condiciones del WHERE de la sentencia SQL de creación podríamos seleccionar sólo los registros necesarios, sólo los del último mes, etc.

Tablas agregadas para optimizar el Data Warehouse

Otro ejemplo importante sería la utilización de vistas materializadas para la creación de tablas agregadas. 

-- Oracle SQL para crear agregadas con materilized views
CREATE MATERIALIZED VIEW ventas_agregadas_mv
BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE AS
   SELECT id_producto, sum(importe) total_ventas
   FROM ventas GROUP BY id_producto;

Con esta sencilla sentencia de Oracle SQL se crearía una tabla agregada de total de ventas por producto de una supuesta tabla de ventas que seria la tabla de hechos.

A nivel de sesión también habría que asegurarse de que la opción QUERY_REWRITE estuviera activada. Por si acaso, desde SQL Plus, se puede habilitar con la sentencia SQL:

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

Si ahora dentro de esta sesión se ejecuta la sentencia SQL

SQL> SELECT sum(importe)
     FROM ventas;

la base de datos preparará el plan de ejecución teniendo en cuenta la vista materializada creada e internamente realizará la selección sobre la vista ventas_agregadas_mv.

Una manera sencilla de comprobarlo, aparte de examinar el plan de ejecución, o de comparar tiempos antes y después de la creación de la vista, o desactivando el QUERY_REWRITE, es comprobar que esta query SQL devuelve resultados en el mismo tiempo que la query

SELECT sum(importe)
FROM ventas_agregadas_mv;

Para consultar más detalles, o la sintaxis SQL completa de la creación de vistas materializadas Oracle, el capítulo Create Materialized View del manual de referencia SQL de Oracle es un buen recurso.

Con Oracle Enterprise Manager (OEM), o con la consola web de la base de datos también se pueden crear las vistas materializadas de una manera más asistida, pero igualmente es importante tener claros los conceptos antes de hacerlo.

Libros de Administración Oracle (DBA) y PL/SQL

¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.

Los libros que ves a continuación son una selección de los que a mi me parecen más interesantes para aprender administración y desarrollo PL/SQL, teniendo en cuenta precio y temática, espero que te puedan ser de utilidad:

• eBooks de Oracle gratuítos para la versión Kindle, o muy baratos (menos de 4€):