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 execucions batch que facin un bolcat massiu de dades en una mateixa taula utilitzant un insert o update per registre dins d'un bloc pel motiu x es poden optimitzar amb l'ús de paràmetres (si el client ho permet) o si fem servir odbc amb bind variables.

Recordem els passos que segueix Oracle per processar una consulta:

1) Validació Sintáctica
2) Validació Semàntica
3) Optimització
4) Generació del QEP (Query Execution Pla)
5) Execució del QEP (Query Execution Pla)

Les sentències poden recollir els paràmetres per valor (where salari> 1000) o un cop compilat la sentència fent ús de Bind Variables (where salari>: b1). L'avantatge de la segona opció és que Oracle compilant un única vegada la sentència i reutilitza el codi compilat per a cada un dels valors per als paràmetres. Però cal anar amb ull ja que en aquest segon cas, Oracle no pot calcular el grau de selectivitat d'una consulta i, en el seu lloc, aplica un grau de selectivitat per defecte (associat a cada tipus d'operació), cosa que pot donar lloc a decisions "equivocades". Per tant, treballant per costos és desaconsellable l'ús de Bind Variables, llevat que treballem amb sentències que es van a executar repetides vegades i que no ofereixin molts dubtes pel que fa als possibles plans d'accés que pot generar.

En casos provats com aquesta el temps d'execució es redueix fins a un 90% en una inserció de 100.000 registres ...

Creació de la taula necessària per a la prova:
create table PROVA
( 
NUM number (22),
TEXT varchar (100)
);

Càrrega amb paràmetres per valor:
declari
v_i number;
begin
loop
INSERT INTO STA.STA_PRUEBA VALUES (3, '50 ');
v_i := i + 1;
exit when i> 1000000;
end loop;
rollback;
end;

Càrrega amb paràmetres utilitzant bind variables

declari 
v_i number;
begin
loop
execute immediate 'INSERT INTO STA.STA_PRUEBA VALUES (: x,: i)' using 3, '50 '; 
v_i: = i + 1; 
exit when i> 1000000; 
end loop;
rollback;
end;

Adjunt els resultats de la prova realitzada abans d'aplicar canvis en producció (un divendres a la tarda )

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 no té la seva xuleta de consultes útils que se solen utilitzar en el dia a dia, i en les nostres aventures i desaventures amb la base de dades?
Incloc en aquest primer post un llistat de consultes, la majoria sobre les vistes del diccionari d'Oracle, i extretes de la Cibermanuales.com, i animo a tot el que vulgui afegir-ne altres que consideri d'utilitat a respondre el post publicant les seves, a veure si entre tots creem un reposador que puguem consultar des de qualsevol lloc per facilitar-nos la vida, o treure'ns d'algun que un altre conflicte.

• Consulta Oracle SQL sobre la vista que mostra l'estat de la base de dades:

select * from v$instance

• Consulta Oracle SQL que mostra si la base de dades està oberta

select estatus from v$instance

• Consulta Oracle SQL sobre la vista que mostra els paràmetres generals d'Oracle

select * from v$system_parameter

• Consulta Oracle SQL per conèixer la Versió d'Oracle

select value from v$system_parameter where name = 'compatible'

• Consulta Oracle SQL per conèixer la Ubicació i nom del fitxer spfile

select value from v$system_parameter where name = 'spfile'

• Consulta Oracle SQL per conèixer la Ubicació i nombre de fitxers de control

select value from v$system_parameter where name = 'control_files'

• Consulta Oracle SQL per conèixer el Nom de la base de dades

select value from v$system_parameter where name = 'db_name'

• Consulta Oracle SQL sobre la vista que mostra les connexions actuals a Oracle Para visualitzar-la és necessari entrar amb privilegis d'administrador

select osuser, username, machine, program
from v$session
order by osuser

• Consulta Oracle SQL que mostra el nombre de connexions actuals a Oracle agrupat per aplicació que realitza la connexió

select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc

• Consulta Oracle SQL que mostra els usuaris d'Oracle connectats i el nombre de sessions per usuari

select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc

Propietaris d'objectes i nombre d'objectes per propietari
select owner, count(owner) Numero
from dba_objects
group by owner
order by Numero desc

• Consulta Oracle SQL sobre el Diccionari de dades (inclou totes les vistes i taules|posts de la Base de Dades)

select * from dictionary

• Consulta Oracle SQL que mostra les dades d'una taula|post especificada (en aquest cas totes les taules|posts que portin la cadena "XXX"

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

• Consulta Oracle SQL per conèixer les taules|posts propietat de l'usuari actual

select * from user_tables

• Consulta Oracle SQL per conèixer tots els objectes propietat de l'usuari connectat a Oracle

select * from user_catalog

• Consulta Oracle SQL per al DBA d'Oracle que mostra els tablespaces, l'espai utilitzat, l'espai lliure i els fitxers de dades dels mateixos:

Select t.tablespace_name "Tablespace", t.status "Estat",
ROUND(MAX(d.bytes)/1024/1024,2) " MB Mida",
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 Lliures",
t.pct_increase "% increment",
SUBSTR(d.file_name,1,80) "Fitxer de dades"
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 per conèixer els productes Oracle instal·lats i la versió:

select * from product_component_version

• Consulta Oracle SQL per conèixer els rols i privilegis per rols:

select * from role_sys_privs

• Consulta Oracle SQL per conèixer les regles d'integritat i columna que afecten:

select constraint_name, column_name from sys.all_cons_columns.. Consulta Oracle SQL per conèixer les taules|posts de les quals és propietari un usuari, en aquest cas "xxx":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'xxx'

• Consulta Oracle SQL com l'anterior, però d'una altra forma més efectiva (taules|posts de les quals és propietari un usuari):

SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'HR'
Paràmetres d'Oracle, valor actual i la seva descripció:
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 mostra els usuaris d'Oracle i dades seves (data de creació, estat, aneu, nom, tablespace temporal...):

Select * FROM dba_users

• Consulta Oracle SQL per conèixer tablespaces i propietaris dels mateixos:

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

Últimes consultes SQL executades a Oracle i usuari que les va executar:
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 per conèixer tots els tablespaces:

select * from V$TABLESPACE.. Consulta Oracle SQL per conèixer la memòria Share_Pool lliure i 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'

Cursors oberts per usuari

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 per conèixer els encerts de la caché (no hauria de superar l'1 per cent)

select sum(pins) Execucions, 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');

Sentències SQL completes executades amb un text determinat 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 CAMP LIKE%'
ORDER BY c.sid, d.piece

Una sentència SQL concreta (filtrat per 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 per conèixer la mida ocupada per la base de dades

select sum(BYTES)/1024/1024MB from DBA_EXTENTS

• Consulta Oracle SQL per conèixer la mida dels fitxers de dades de la base de dades

select sum(bytes)/1024/1024MB from dba_data_files

• Consulta Oracle SQL per conèixer la mida ocupada per una taula|post concreta sense incloure els índexs de la mateixa

select sum(bytes)/1024/1024MB from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'

• Consulta Oracle SQL per conèixer la mida ocupada per una taula|post concreta incloent els índexs de la mateixa

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 per conèixer la mida ocupada per una columna d'una taula|post

select sum(vsize('NOMBRECOLUMNA'))/1024/1024MB from NOMBRETABLA

.. Consulta Oracle SQL per conèixer l'espai ocupat per usuari

SELECT owner

, SUM(BYTES)/1024/1024

MB FROM

DBA_EXTENTS

group by owner

.. Consulta Oracle SQL per conèixer l'espai ocupat pels diferents segments (

taules|posts

, índexs,

undo

,

rollback

,

clúster|grup de sectors

...)

SELECT

SEGMENT_TYPE, SUM(BYTES)/1024/1024

MB FROM

DBA_EXTENTS

group by

SEGMENT_TYPE

.. Consulta Oracle SQL per obtenir totes les funcions d'Oracle:

NVL

,

ABS

,

LTRIM

...

SELECT distinct

object_name

FROM

all_arguments

WHERE

package_name = 'ESTÀNDARD'

order by

object_name

.. Consulta Oracle SQL per conèixer l'espai ocupat per tots els objectes de la base de dades, mostra els objectes que més ocupen primer

SELECT

SEGMENT_NAME, SUM(BYTES)/1024/1024

MB FROM

DBA_EXTENTS

group by

SEGMENT_NAME

order by

2

desc
PR

:

wait

...

I:

wait

...

L:

wait

...

LD

:

wait

...

I:

wait

...

wait

...

SD

:

wait

...

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€):