Oracle Notepad

Flash Recovery Area

2010-09-30T04:44:00.000-07:00

A partir de Oracle 10g R1 aparece un recurso muy interesante en la base de datos, destinado a guardar y organizar fácilmente todos aquellos archivos que hacen a la recuperación de la base de datos: Flash Recover Area (FRA). En ella se almacenan backups, copias de datafiles, controlfiles, archivelogs. Esta forma de manejar los archivos hace la tarea del DBA más eficiente, ya que permite recuperar una falla más rapidamente y realiza algunas funciones de limpieza automáticamente.
Como esta opción no viene activada por defecto, es necesario alterar un par de parámetros con ALTER SYSTEM para que entre en efecto. Nada complicado. No necesitaremos reiniciar la base, a menos que no la tengamos en ARCHIVELOG mode. Este último modo es requerido.

1) Colocar la base de datos en modo ARCHIVELOG (en caso que no lo esté)

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2) Crear un directorio destino para los archivos via sistema operativo. En nuestro caso será: /u04/fra
En caso de estar usando ASM es exactamente igual, crearemos un grupo llamado por ejemplo +FRA.

3) Configurar los siguientes parámetros con usuario sys:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/fra' SCOPE=BOTH SID='*';

El tamaño (10G) es un ejemplo y a criterio de cada uno, dependiendo del uso que le vayamos a dar. Se recomienda reservar al menos 2 veces el tamaño actual de la base de datos.
En próximos articulos veremos ejemplos para sacar provecho de nuestra flash recovery área.

Volumenes Netbackup vuelven a scratch

2010-08-27T05:33:00.001-07:00

Esta es de Veritas Netbackup 6.5, y me consta que para versiones 5 también funciona.

Solo para enmarcar el problema, tengo configurado un robot de cintas TLD para backup, que administra el respaldo diario de una base de datos Oracle y algunos file systems. El tiempo de retención de los respaldos está definido en 1 mes.

Ocurrió que algunos volúmenes del pool 'Oracle' volvían al estado indefinido 'scratch' luego que expiraba el tiempo de retención, y debía reasignarlos cada vez que sucedía, volviéndolos al pool original con la Consola de Administración de Netbackup.

Luego de buscar un poco e intentar alterando archivos de configuración (sin suerte), encontré en un foro de Symantec el siguiente comando online que solucionó mi problema:

(con usuario root)

nbemmcmd -changesetting -return_unassigned_media_to_scratch_pool no -machinename nombre_del_host

NBEMMCMD, Version:6.5
Command completed successfully.

nombre_del_host es la máquina donde el Netbackup está instalado.

Recuperando dispositivos failed en Solaris

2010-07-22T13:21:00.001-07:00

Esta semana tuve que aprender a la fuerza algunas pisadas avanzadas con Solaris, para intentar recuperar una unidad de cinta SCSI que no era reconocida. Tanto esfuerzo y sufrimiento merece un post que espero ayude a alguien algún dia.

La unidad en cuestión aparece entre los dispositivos como failed:

(como usuario root en Solaris 10)

cfgadm -al
Ap_Id                          Type         Receptacle   Occupant     Condition
N0.IB6                         PCI-X_I/O_B  connected    configured   ok
N0.IB6::pci0                   io           connected    configured   ok
N0.IB6::pci1                   io           connected    configured   ok
N0.IB6::pci2                   io           connected    configured   ok
N0.IB6::pci3                   io           connected    configured   ok
N0.SB0                         CPU_V3       connected    configured   ok
N0.SB0::cpu0                   cpu          connected    configured   ok
....
(varias entradas)
...
c4                             fc-private   connected    configured   unknown
c4::500110a0008c4980           tape         connected    configured   unknown
c5                             fc-private   connected    configured   unknown
c5::202800a0b82ae608           disk         connected    configured   unknown
c6                             fc-private   connected    configured   unknown
c6::500110a0008c497a           med-changer  connected    configured   failed

De las dos unidades de backup (c4 y c6), la última aparece como failed, y en vez del tipo 'tape' se muestra 'med-changer'.

En varias fuentes se recomienda desconfigurar el dispositivo para volverlo a configurar, pero el comando no es reconocido:

cfgadm -f -c unconfigure c6::500110a0008c497a
cfgadm: Configuration operation not supported

Cuando intentamos ver la configuración del drive, obtenemos un error de comunicación:

luxadm display 500110a0008c497a
 Error: SCSI failure. - 500110a0008c497a.

El problema responde a un desentendimiento entre el SO y el dispositivo. El hardware ha quedado en un estado inconsistente pero el SO no fue actualizado en tal sentido. Es necesario resetear el link que comunica con el dispositivo. Tener precaución con usar el siguiente comando, asegurarse de informar el id correcto!

El comando mágico es:

luxadm -e forcelip /dev/cfg c6

Verificar con el comando cfgadm -al que el dispositivo ya no tiene el estado 'failed'. Probar el comando luxadm display como se muestra anteriormente, debería mostrar la configuración del dispositivo correctamente.

Si esto no resuelve, los caminos alternativos son dos:
1) Sacar el cable de datos que llega al drive y reconectarlo luego de 2 minutos, verificando el listado de dispositivos.
2) Reiniciar el servidor (obviamente la opción menos deseada en un ambiente en producción).

Keep alive en PL/SQL Developer

2010-06-24T09:37:00.001-07:00

La mayoría de los clientes telnet y ftp tienen una función que nos permite "refrescar" nuestra conexión con un servidor y evitar que la sesión sea expirada. Comúnmente se encuentra como 'keep alive', y básicamente consiste en el envío de paquetes nulos para reiniciar el cronómetro de timeout.

Son pocas las ocasiones en las cuales he necesitado de un keep alive para PL/SQL Developer, pero me ha ocurrido y para mi desgracia no he encontrado ninguna opción del estilo en la herramienta. Eso dificulta enormemente correr un debug por ejemplo, donde necesitamos avanzar paso a paso y ocasionalmente podemos demorar más que el tiempo de timeout.

Comentario aparte, una de las cosas que me molestan de PL/SQL Developer es el tiempo que queda trabado cuando se ha desconectado, a veces más de 1 minuto! Pero hay que reconocer que es una herramienta excelente cuando podemos trabajar normalmente.

Como PL/SQL Developer no permite más de 2 scripts ejecutando al mismo tiempo, debemos descartar la idea de crear un simple script con un loop y un SELECT para refrescar la conexión. Eso no nos permitiría por ejemplo ejecutar un debug sobre nuestra aplicación.

Workaround
Aunque ignoro si Allaround Automations planea en algún momento incluir algo de ese tipo (tengo la versión 8.0 y por ahora nada), hay una forma de sortear el problema y mantener la conexión activa, permitiéndonos trabajar normalmente. La opción es 'Sessions...' bajo el menú Tools.

Dentro de Sessions, hay un switch para activar el auto-refresh, como se ve en la imagen a la izquierda.

Si presionamos botón derecho del mouse sobre ese botón, podemos definir el intervalo de refresh entre 1 y 60 segundos. Un valor de 30 debería ser suficiente la gran mayoría de los casos.

Me tardé un poco en descubrirlo pero espero que ayude a alguien.

Una de funciones analíticas

2010-05-27T07:31:00.000-07:00

En esta oportunidad veremos una técnica interesante en SQL para relacionar valores de 2 filas consecutivas, para todos los registros de una tabla. El criterio de 'consecutividad' puede estar dado por alguna de las columnas de la tabla, como el id, la fecha de creación del registro, etc.

En el ejemplo a seguir, tenemos un sistema de registro de tickets, con una tabla histórica que registra el momento en que un ticket cambió de estado.
Lo que queremos hacer es un reporte con el tiempo total en cada uno de esos estados, con un único SQL.

Esta no es una consulta trivial, ya que cada registro contiene únicamente una fecha, por tanto tendremos que asociar al menos dos de ellos para obtener un intervalo de tiempo.

Para complicar un poco, lo resolveremos primero con MySQL, que tiene bastante menos potencial que Oracle para este tipo de consultas. De hecho este ejercicio surgió a partir de la necesidad de un colega trabajando con ese motor.

Comenzamos creando la tabla:

CREATE TABLE status_history
(id        INTEGER PRIMARY KEY,
ticket_id INTEGER,
estado    VARCHAR(1),
fecha     DATETIME);

Incluyamos nuestro primer ticket, con algunos cambios de estado entre A, B y C. Los estados tienen un orden cronológico y es posible volver al mismo repetidas veces.

INSERT INTO status_history VALUES (1, 1, 'A', '2010-01-01 14:00:00');
INSERT INTO status_history VALUES (2, 1, 'B', '2010-01-03 18:30:00');
INSERT INTO status_history VALUES (3, 1, 'C', '2010-01-07 10:00:00');
INSERT INTO status_history VALUES (4, 1, 'A', '2010-01-11 12:10:00');
INSERT INTO status_history VALUES (5, 1, 'C', '2010-01-14 15:00:00');

Como se puede ver, el ticket 1 comenzó en el estado A, luego pasó al B, al C, volvió a A y finalizó en C. Todos esos cambios registraron únicamente la fecha de inserción.

Observando los datos de la tabla, deducimos que la diferencia de tiempo entre el segundo registro 'B' y el primero 'A' es tiempo transcurrido en estado 'A' (en el diagrama representado como tA1). Lo mismo para el último intervalo tA2. La suma de tA1 y tA2 representará el tiempo total que el ticket estuvo en estado 'A'.

La estrategia será usar un auto-join y relacionar los registros n y n+1. Para eso necesitamos tener un campo secuencial y sin huecos para que la igualdad funcione.
Veremos primero el caso simple donde cada registro tiene un id consecutivo, y luego nos enfocaremos en resolver el problema de los huecos.

CASO 1 - IDs continuos (1,2,3,4,5....)

En este caso, sabemos que cada id está a una distancia 1 del id siguiente (vale también para otras diferencias), por tanto haremos el join usando la condición id=id+1 para obtener las fechas y la diferencia entre ellas.

SELECT s1.estado,
      TIME_TO_SEC(timediff(s2.fecha,s1.fecha))/60/60 AS total_horas
FROM
  status_history s1, status_history s2
WHERE s1.id+1 = s2.id
 AND s1.ticket_id=1
 AND s2.ticket_id=1

Para manipular la diferencia entre dos fechas, usamos primero TIMEDIFF que me retorna un tipo TIME (HH:MI:SS) y luego TIME_TO_SEC para obtener la cantidad total de segundos.

Obtenemos las siguientes tuplas:

ESTADO   TOTAL_HORAS
======   ============
'A'      52.50000000
'B'      87.50000000
'C'      98.16666667
'A'      74.83333333

Cabe observar que aparecen dos instancias del estado A, que en el diagrama anterior equivalen a tA1 y tA2. Para obtener la suma total, agrupamos por estado y utilizamos la función SUM:

SELECT s1.estado,
      SUM(TIME_TO_SEC(timediff(s2.fecha,s1.fecha)))/60/60 AS total_horas
FROM
  status_history s1, status_history s2
WHERE s1.id+1 = s2.id
 AND s1.ticket_id=1
 AND s2.ticket_id=1
GROUP BY estado

Finalmente el resultado deseado:

ESTADO   TOTAL_HORAS
======   ============
'A'      127.33333333
'B'      87.50000000
'C'      98.16666667

CASO 2 - IDs con huecos (1,2,4,5,9....)

En el caso anterior, podiamos usar la condicion id=id+1, ya que estabamos seguros que siempre iba a haber una correspondencia (con excepción del último registro). En el caso de existir huecos, el id ya no nos sirve; necesitamos otro campo para ese fin. En Oracle, esto sería muy fácil con la columna virtual rownum, pero en MySQL no tenemos ese recurso.
Hay sin embargo, una forma de simular rownum, y es usando una variable declarada en SQL, inicializada en un select interior. La sintáxis no queda muy amigable pero sirve al propósito de este problema, que es tener un campo secuencial sin huecos.

Columna virtual ROWNUM con MySQL:

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, <tabla o consulta> t

Ahora si, podemos sustituir las tablas por este código y usar rownum como condición.

Para probarlo con nuestro ejemplo, agregaremos un nuevo cambio de estado, con id=8:

INSERT INTO status_history VALUES (8, 1, 'D', '2010-01-16 11:00:00');

La secuencia de ids ahora es 1, 2, 3, 4, 5, 8

La consulta queda así:

SELECT t1.estado,
      SUM(TIME_TO_SEC(timediff(t2.fecha,t1.fecha)))/60/60 AS total_horas
FROM
  (SELECT @rownum1:=@rownum1+1 rownum, s1.*
   FROM (SELECT @rownum1:=0) r, status_history s1
   WHERE s1.ticket_id=1
   ORDER BY s1.id) t1,
  (SELECT @rownum2:=@rownum2+1 rownum, s2.*
   FROM (SELECT @rownum2:=0) r, status_history s2
   WHERE s2.ticket_id=1   
   ORDER BY s2.id) t2
WHERE t1.rownum+1 = t2.rownum
GROUP BY t1.estado

Observar que cada subconsulta utiliza su propia variable @rownum, ya que si usarámos la misma, se incrementaría incorrectamente.

El resultado es:

ESTADO   TOTAL_HORAS
======   ============
'A'      127.33333333
'B'      87.50000000
'C'      142.16666667

Solamente el tiempo de 'C' se vio incrementado con el pasaje de 'C' a 'D'. El estado 'D' no tiene tiempo asociado por tratarse del estado terminal.

La solución Oracle con Funciones Analíticas

Como parte final, voy a mostrar cómo el poder de las funciones analíticas de Oracle nos permiten resolver este tipo de problemas con elegancia y sencillez, sin preocuparnos con auto-joins, rownums ni huecos.
La función ideal en este caso es LEAD. Esta función nos retorna el siguiente registro basado en algun criterio de ordenación, que puede ser una columna.

Todo se resume a usar lo siguiente:

LEAD(fecha, 1, NULL) OVER(ORDER BY id)

El primer parámetro es la columna que voy a retornar, en mi caso, fecha.
El segundo es el offset que quiero recuperar, es decir cuantas filas 'adelante'.
El tercer parámetro es el valor que quiero mostrar si no hay siguiente registro.
Por último, la columna que voy a usar como criterio de ordenación.

La consulta principal que me da tiempos por estado (no agrupados) queda así:

SELECT t1.estado,
      (LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 AS total_horas
FROM   status_history t1
ORDER BY id

Estoy restando la fecha del registro siguiente (recuperado por LEAD) al registro actual. Ver que tampoco necesito funciones de fechas, basta con restarlas y multiplicar por 24.

Solo resta agrupar los estados y sumar los totales en una query exterior:

SELECT estado,
      SUM(total_horas) total_horas
FROM   (SELECT t1.estado,
               (LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 total_horas
        FROM   status_history t1
        ORDER  BY id)
WHERE total_horas IS NOT NULL
GROUP  BY estado
ORDER BY estado;

El resultado es el mismo, lo pueden comprobar si se animan a hacer la prueba.
Tengo que agregar la condición total_horas IS NOT NULL, ya que el uso de LEAD no impide que se muestre el estado D con tiempo NULL (recuerdan el tercer parámetro de LEAD?), que en este caso no nos interesa.

Como comentario, la función LAG nos retorna el registro anterior usando la misma sintáxis que LEAD.

Conclusiones

Las funciones analíticas son útiles y necesarias en muchos casos. Además de ser más eficientes, nos permiten resolver rápidamente el problema de agrupar y buscar relaciones entre filas de una misma consulta.

Bases de datos como MySQL no poseen estos recursos, y nos obligan a resolver el problema con mucho más esfuerzo. Esperemos que con la nueva adquisición, Oracle piense en incorporarlas, para beneficio de quienes trabajan con SQL :)

Mientras tanto, en el caso de MySQL lo importante es determinar cuál es la condición que define la consecutividad entre dos registros, teniendo cuidado si hay huecos. En el caso de Oracle, simplemente es encontrar una relación de precedencia entre dos registros.

Ver también:
Documentación de Funciones Analíticas en Oracle 10g

Listar parámetros indocumentados en Oracle

2010-04-13T12:04:00.001-07:00

Los parámetros indocumentados son controles especiales que modifican el comportamiento de la base de datos, y pueden generar problemas inesperados si no se usan adecuadamente. Deben ser utilizados bajo recomendación específica del soporte. Se identifican fácilmente ya que comienzan típicamente con "_".

El comando de SQL*Plus show parameters lista los valores estandar de la base de datos, y también de aquellos parámetros indocumentados que hemos alterado explícitamente.

Hay veces que queremos experimentar con algún parámetro indocumentado (no en producción obviamente!), pero necesitamos conocer el valor por defecto que está actualmente en uso. Aquí va una consulta para listarlos, con el valor actual para la sesión y para la instancia.

set linesize 131
set pages 9999
col parameter for A30
col description for A40
col "Session Value" for A15
col "Instance Value" for A15

SELECT
a.ksppinm  "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'
/

Para volver al valor estándar del parámetro, bastará eliminarlo del archivo de parámetros de la base, si lo hemos incluído en el mismo.

Expresiones regulares en SQL con 9i

2010-03-04T08:42:00.001-08:00

Recibi un comentario de Laura con el siguiente problema:

"Estoy empezando a trabajar con oracle 9.2 y tengo que cambiar una sentencia que esta en mysql con expresiones regulares a oracle. La sentencia que tengo que pasar a oracle es la siguiente:

SELECT * FROM prueba WHERE (content REGEXP ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes

¿Alguien sabe como puedo pasar a oracle esta sentecia?"

A diferencia de sus sucesores, Oracle 9i tiene un limitado manejo de expresiones regulares, el cual está mas enfocado hacia PL/SQL y no tanto a SQL.

El paquete para usar expresiones regulares se llama owa_pattern. Basta hacer un desc owa_pattern en la consola para saber si tenemos acceso a el, en caso contrario solicitarlo al administrador.

La función para buscar ocurrencias en expresiones regulares dentro del paquete es:
match([cadena_entrada],[expresion_regular])
Retorna un valor booleano true o false. Y aquí está el dilema, en SQL no existe el tipo booleano como tal, por tanto el motor no 'entiende' que es lo que la función está retornando.

SQL> select owa_pattern.match('ejemplo','ej*o') from dual;

ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

La solución es crear una función traductora en PL/SQL para convertir el booleano retornado por match a un tipo válido en SQL, como por ejemplo un numérico. Este wrapper recibirá los mismos parámetros que la función original.

create or replace function sql_match (exp varchar2, regexp varchar2) return number as
  begin
    if (owa_pattern.match(exp, regexp)) then
      return 1;
    else
      return 0;
    end if;
  end;

La ponemos en práctica:

SQL> select sql_match('ejemplo','ej.*o') res from dual;

RES
----------
1

Ahora esta función es perfectamente compatible con SQL, y podemos usarla para evaluar el valor de una columna:

Ejemplo: nombres de empleados que comienzan con 'A'

SQL> select ename, sql_match(ename,'^A.*') res from scott.emp;

ENAME             RES
---------- ----------
SMITH               0
ALLEN               1
WARD                0
JONES               0
MARTIN              0
BLAKE               0
CLARK               0
SCOTT               0
KING                0
TURNER              0
ADAMS               1
JAMES               0
FORD                0
MILLER              0

14 rows selected

Ahora si, volviendo al problema original planteado por nuestra amiga, la traducción de MySQL a Oracle 9i queda:

SELECT * FROM prueba WHERE (sql_match(content, ?)=1) AND (codigo = ?) AND (nombre = ?) GROUP BY mes

Como dije anteriormente, en versiones a partir de 10g, contamos con expresiones regulares prontas para SQL, como la función REGEXP_LIKE. El ejemplo aplicado a este caso quedaría de esta forma:

SELECT * FROM prueba WHERE regexp_like(content, ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes

Critical Patch Update Febrero 2010

2010-02-10T06:14:00.000-08:00

Oracle lanzó el último 4 de febrero una actualización de seguridad para sus servidores Weblogic, para las siguientes versiones:

• Oracle WebLogic Server 11gR1 releases (10.3.1 and 10.3.2)
• Oracle WebLogic Server 10gR3 release (10.3.0)
• Oracle WebLogic Server 10.0 through MP2
• Oracle WebLogic Server 9.0, 9.1, 9.2 through MP3
• Oracle WebLogic Server 8.1 through SP6
• Oracle WebLogic Server 7.0 through SP7

Este patch es de extrema urgencia y se recomienda la inmediata aplicación. Tal es así que se adelantó a la próxima actualización agendada (Oracle lanza patches regulares a mitad de abril, julio, octubre y enero). En este caso, una vulnerabilidad en el manejador de nodos permite acceder sin autenticación.

Recientemente un experto de seguridad había afirmado en una convención de hackers que 9 de cada 10 bases de datos Oracle podían ser violadas externamente, gracias a una vulnerabilidad que el encontró (aunque no mostró pruebas de hechos concretados). Este experto inglés, autor de varios libros, ya ha detectado decenas de bugs en productos como SQL Server, DB2, Informix, y Oracle. La companía aparentemente no se manifestó sobre este asunto.

Cierto o no, lo más recomendable es estar precavidos.

Ver también:
http://www.oracle.com/technology/deploy/security/alerts.htm
http://www.reuters.com/article/idUSTRE6125FB20100203?type=technologyNews

Como exportar e importar código PL/SQL en 9i

2010-01-07T05:31:00.000-08:00

A medida que avanzan las versiones de Oracle, cada vez se hace más dificil encontrar documentación para las anteriores, como por ejemplo 8i y 9i. Por suerte existen los blogs :)

En tiempos de 9i, EXP e IMP eran las opciones para exportar e importar objetos en nuestro esquema de base de datos. A partir de Oracle 10g, existen versiones mejoradas como Data Pump, que repasaré en otro momento.

Una duda que surge es como exportar e importar el código PL/SQL de un esquema para tener un rápido backup/restore. Hay algunos métodos que se basan en capturar el código en archivos (ver artículo), pero a la hora de llevar el código rápidamente con sus grants, todo de una vez a otro lugar, lo mejor es usar EXP/IMP.

Primero que nada, debemos saber que EXP/IMP trabaja en cuatro modos:

TABLE
TABLESPACE
USER
FULL

Si queremos exportar el código, deberemos usar el modo USER, o FULL (todos los esquemas).
Ahora, la única forma que IMP recupera el código PL/SQL, es importando en modo FULL. Aunque parezca extraño, el modo de importación USER no lo recupera aunque hayamos hecho el export de la misma forma.

Hecha esta aclaración, pasamos a los comandos.

Generar el dump (en modo USER)

exp usuario/password file=/tmp/bck_usuario.dmp owner=usuario rows=N triggers=Y indexes=N constraints=N log=/tmp/bck_usuario.log

Importar el código (en modo FULL)

imp usuario/password file=/tmp/bck_usuario.dmp full=Y ignore=y indexes=N log=/tmp/bck_usuario.log

Observaciones

Junto con el export, estamos capturando las tablas también (sin datos). El método EXP no es muy flexible en este sentido. Sin embargo a la hora de importar, con el parámetro ignore=Y, estaremos ignorando los intentos de creación si las mismas ya existen en el destino.
Como IMP únicamente puede utilizarse en modo FULL, si queremos traer solamente un usuario entonces deberemos generar un export de tipo USER.

Cómo saber el tipo de Linux para instalar Oracle

2009-12-18T10:36:00.000-08:00

Alguna vez caímos en paracaídas a instalar algún producto Oracle en un servidor Linux desconocido, e inmediatamente surgieron las siguientes interrogantes:

¿Qué versión de Linux es?
¿Cuál es la versión de Oracle que debo descargar?

La base de datos Oracle es el producto más portable del mercado, y por eso dispone de muchas versiones dependiendo de la arquitectura y versión del sistema operativo. Todo dba se ha visto confundido más de una vez al momento de elegir la versión correcta.

Por ese motivo recomiendo tener esta guía-ayuda a mano, para no perder tiempo a la hora de elegir.

Mapea los códigos informados por el comando Unix uname, con la versión de Linux que aparece en la página de downloads de Oracle. Basta con observar los últimos tres identificadores que aparecen en la salida del comando.
Por ejemplo para el primer caso sería "i686 i686 i386".

Espero que ayude!

$ uname -a 
Linux hostname 2.6.9-34.0.2.ELsmp #1 SMP Fri Jun 30 10:33:58 EDT 2006 i686 i686 i386 GNU/Linux  
-->

Linux x86: (32-bit OS)

$ uname -a  
Linux hostname 2.6.9-55.ELsmp #1 SMP Fri Apr 20 16:36:54 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux  
-->

Linux x86_64: (64-bit OS)

$ uname -a  
Linux hostname 2.6.9-42.EL #1 SMP Wed Jul 12 23:25:09 EDT 2006 ia64 ia64 ia64 GNU/Linux 
-->

Linux Itanium: (64-bit OS)

$ uname -a  
Linux hostname 2.6.9-34.0.1.0.11.EL #1 SMP Mon Dec 4 16:10:42 PST 2006 ppc64 ppc64 ppc64 GNU/Linux
-->

IBM Power Based Linux: (64-bit OS)

$ uname -a  
Linux hostname 2.6.16.46-0.12-default #1 SMP Thu May 17 14:00:09 UTC 2007 s390x s390x s390x GNU/Linux
-->

IBM zSeries Based Linux: (64-bit OS)

$ uname -a  
Linux hostname 2.4.21-50.EL #1 SMP Tue May 8 17:10:38 EDT 2007 s390 s390 s390 GNU/Linux
-->

IBM S/390 Based Linux (31-bit): (31-bit OS)

Controlando el horario de verano en Solaris

2009-11-28T10:03:00.000-08:00

Solaris 10 viene configurado con cambios de horario de verano automáticos (daylight saving time), basados en archivos de configuración predefinidos con fecha y hora del cambio para cada región.

Puede ocurrir, como ya pasó este año en Brasil, que el gobierno decida adelantar dicha fecha y entonces eso puede darnos algunos problemas si nos toma desprevenidos. Dependiendo del negocio, algunos sistemas en producción no deben alterar el horario, otros en cambio, deben hacerlo estrictamente según la normativa oficial.

Para tener el control, tenemos que verificar la configuración en Solaris y ver que está de acuerdo con nuestros intereses.

COMO ALTERAR LA CONFIGURACION DEL HORARIO DE VERANO

Paso 1 - Verificar la zona horaria del servidor

Siempre logueados como root, leemos el archivo TIMEZONE y buscamos el valor de la variable TZ al final del archivo:

cat /etc/TIMEZONE

TZ=Brazil/East

Paso 2 - Hacer un backup de la configuración actual (por si acaso)

cd /usr/share/lib/zoneinfo
cd Brazil (o la region que corresponda según el Paso 1)
cp East East.backup

Tal vez encontremos un archivo .zic, con alguna configuración que alguien ya hizo, es aconsejable darle una ojeada y ver si nos sirve para el paso 4.

Paso 3 - Verificar configuración del horario de verano, según el TZ encontrado en el Paso 1

zdump -v Brazil/East | grep 20

Encontraremos varias líneas del tipo:

Brazil/East Sat Nov 28 18:31:41 2009 UTC = Sat Nov 28 15:31:41 2009 BRT isdst=0

Para cada año aparece la correspondencia entre el horario universal (UTC) y el de nuestra región. El ultimo parámetro indica si se trata de horario de verano (isdst=1) o no (isdst=0), por tanto debe haber una diferencia de una hora menos cuando isdst=1.
O sea que si vivimos en UMT-3, en el horario de verano vamos a tener UMT-2.

Verificar se coincide con el cambio oficial programado para el corriente año.

Paso 4 - Crear un archivo .zic con las alteraciones deseadas

Crear un nuevo archivo llamado config_dst.zic con el siguiente contenido:

Rule Brazil 2009 only - Oct 11 00:00 1 S
Rule Brazil 2009 only - Oct 20 00:00 0 -

Zone Brazil/East -3:00 Brazil BR%sT

(fin del archivo)

En el paso 2 decíamos que tal vez ya exista un archivo .zic en el directorio, en ese caso podemos agregar nuestras líneas arriba del todo, así no perdemos el resto de las configuraciones. Cuidado que no existan otras definiciones para el mismo año, porque pueden entrar en conflicto.

La última línea indica el nombre del archivo de configuración, y en que directorio se encuentra. También define el indicador de horario de verano que veremos cuando usemos el comando date. Este será BRST en horario de verano, y BRT en horario normal.

Paso 5 - Aplicar la nueva configuración con el comando zic

zic config_dst.zic

Paso 6 - Esperar la fecha prevista y verificar

Con el comando date, verificaremos que el horario es el correcto y el tipo de horario es BRST, tal como lo configuramos.

Adiós Metalink

2009-10-07T07:49:00.000-07:00

El tradicional site de soporte oficial de Oracle, más conocido como Metalink (metalink.oracle.com) será retirado definitivamente el próximo 6 de noviembre, y dejará al nuevo sistema basado en Adobe Flash 9, 'My Oracle Support', como única opción para los clientes.

Personalmente gusto más del viejo Metalink desarrollado con APEX, el html es sin dudas más estable y sencillo de navegar que la abrumadora interfase de ventanas deslizantes y tiptools de My Oracle Support. No menos importante es el hecho de que Flash no funciona en muchos dispositivos móviles y algunos browsers, además de obligar a los usuarios a bajar software adicional. No me molesta que esté allí, siempre y cuando sea opcional.

Múltiples encuestas en páginas y blogs de Oracle muestran que la mayoría prefieren a Metalink. Yo mismo he colocado mi opinión en varias encuestas de Oracle, cada vez que me ha sido ofrecida la oportunidad. Pero parece que los planes de la corporación son inamovibles y Metalink dejará de existir para el lamento y experiencia de soporte de muchos.

Actualmente Oracle tiene abierta una encuesta para expresar nuestra opinión, en este link.

Más información:
Nota ID 841061.1 en Metalink
Trainings My Oracle Support

Oracle Critical Patch Octubre 2009

2009-09-04T06:47:00.000-07:00

El set de patches de seguridad que Oracle había programado para publicar el 13 de Octubre de 2009, fue re agendado para el 20 del mismo mes, es decir una semana después.

Llama la atención el motivo que la corporación dio al respecto: "ya que muchos clientes con responsabilidad de aplicar los patches en sus respectivas empresas van a estar asistiendo al Oracle Open World (11 al 15 de Octubre)".

Es ese un motivo justificado para retrasar un patch de seguridad? Porque un mínimo porcentaje de clientes va a asistir a tal evento? Acaso todos tienen que aplicarlos al mismo tiempo?

En realidad quien estará con toda la atención centrada en el evento, será naturalmente la propia compañia, incluyendo algunos responsables por los patches. Pero eso de argumentar que por causa de algunos clientes los retrasamos, no parece lógico ni justo.
Hacen parecer que los patches críticos no son tan críticos.

Cómo ocultar el código de PL/SQL

2009-08-13T07:00:00.000-07:00

Puede el código PL/SQL ser escondido y protegido de miradas ajenas, cuando lo implantamos en alguna base de datos externa?

La respuesta a esta inquietud es SI, gracias a un ejecutable y un paquete disponible que nos permite compilar un procedimiento almacenado y hacer que el fuente quede confuso para quien intenta leerlo.
Oracle se refiere a este método como ofuscamiento "obfuscation" (el término ofuscar en el diccionario tiene el significado de "oscurecer, encubrir").

Es curioso como todavía hay empresas que desarrollan software de altísimo valor corporativo y lo implantan expuestamente en bases de datos de usuarios finales que en muchos casos tiene acceso la propia competencia. No todos en la industria son mal intencionados, pero es mejor prevenir que lamentar perder margen de ventaja.

Qué encriptar

Todo lo que sea código almacenado: procedimientos, funciones, paquetes y tipos. La excepción son los triggers, el método no los soporta, sin embargo una solución es pasar la lógica a un procedure encriptado y llamarlo desde el trigger.

Una recomendación: usemos un criterio, no seamos paranoicos.
Muy a menudo, parte de nuestro código tiene que ser compartido con otros proveedores, necesitamos disponibilizar ciertos objetos para que otros puedan construir sus propios programas a partir de ellos. No necesitamos ofuscar todos los paquetes de nuestra base de datos, solamente aquellos que tengan lógica de negocio sensible de la compañia, como algoritmos, paquetes financieros, lógica de procesamiento, mantenimiento de cuentas, paquetes de seguridad, etc.

Oracle no recomienda usar este método para encriptar contraseñas, ya que si abrimos el archivo generado, podremos ver identificadores y reconocer algunas palabras que están presentes en el código original.

Antes de comenzar

Es importante tener en cuenta que estaremos escondiendo el código de miradas ajenas y hasta de la nuestra, ya que una vez que el código está encriptado en la base de datos, no hay forma ni usuario que pueda recuperarlo. Para realizar modificaciones, hay que hacerlas sobre la versión de texto original. La recomendación es usar un manejador de versiones como repositorio de código, y luego adoptar la práctica de encriptar antes de recompilar.

Cómo encriptar

Existe un ejecutable en $ORACLE_HOME/bin que se llama wrap.
Llamándolo desde la consola, y pasándole el nombre de un script en el parámetro iname, nos retorna un archivo de texto .plb con código interno, el cual podremos compilar en SQL*plus para crear el objeto almacenado "ofuscado".

Ejemplo 1

Vamos a encriptar un procedimiento. Para ello ya tenemos el código del mismo en un archivo ob_proc.sql, copiado en el servidor de la base de datos. Todo lo que tenemos que hacer es entrar en la consola y ejecutar:

wrap edebug=wrap_new_sql iname=ob_proc.sql

PL/SQL Wrapper: Release 9.2.0.8.0- Production on Thu Aug 13 11:38:30 2009 Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

Processing ob_proc.sql to ob_proc.plb

Nota: El flag edebug=wrap_new_sql es necesario para poder soportar el nuevo compilador de sql y corregir un bug existente con algunas sintaxis de sql avanzado.

La salida, es el archivo encriptado ob_proc.plb, el cual ahora podemos compilar en SQL*plus.

SQL> @ob_proc.plb

Procedure created.

Esta pronto. El código del procedure en la base de datos no es más legible, ya sea usando cualquier programa de desarrollo, paquete Oracle o vista del diccionario. Sin embargo es perfectamente ejecutable como cualquier otro procedimiento.

Ejemplo 2

Ahora encriptaremos un paquete. Se realiza en forma similar, recordando que el paquete se compone por especificación y opcionalmente un cuerpo. Si bien podemos encriptar ambos, se recomienda en la mayoria de los casos encriptar únicamente el cuerpo. Después de todo, es donde reside la lógica que queremos proteger. La especificación es útil muchas veces para consultar la firma de las funciones que están siendo expuestas, y es amable disponibilizarlas para el uso común.

Teniendo el cuerpo de nuestro paquete preparado en el archivo ob_pack_body.sql, ejecutamos:

wrap edebug=wrap_new_sql iname=ob_pack_body.sql

PL/SQL Wrapper: Release 9.2.0.8.0- Production on Thu Aug 13 11:38:30 2009 Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

Processing ob_pack_body.sql to ob_pack_body.plb

Si nuestro paquete ya estaba compilado en la base de datos, recordemos que únicamente necesitamos recompilar el cuerpo. De lo contrario, tendremos que compilar la especificación primero.

SQL> @ob_pack_body.plb

Package body created.

El código de especificación del paquete, que ya estaba compilado, continúa siendo visible, mientras que el body ahora fue ocultado y no está más disponible a la vista de todos.

Ver también:
Cómo extraer código en archivos separados

¿Cuál es el mejor tipo de datos para almacenar IPs?

2009-07-15T07:06:00.000-07:00

Esta pregunta surgió en Oracle Wiki, foro en el cual habitualmente colaboro.

"Alguien ha desarrollado un tipo datos para IP address? Necesito que sea indexable y que pueda usar operaciones OR, AND, XOR para chequear que una IP está en el rango especificado por la red y la máscara."

Como todos sabemos, las IPs están compuestas por 4 números que van de 0 a 255, separados por un punto. Esta es una representación posible, la que hace fácil su lectura en términos de redes. Sin embargo, también podemos convertirlas a notación binaria, decimal o hexadecimal.
El modo que elijamos va a beneficiar un aspecto u otro, dependiendo el uso y aplicación que le estemos dando a la información.

Algunas formas de almacenar IPs

Hay muchas maneras de almacenar direcciones IP, y nos bastan los tipos de datos nativos que Oracle ofrece. Estas son las más comunmente utilizadas.

Notación de Punto
Es el típico formato de IP que todos conocemos.
Ejemplo: 127.0.0.1
Tipo de datos sugerido: VARCHAR2(15)
Ventajas: Fácil de leer y recuperar, listo para reportes relacionados a redes.
Desventajas: Requiere convertir para poder usar algebra booleana, no muy eficiente en uso de espacio.

Hexadecimal
Es el formato de IP removiendo los puntos y pasando cada componente a hexa.
Ejemplo: 7F000001
Tipo de datos sugerido: VARCHAR2(8)
Ventajas: Fácil de convertir a los otros modos, ocupa menos espacio que la notación de punto.
Desventajas: Requiere conversión para lectura y operaciones booleanas.

Decimal
Es la representación interna de la IP. Usado comúnmente por browsers y algunos protocolos, es la representacion decimal del formato binario.
Ejemplo: 2130706433 (equivale a 127.0.0.1)
Tipo de datos sugerido: NUMBER(10)
Ventajas: Uso de espacio muy optimizado, no requiere conversión para integración con algunos protocolos.
Desventajas: Es el formato más ilegible (para los humanos) de todos.

Nota: Multiplicando cada componente de la IP por una potencia 2^24, 2^16, 2^8 y 2^0, y sumándolos, obtendremos el entero equivalente. Hagan la prueba de convertir una IP y colocarla en el browser!

Notación Binaria
Podemos tomar cada número de la representación por punto y convertirlo a un octeto binario, completando con ceros a la izquierda.
Ejemplo: 01111111000000000000000000000001 (equivale a 127.0.0.1)
Tipo de datos sugerido: VARCHAR2(32)
Ventajas: Listo para algebra booleana, fácil de convertir a otros formatos.
Desventajas: Poco legible, uso muy ineficiente de espacio.

Conclusiones

No hay formatos malos ni buenos, sólo que algunas opciones serán mejores que otras a la hora de decidir. La idea es minimizar ese impacto según nuestro objetivo.

El formato de punto es óptimo para lectura de redes, ya que con una vista rápida es posible consultar direcciones IP directamente y presentar reportes sin esfuerzo. Por su claridad, es recomendado para el desarrollo de aplicaciones que registran logs de tráfico.
El formato hexadecimal es una variante para intentar mejorar el uso de espacio, ya que solamente 8 bytes serán necesarios. Por otro lado, requiere convertir cada par para obtener el formato tradicional y hacerlo más comprensible en lenguaje de red.
El formato decimal es el más eficaz utilizador de espacio. En sistemas real-time, BD embebidas y dispositivos móviles, el uso de espacio es crítico. Nótese que el tipo sugerido ocuparía 6 bytes en el peor caso, contra 8 fijos que utiliza la forma hexadecimal.
Finalmente el formato binario es para programadores 'perezosos' que validan IPs, ya que no requieren convertirla. Por ejemplo, si nuestra IP es de tipo A, B o C, realizando la operación (IP) AND (Netmask correspondiente), obtenemos el ID de red. Del lado de las desventajas, ademas de ser difícil de leer, es un gran derrochador de espacio.

Obtener totales con AWK

2009-07-09T13:20:00.000-07:00

Hay veces que en nuestro trabajo somos solicitados para realizar un spool de datos (millones de registros) y finalmente informar el total de algunas columnas.

Dependiendo de varios factores como cantidad de registros, distribución de datos y relacionamientos, la ejecución de la consulta primaria para obtener los datos, puede ser costosa. Un tiempo similar puede tomarnos ejecutar la consulta para obtener el total de las columnas.

Sin embargo, el uso de AWK para obtener los totales, puede ser un diferencial. La performance de esta operación en el archivo de texto sobre la ejecución de un SQL (usando sum y group by) puede ser abismal (de muchos minutos a un par de segundos).

Ejemplo: como obtener el total de la segunda y tercera columna de un archivo spool con millones de registros y valores separados por pipe '|'.

awk -F"|" '{sum1+=$2; sum2+=$3} END {print sum1,sum2}' archivo_entrada.csv

Primero en la sección de procesamiento línea a línea (primer par de { }) sumamos los valores de la segunda y tercera columna ($2, $3) en dos variables, sum1 y sum2, por defecto inicializadas en cero.

Finalmente la sección de ejecución final muestra los valores de sum1 y sum2.

Si deseamos usar otro delimitador bastará con alterar el parámetro -F.

Así de simple y eficiente.

Ordenar resultados por el alfabeto castellano

2009-06-12T06:49:00.000-07:00

Por defecto, el ordenamiento de una consulta SQL en Oracle se rige según el valor numérico del mapa de caracteres, típicamente: A, B, C, D, E , F, G, H, I, etc. Este método, que recibe el nombre de ordenamiento binario, es el método más eficiente y conveniente para ordenar palabras en inglés.

Sin embargo, las palabras del castellano no serán ordenadas correctamente, y muchas veces debemos respetar las reglas de nuestro idioma (aunque la popularidad del inglés diga lo contrario).

Por ejemplo si listamos países, Chile aparecerá antes que Colombia, cuando la letra 'CH' debería suceder a la 'C'. Similarmente la palabra lluvia aparecerá antes que lotería, cuando no deberíamos ignorar que la 'LL' viene despues de la 'L'. Finalmente recordar que nuestra original letra eñe no puede ser dejada de lado.

Para ordenar usando las letras del español, bastará con alterar la sesión:

ALTER SESSION SET nls_sort=spanish_m;

Ejemplo

SQL> select * from paises order by nombre;

NOMBRE
------------
Camerún
Canadá
Chile
Colombia
Congo
Cuba

SQL> alter session set nls_sort=spanish_m;

Session altered

NOMBRE
------------
Camerún
Canadá
Colombia
Congo
Cuba
Chile

Acentos y mayúsculas

Otro efecto de setear el parametro nls_sort, es que suprimimos la precedencia de palabras acentuadas sobre no acentuadas, así como de mayúsculas sobre minúsculas.

Ejemplo 1: Problema de acentos

SQL> select letra from mialfabeto order by letra;

LETRA
--------
a
b
c
á

SQL> alter session set nls_sort=spanish_m;

SQL> select letra from mialfabeto order by letra;

LETRA
--------
a
á
b
c

Ejemplo 2: Problema de mayúsculas que anteceden a minúsculas

SQL> select texto from conceptos order by texto;

TEXTO
--------------
Integrado
distribuído
integración

SQL> alter session set nls_sort=spanish_m;

SQL> select texto from conceptos order by texto;

TEXTO
--------------
distribuído
integración
Integrado

Un autodeadlock y algunas buenas prácticas

2009-05-13T10:58:00.000-07:00

Este deadlock sí que es eficiente. Poco esfuerzo de programación, una única sesión y una espera de un recurso siendo usado por... uno mismo!

CREATE OR REPLACE PROCEDURE deadlock_suicida AS
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE deadlock_suicida';
END;
/

Procedure created.

SQL> exec deadlock_suicida;

Deadlock más comunes
El ejemplo de deadlock más frecuente es cuando dos sesiones intentan modificar registros de una tabla que la otra sesión está usando, y terminan esperando a que la otra libere primero. Un deadlock no ocurre cuando las operaciones son de lectura, sino que tienen que existir operaciones que produzcan locks implícitos o explícitos.

Los deadlocks no son un defecto de Oracle, sino que reponden a carencias de conocimiento de los mecanismos de locking por parte del programador, que no ha tenido en cuenta que su código es ejecutado por múltiples sesiones que comparten los recursos concurrentemente.

Afortunadamente, este problema es automáticamente manejado por Oracle. Cuando dos sesiones se encuentran bloqueadas, una de ellas recibirá el mítico ORA-00060 y será finalizada con rollback. La otra sesión sufrió algunos minutos de bloqueo, pero continuará ejecutando normalmente. Como es una duda común, aclaro que no existe manera alguna de ajustar el tiempo de timeout.

Cómo evitar deadlocks
No existen recetas infalibles para evitar los deadlocks sino buenas prácticas. Deshacerse de un deadlock puede ser muy trabajoso si no se conoce a fondo la aplicación que lo produjo. Requiere de un análisis exaustivo para determinar como los registros son obtenidos y liberados por una sesión, y luego extrapolar sobre un escenario de dos sesiones concurrentes. El primer paso de análisis, debe ser analizar la sesión generada en el directorio de dump de usuarios ubicada en $ORACLE_HOME/admin/udump.

Algunas buenas prácticas
1) Si no se trata del mismo código que generó deadlock, tratar que los registros de todas las tablas sean obtenidos en el mismo orden por las aplicaciones en conflicto.
2) Intentar obtener los recursos más exclusivos que van ser modificados en primer lugar. Puede usarse SELECT FOR UPDATE para este propósito. Es aconsejable tener nuestra aplicación bien tuneada de forma de no bloquear a todas las otras sesiones y enlentecer nuestro sistema.
3) Por ejemplo si vamos a actualizar tablas padre-hijo, podemos tomar la práctica de siempre bloquear primero la tabla padre.

Cómo crear una vista parametrizada

2009-04-01T07:58:00.000-07:00

En Oracle, podemos crear vistas que retornen resultados dependientes de parámetros previamente seteados. La forma de lograr esto es usando un feature llamado Application Contexts.

¿Qué es un contexto de aplicación?
El contexto de aplicación es simplemente un espacio en memoria que nos permite almacenar valores para luego utilizarlos en SQL o PL/SQL, como cualquier otra variable definida en el entorno. De forma transparente, mis objetos pueden ser 'manipulados' externamente sin necesidad que mis aplicaciones o procesos batch se enteren.
El contexto puede ser definido tanto localmente (privado para cada sesión) como globalmente, compartiendo sus valores para todas las sesiones de la instancia.
Para poder alterar los valores del contexto, debemos crear un paquete especialmente autorizado para ese fin. Esto es un requerimiento por razones de seguridad.
También necesitamos tener el permiso especial de sistema CREATE ANY CONTEXT.

Ejemplo (con sqlplus)

Vamos a ver un sencillo ejemplo de cómo implementar una vista parametrizada con contextos de aplicación, usando el popular usuario SCOTT. El parámetro para la vista en este caso será el número de departamento.

Paso 1: Crear el contexto de aplicación

CREATE CONTEXT app_ctx_scott USING pk_scott_app_context
/

Paso 2: Crear el paquete para manipular el contexto

CREATE OR REPLACE PACKAGE pk_scott_app_context AS
  -- El contexto tendra un unico valor deptno
  PROCEDURE set_dept (p_deptno IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pk_scott_app_context AS
  PROCEDURE set_dept (p_deptno IN NUMBER) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('app_ctx_scott', 'deptno', p_deptno);
  END;
END;
/

Paso 3: Setear el parámetro de contexto deptno para el departamento de ventas

BEGIN
pk_scott_app_context.set_dept(30);
END;
/

Paso 4: Crear la vista parametrizada

CREATE VIEW empleados AS
SELECT e.empno, e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno = sys_context('app_ctx_scott','deptno');

Paso 5: Obtener los resultados consultando la vista

SELECT * FROM empleados;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7844 TURNER     SALESMAN  SALES
      7900 JAMES      CLERK     SALES

6 rows selected.

Actualmente la vista retorna los empleados del departamento de ventas, ya que así está definida la variable en el contexto. Ahora cambiaré el valor del parámetro para que la vista retorne resultados únicamente del departamento contable:

BEGIN
  pk_scott_app_context.set_dept(10);
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM empleados;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7782 CLARK      MANAGER   ACCOUNTING
      7839 KING       PRESIDENT ACCOUNTING
      7934 MILLER     CLERK     ACCOUNTING

3 rows selected.

De la misma forma podemos aplicar esta técnica a procedimientos y funciones, pudiendo manipular valores utilizados internamente o inclusive introduciendo fragmentos de código como sql dinámico.

Migrando MySQL a Oracle con SQL Developer

2009-03-01T01:58:00.000-08:00

Siempre que evaluamos herramientas para realizar esta tarea, debemos considerar variables tales como rapidez, facilidad de uso y bajo costo, entre otras.

SQL Developer es un producto de costo cero, que permite migrar bases de datos MySQL, Access o SQL Server a Oracle con facilidad.
En este tutorial mostraré el método de migración rápida de esquemas MySQL 5 a Oracle XE, usando la versión 1.5.3 de SQL Developer.

Preparación del software

- Descargar e instalar SQL Developer:
La última versión está disponible en OTN.

- Instalar el plugin de conexión a MySQL:
Para esto, desde SQL Developer vamos al menú Ayuda, Verificar Actualizaciones, marcamos la casilla Third Party SQL Developer Extensions y descargamos el driver de conexión a MySQL. SQL Developer deberá ser reiniciado para que el plugin entre en efecto.

NOTA: Si la extensión no nos aparece entre las opciones de descarga, es posible que ya la tengamos instalada. Para verificar, vamos al menú Ayuda, Sobre, Extensiones, y verificamos que exista la entrada MySQL JDBC Driver.

Crear un usuario para la migración

El usuario de migración será el encargado del proceso de colección, transformación, carga y movimiento de datos, y necesitará un conjunto de permisos especiales para tales efectos.

Para crearlo, loguearse a sqlplus con el usuario system y ejecutar:

CREATE USER migration IDENTIFIED BY xxxxxxx DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT create session, create view, resource, create user, create role, alter any trigger TO migration WITH ADMIN OPTION;

Crear las conexiones

- Crear una conexión para el esquema MySQL a ser migrado:
Colocar un nombre para la conexión y el usuario MySQL (en el ejemplo será root, sin password). Luego seleccionar la lengueta MySQL para verificar el hostname (o IP del servidor) y el puerto (generalmente 3306).
Testeando la conexión, el resultado debe ser SUCCESS para poder continuar, de lo contrario revisar el nombre del servidor Apache, usuario de MySQL, y el puerto configurado en el archivo de configuración my.ini.

- Crear una conexión Oracle para el repositorio y el esquema destino:
Similarmente como hicimos para MySQL, crearemos una conexión para nuestra base de datos destino, en este caso XE. Configurar el usuario system con su password, y el nombre del servicio de nuestra base de datos.
Como en el caso anterior, testear la conexión.

Una vez creadas, ambas conexiones deberán aparecer en el menú vertical izquierdo. A partir de ellas podremos ver la definición de los objetos antes y después de la migración.

Es recomendado crear una conexión separada para el repositorio y otra para el esquema destino, la idea es que sean usuarios diferentes. Para simplificar este ejemplo utilizaremos el mismo esquema para las dos cosas.

Crear un repositorio

SQL Developer usa un Repositorio para almacenar los packages y datos temporales para validar y convertir nuestros objetos. Si no lo creamos, el propio wizard lo creará automáticamente, pero siempre es mejor crearlo antes y verificar que quede todo correctamente instalado.

Vamos a la conexión de Oracle recientemente creada y con el botón derecho seleccionamos Migration Repository, y Associate Migration Repository.

En un minuto, el repositorio estará creado. Podemos verificar que las tablas y vistas del repositorio fueron creadas, con prefijo 'MD_' y 'MGV_' respectivamente. También es bueno verificar que estén correctamente compilados los 4 packages: MD_META, MIGRATION, MIGRATION_REPORT y MIGRATION_TRANSFORMER.

Iniciar el asistente

Teniendo todos los pasos previos completados, comenzamos con la migración propiamente dicha. Vamos al menú Migration, Quick Migrate, y seleccionamos la conexión de la base de datos orígen, es decir MySQL.

Si nuestra conexión fue correctamente creada, nos pide la conexión destino. Colocaremos la conexión XE creada para recibir los objetos.

El siguiente paso es la verificación del repositorio a utilizar. Debe aparecer el mensaje OK: Using [nombre conexión].

El cuarto paso es la verificación de pre-requisitos. Luego de presionar el botón Verify, una serie de tests son ejecutados, que incluyen conectividad de fuente y destino, y permisos de usuario. Cualquier observación que Oracle levante aquí tiene que ser resuelta antes de poder avanzar.

Luego que todos los pasos de la verificación de pre-requisitos resultan en SUCCESS, seguimos.

Finalmente debemos elegir el tipo de migración entre MIGRATE TABLES ONLY, MIGRATE TABLES AND DATA o MIGRATE EVERYTHING.

En este caso elegiremos migrate everything: usuarios, tablas, datos, índices, constraints, vistas y código.

Al presionar Finalizar, cruzaremos los dedos.

Resultados de la migración

La imagen a la izquierda muestra el resultado final de cada stream que realiza el movimiento final en paralelo, junto con la cantidad de filas migradas y errores sucedidos. Es de esperar no encontrar ningún error aquí!

Durante la migración, podemos pasar todas las etapas sin inconvenientes - el caso ideal- o, podemos encontrar errores generalmente durante la ejecución (Build) de los DDLs generados, lo cual abortará el proceso. La verdad verdadera, es que no siempre lograremos terminar exitosamente la migración en la primera vez, solamente si la base de datos orígen respeta ciertas reglas en su definición.

Diversos errores de conversión pueden ocurrir desde que MySQL permite amplia libertad de declaraciones fuera del ANSI/ISO SQL standard (además de que MySQL tiene varias extensiones propias de SQL).

En caso de error, debemos leer el dump de la ejecución y analizar de qué tipo fue la falla. Una vez resuelto, eliminaremos los objetos 'sucios' creados y volveremos a ejecutar el asistente.

Este proceso de ensayo y error puede ser repetitivo, y es común en la gran mayoría de las migraciones hasta que finalmente logramos una ejecución limpia. Necesitaremos paciencia y perseverancia para llegar al objetivo.

Tareas post-migración

Luego de la migración, es recomendable verificar que cada tabla fue copiada correctamente. Revisar como fueron transformados los datos, verificar los campos de tipo fecha o numéricos, o aquellos que tienen valores por defecto.

Es también un buen momento para reforzar la integridad del esquema con todas aquellas mejoras que Oracle ofrece como foreign keys, constraints, e índices. MySQL es poco restrictivo en ese aspecto, y eso puede generar vicios para algunos programadores.
Por ejemplo, el hecho de que las fks y transacciones son soportadas solamente en tablas InnoDB. En este tipo de escenarios, son las aplicaciones las responsables de forzar la integridad de datos, y no siempre logran ese objetivo.

Cuidado con aplicar restricciones sin analizar previamente su impacto, ya que podremos estar causando un mal peor del que queremos remediar. Un ejemplo común, es el uso de claves referenciales fantasma (o dummy) en la aplicación (sin clave valida en la tabla padre). Tendremos que evaluar si al incorporar las constraints estaremos afectando a la aplicación que tiene implementada esta práctica.

Una crítica que encuentro oportuna, es el hecho de que para cada campo autonumérico de MySQL, SQL Developer crea una secuencia y un trigger asociado para 'simular' la asignación automática. Entiendo que está a favor de la transparencia, pero por otro lado me parece una decisión interesante en términos de diseño, y me gustaría que fuera un feature opcional para tener el control.

De la misma forma, otra opción 'indeseada' para mí, es que genera un trigger por cada campo enumerado ('1', '2', '3',..) para asegurar la asignación de valores. Desearía que SQL Developer creara check constraints en lugar de estos triggers que sólo empeoran la performance y limitan la escalabilidad de las aplicaciones.

En conclusión

Por la facilidad de operación, visibilidad gráfica de cada una de las etapas, bajo costo -y pese a las críticas que he expuesto-, recomiendo que le den una oportunidad a SQL Developer como opción para migrar de MySQL a Oracle.

Soporte Oracle con VMWare?

2009-02-20T06:14:00.000-08:00

Oracle establece según nota 249212.1 en Metalink, que no ha certificado ningún producto corriendo virtualizado con VMWare. Si un problema determinado ocurre y el usuario necesita soporte oficial Oracle, deberá probar que el error no se debe a estar corriendo bajo VMWare, por ejemplo reproduciéndolo bajo el sistema operativo nativo. Esto sin dudas complica la existencia de los administradores de sistemas y bases de datos, que deben preparar ambientes no virtualizados para intentar capturar el mismo error. En ese proceso, el tiempo puede ser el peor enemigo.

Desde el punto de vista del soporte, es entendible y sensato que Oracle no se responsabilice por productos de terceros como VMWare. Desde el punto de vista comercial, es una posición conveniente en tiempos que Oracle invierte en marketing para su propia infraestructura de virtualización (Oracle VM). Los potenciales compradores de una solución virtual deberán evaluar con especial cuidado el soporte que tendrán corriendo una base de datos Oracle virtualizada. Una decisión segura llevará a adoptar el producto que le garanta la máxima cobertura de soporte.

VMWare, INC comienza a experimentar un crecimiento desacelerado y ya tiene sus primeras bajas en la guerra de la competencia con Microsoft, Citrix, Sun, Oracle y otros. Su presidente ejecutivo fue recientemente demitido, y para empeorar los pronósticos, viene de recibir un duro golpe en el mercado de valores luego que sus acciones cayeran un 11%, pese al crecimiento del 53% en sus ganancias en el último balance de 2008.

Error al crear usuario en Oracle TimesTen

2009-02-05T10:00:00.000-08:00

Cuando se intenta crear un usuario en Oracle TimesTen, se obtiene el siguiente error:

Command> create user lferTTadmin identified by '$ql450';
15007: Access control not enabled
The command failed.

Durante la instalación de TimesTen 7.0, se preguntó al usuario si se deseaba activar el access control (Do you want to enable Access Control? Yes/No). Si No fue la respuesta, aún hay una forma de poder activarlo 'post instalación'.

Abrir una consola del sistema operativo, y ejecutar:
ttmodinstall -enableAccessControl

Luego de eso, el control de acceso estará habilitado.

C:\Windows\system32>ttmodinstall -enableAccessControl

C:\Windows\system32>"C:\TimesTen\tt70_32\perl\bin\perl.exe" "C:\TimesTen\tt70_32
\bin\ttmodinstall" -enableAccessControl
Would you like to enable access control for this instance? [ no ] yes
NOTE: The daemon must be stopped before enabling access control.
Would you like to stop the daemon? [ yes ] yes
The TimesTen Data Manager 7.0 service is stopping...
The TimesTen Data Manager 7.0 service was stopped successfully.

Patching successful ...
Restarting the daemon ...
The TimesTen Data Manager 7.0 service is starting.
The TimesTen Data Manager 7.0 service was started successfully.

Access control is now enabled for this TimesTen instance.
C:\Windows\system32>ttisql TT_test

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.

connect "DSN=TT_dns_prod04";
Connection successful: DSN=TT_test;UID=sixbell;DataStore=C:\Users\dashboard\Deskto
p\temp\TT_store;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;DR
IVER=C:\TimesTen\tt70_32\bin\ttdv70.dll;TypeMode=0;
(Default setting AutoCommit=1)
Command> create user lferTTadmin identified by '$ql450';
Command>

Parches de seguridad Enero 2009

2009-02-05T06:22:00.000-08:00

Oracle ha anunciado un nuevo Critical Patch Update que afectan a la mayoría de sus productos, y recomienda fuertemente su aplicación en todos los ambientes, para evitar la explotación de accesos indebidos.
El set incluye 20 patches para Oracle Database (a partir de 9i Release 2), 4 para Application Server (a partir de 10g Release 2), 1 para Collaboration Suite (a partir de 10g), 4 para Applications Suite (a partir de 11i), 1 para Enterprise Manager (a partir de 10g Release 4), 6 para PeopleSoft y JDEdwards Suite (a partir de 8.9), y 5 para BEA Products Suite (a partir de 7.0).
Desde luego que el download está disponible en OTN.

Ver también
Critical Patch Update Advisory - January 2009

Cómo pasar valores desde sqlplus hacia Unix Shell

2009-02-04T11:38:00.000-08:00

Hay ocasiones que dentro de un Shell script queremos consultar la base de datos y con dicho resultado continuar la lógica de nuestro Shell. Como logramos transferir ese resultado?

Tenemos dos enfoques para capturar valores luego de terminada la ejecución de Sql*plus: por archivos y por variables.

La primera de ellas es básicamente escribir la salida de la consulta en un archivo y luego levantarlo desde Shell. Este caso es ideal cuando nuestra consulta retorna varios registros, ya que el archivo servirá de entrada a algún comando Unix para procesarlo línea a línea. No veremos un ejemplo ya que es muy sencillo, basta usar el comando SPOOL de Sqlplus para obtener la salida en un archivo de texto. Si bien no es del todo prolijo, funciona.

Y cuando digo prolijidad, me refiero a que en realidad generar un archivo temporal no es necesario, ya que podemos recoger el resultado de la consulta en una variable Shell directamente. Lo veremos con algunos ejemplos hechos para Solaris 10. Algunos parámetros pueden variar respecto a Linux, se recomienda acudir a la documentación de man.

Resultado único
Supongamos que quiero leer el contenido de la columna Dummy de la tabla Dual y continuar manipulando el valor en Shell.

Unix lo hace fácil: ejecutamos la consulta en una sesión sqlplus, y la salida es capturada en la variable 'resultado'. Para simplificar este ejemplo no he considerado si hubo algún error en la consulta, pero puede agregarse lógica que trate mensajes de error dentro de la variable asignada.


#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
select * from dual;
exit;
EOF`

echo "El resultado es: $resultado"

Varios resultados en una línea
Qué hacer si necesitamos retornar más de un valor? Continuamos retornando una única línea, pero separamos los valores dentro del sql con algún caracter que no ocurra dentro de cada resultado, por ejemplo punto y coma. En el ejemplo obtenemos el usuario conectado, la fecha actual y el valor de la columna de Dual, todo al mismo tiempo.

#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
select user||';'||sysdate||';'||dummy from dual;
exit;
EOF`

echo "Los valores son: $resultado"

El valor retornado es: SCOTT;05-FEB-09;X

Con el comando cut separamos los valores fácilmente:

echo $resultado | cut -d';' -f1
SCOTT
echo $resultado | cut -d';' -f2
05-FEB-09
echo $resultado | cut -d';' -f3
X

Resultados multilínea
Si queremos obtener varias líneas en lugar de una sola, también podemos hacerlo de esta forma. Como en el caso anterior, si tenemos múltiples valores por línea es aconsejable usar separadores, ya que los espacios no son buenos a la hora de identificar strings que contengan espacios. Además, evitamos los incómodos espacios entre líneas al optimizar el tamaño de cada cadena y no llegar al fin de cada línea.

#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
set linesize 131
set pagesize 9999

select empno||';'||ename||';'||job||';'||mgr||';'||deptno from emp;
exit;
EOF`

echo "El resultado es: $resultado"

La salida en este caso es:

7369;SMITH;CLERK;7902;20
7499;ALLEN;SALESMAN;7698;30
7521;WARD;SALESMAN;7698;30
7566;JONES;MANAGER;7839;20
7654;MARTIN;SALESMAN;7698;30
7698;BLAKE;MANAGER;7839;30
7782;CLARK;MANAGER;7839;10
7788;SCOTT;ANALYST;7566;20
7839;KING;PRESIDENT;;10
7844;TURNER;SALESMAN;7698;30
7876;ADAMS;CLERK;7788;20
7900;JAMES;CLERK;7698;30
7902;FORD;ANALYST;7566;20

Ahora, podemos usar cualquier comando para tratar las líneas. Uno de mis favoritos es awk, ya que nos provee de muchas funciones para tratamiento de cada tipo.
Para que awk consuma cada línea de la variable como si fuese un archivo, debemos incluir comillas dobles, de otro modo lo considerará como una única gran línea.
En el siguiente ejemplo, vemos como awk toma línea a línea e imprime un texto anexo.

echo "$resultado" | awk -F";" 'BEGIN {$cnt=1}
{print "Linea "$cnt, $0; $cnt=$cnt+1;}'

La salida generada por awk es:

Linea 1 7369;SMITH;CLERK;7902;20
Linea 2 7499;ALLEN;SALESMAN;7698;30
Linea 3 7521;WARD;SALESMAN;7698;30
Linea 4 7566;JONES;MANAGER;7839;20
Linea 5 7654;MARTIN;SALESMAN;7698;30
Linea 6 7698;BLAKE;MANAGER;7839;30
Linea 7 7782;CLARK;MANAGER;7839;10
Linea 8 7788;SCOTT;ANALYST;7566;20
Linea 9 7839;KING;PRESIDENT;;10
Linea 10 7844;TURNER;SALESMAN;7698;30
Linea 11 7876;ADAMS;CLERK;7788;20
Linea 12 7900;JAMES;CLERK;7698;30
Linea 13 7902;FORD;ANALYST;7566;20
Linea 14 7934;MILLER;CLERK;7782;10

Oracle SQL Developer Data Modelling

2009-01-30T04:34:00.000-08:00

Los días de Oracle Designer 10g están acabando. El cásico paquete de diseño, versionamiento e implementación de bases de datos, tiene un jóven sustituyo acorde a los tiempos que corren: Oracle SQL Developer Data Modelling.

Esta herramienta de nombre largo es totalmente gratuita, profesional y actualizada, y su propósito es quitarle el mercado que otros diseñadores compactos como Erwin o Toad Data Modeller han ganado.

Es que la comunidad Oracle reclamaba hace tiempo las limitaciones de la interfase, que no había cambiado mucho desde la versión Designer/2000 de 1995, y prácticamente nada desde 9i para 10g. También, la ubicación geográfica de los desarrolladores era un problema cada vez que debían movilizarse lejos del repositorio que Designer require para funcionar.

Oracle SQL Developer Data Modelling simplemente se copia en una carpeta y no require de instalador. Es un producto separado disponible para Windows, Linux y Mac.
Se conecta con Oracle a partir de 9.2.0.1, no necesita un repositorio (aunque opcionalmente puede usarlo), y tiene una abanico de funcionalidades muy atractivas: modelos lógico, relacional y físico, ingeniería reversa desde otras bases de datos, diseño multidimensional de tablas para DW, data flow diagrams, modelado gráfico de Oracle types, importación desde otros modeladores como Erwin, importación por ODBC, exportación a ddl, csv, xml, Oracle AW.

Mi impresión sobre la herramienta es muy buena. Luego de un buen tiempo cuesta dejar algunos condicionamientos que el arcaico Designer impone para sobrellevar los problemas de su propia interfase, pero parece que con Oracle Data Modelling no son más necesarios.
Uno típico era el nombrado automático de objetos, Designer tenía una forma particular de autogenerar nombres de FKs e índices, y cada vez que se generaban había que arreglarlos. Oracle Data Modeller permite personalizar todos esos prefijos que usa para ingeniería reversa, o pasaje de modelo lógico para relacional, facilitando el cumplimiento de estándares. Por ejemplo para la generación de índices puedo definir los nombres como: IX_{table}_{seq nr}.
El Navigator (pequeño cuadro que muestra un mapa de todo el modelo) es otra buena innovación que aporta navegabilidad en diagramas grandes.
Es posible especificar una vasta variedad de propiedades sobre cada objeto, para mejorar la generación de código final y evitar tener que repasar los ddls generados.
También cuenta con un analizador de impacto, para anticipar la propagación de cambios en el modelo existente.
La integración por ODBC permite conectarse con prácticamente cualquier base de datos existente.
La mayor ventaja de todas es, como dije anteriormente, que es gratuito.

Oracle SQL Developer Data Modeller está proyectado para ser lanzado sobre el correr de 2009, pero una serie de versiones beta están siendo publicadas por Oracle para evaluación.
La versión Early Adopter Release 2 (Nov 2008) está disponible para descargar en OTN.

ACTUALIZACIÓN (01-mar-2010): Este articulo comenta sobre la versión Early Adopter R2, la cual no está más disponible. Las versiones comerciales actuales no son gratuitas. Ver comentarios abajo.

Ver también:
OTN SQL Developer

Combinando Microsoft Access y Oracle

2009-01-23T16:04:00.000-08:00

En este artículo vamos a ver cómo implementar una sencilla aplicación que trabaje sobre una base de datos Oracle para actualizar datos y generar reportes, usando una herramienta amigable y disponible en la mayoría de los ordenadores como lo es Microsoft Access.

La idea es, en 5 pasos crear una interfase gráfica con poco esfuerzo y en tiempo récord, que facilite la vida de muchos usuarios no IT, como por ejemplo la del Gerente del Area de Recursos Humanos que necesita ingresar y consultar datos en su base de datos corporativa. No hay necesidad de comprar un caro producto comercial para esa sencilla tarea.

Qué software necesitamos para desarrollar y ejecutar nuestra aplicación?

- Microsoft Access, sino nuestro archivo .mdb no será reconocido por Windows.
- Un cliente Oracle, para que podamos conectarnos con la base de datos de destino.

(Este software debe estar presente ya sea en la máquina que usemos para desarrollar como en la máquina destino donde va a correr el programa. No está en el alcance de este artículo explicar cómo instalar estos dos productos)

Los 5 pasos para crear la aplicación

1) Crear una conexión ODBC
2) Vincular en Access las tablas de Oracle
3) Crear las relaciones existentes
4) Crear formularios
5) Crear un reporte

Nuestra aplicación va a conectarse y trabajar con el usuario Oracle SCOTT. Uso Windows Vista, Access 2003 y un cliente Oracle 10g.

Disculpas por las pantallas de Access en portugués y Windows en inglés, pero es todo lo que tengo en la vuelta :)

Paso 1. Crear una conexión ODBC

- En Windows, vamos al menú Start, Control Panel, Administrative Tools y seleccionamos Data Sources (ODBC)

- En la lengueta User DNS, presionar sobre el botón Add. Una lista de drivers disponibles aparecerá en un diálogo. Tenemos dos posiblidades para seleccionar, una es elegir el driver de Microsoft para Oracle (Microsoft ODBC for Oracle), y la otra es elegir el driver de Oracle que fue instalado con nuestro cliente (en mi caso Oracle em OraClient10g). Recomiendo usar el driver nativo de Oracle, ya que está optimizado y se corresponde exactamente con la versión de Oracle que acabamos de instalar.

Nota: En caso que se nos presente un mensaje de error con el título SQORAS32 y el mensaje An unsupported operation was attemped, se trata de un bug conocido cuya solución está en Metalink nota NR.5699495. Será necesario descargar un patch y reemplazar algunos archivos en el cliente Oracle. Es aplicable a los clientes 10.2.0.3.0 a 10.2.0.3.7 y las instrucciones vienen con el propio patch.

- En el diálogo siguiente, ingresar los datos de la conexión:

Data Source Name: Un nombre cualquiera describiendo nuestra conexión
Description: Opcional, puede ser nada
TNS Service Name: El mismo nombre de servicio en nuestro tnsnames.ora
User ID: Opcional, puede ser nada

- Pulsar Test Connection para ver si nuestra conexión funciona. Aparecerá un diálogo solicitando usuario y password. En mi caso colocaré scott/tiger. El mensaje Connection successfull deberá aparecer para poder continuar. De lo contrario, revisar el tnsnames.ora que está siendo usado, para ver si tiene la entrada correcta.

Luego de seleccionar OK en el diálogo principal, nuestra conexión ODBC está lista para ser usada.

Paso 2. Vincular en Access las tablas de Oracle

- Ingresar a Access y comenzar un nuevo proyecto en blanco.

- En el menú Insertar, elegir Tabla y Vinculación de tabla.

Aparecerá un diálogo como los de seleccionar archivos, pero tenemos primero que elegir en el combo Tipos de archivos, la opción Bases de datos (ODBC).

Luego se muestra el diálogo Select Data Source. Ir a la lengueta Machine Data Source y seleccionar la conexión ODBC creada en el paso anterior.

Somos solicitados para informar el usuario y password de la base de datos Oracle (scott/tiger).

Observar que se nos presentan todas las tablas y vistas a las que el usuario scott tiene acceso. Buscar aquellas que comienzan con el prefijo SCOTT y marcarlas: BONUS, DEPT, EMP y SALGRADE. Seleccionar OK.

- Access solicitará indicar para algunas tablas las columnas identificadoras (clave primaria). Eso dependerá si la clave está definida en la tabla original o no. En este caso vamos a ignorar el aviso simplemente seleccionando OK.

Listo! Las tablas ya están vinculadas en nuestro proyecto. Notar que el nombre de cada tabla asignado automáticamente por Access es [ESQUEMA]_[TABLA].

Paso 3. Crear las relaciones

Cuando se vinculan las tablas del esquema Oracle en Access, no se crean los relacionamientos entre ellas. Es necesario hacerlo manualmente, si queremos mantener el esquema de integridad de datos.

- En el menú Herramientas, ingresar a Relaciones.

- En el diálogo Mostrar Tabla, en la lengueta Tablas, seleccionar (presionando Control) las tablas SCOTT_EMP y SCOTT_DEPT, y pulsar Agregar. Las tablas se mostrarán en la ventana de relacionamientos, y podremos cerrar el diálogo Mostrar Tabla.

- Si la tabla SCOTT_EMP no muestra todas las columnas, agrandarla desde el vértice inferior derecho para permitir ver la columna DEPT.

- Hacer click (izquierdo) sobre la columna DEPT de SCOTT_EMP, y sin soltar, arrastrar la columna hacia la tabla SCOTT_EMP, soltándola encima de la columna DEPTNO (clave primaria de SCOTT_DEPT). Un nuevo diálogo se abrirá con la definición de la relación.

- Podemos elegir las propiedades de esta relación, en nuestro caso vamos a dejar todo por defecto, que corresponde a un INNER JOIN entre ambas tablas. Luego de seleccionar Crear, la relación entre las tablas será visible gráficamente en pantalla.

- Cerrar la ventana de relacionamientos y elegir Sí para guardar los cambios.

Paso 4. Crear formularios

Tenemos las tablas vinculadas en Access con sus relaciones, y de aquí para adelante depende exclusivamente de nuestros conocimientos en Access. Da lo mismo que sean tablas propias de Access o de Oracle con las que estemos trabajando. Podemos inclusive usar este mismo esquema para importar datos desde una tabla Oracle a una local.

Crearemos un formulario para cada una de las tablas importadas, para facilitar el mantenimiento de los datos a través de la aplicación. Explicaré cómo hacer los formularios de DEPT y EMP; para el resto es el mismo procedimiento.

- En la ventana principal de Access, ir a Formularios, y elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_DEPT en el combo de tablas, y seleccionar todos los campos disponibles (DEPTNO, DNAME, LOC) con el botón >>.

- Presionar Avanzar, elegir el layout 'Columna', el estilo 'Standard', y escribir el título 'Departamentos'. Seleccionar Concluir.

Ya está listo nuestro primer formulario donde daremos mantenimiento a los departamentos. Por defecto, todas las operaciones sobre los datos están permitidas. Si se desea, se pueden alterar las propiedades del formulario activando la caja Propiedades con Alt-Enter.

Ahora vamos al formulario de empleados.

- En la ventana Formularios, elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_EMP en el combo de tablas, y seleccionar todos los campos disponibles con el botón >>.

- Elegir las mismas opciones que en la tabla anterior, excepto por el título 'Empleados'.

Nuestro formulario de empleados está listo, pero vamos a introducir una pequeña mejora: que el campo DEPTNO en lugar de un texto numérico sea un combo list con los nombres de los departamentos, para evitar tener que recordar los números.

- Seleccionar en el menú Exibir, el modo Diseño.

- Marcar el campo DEPTNO en el formulario y borrarlo (pulsando la tecla DELETE)

- En la caja de herramientas (lateral izquierda) seleccionar la herramienta Caja de Combinación.

- Crear el combo en el lugar que eliminamos el campo numérico DEPTNO. Se abrirá automáticamente el asistente para caja de combinación.

- Seleccionar la primera opción para que el combo busque los datos a partir de una consulta y Avanzar.

- Elegir la tabla SCOTT_DEPT y Avanzar.

- Seleccionar los campos DEPTNO y DNAME y Avanzar

- Definir el orden de ordenamiento por DNAME (creciente) y Avanzar.

- Dejar seleccionado el checkbox Ocultar llave primaria y Avanzar.

- Dejar la opción Recordar valor para uso posterior y Avanzar.

- Si se desea modificar el rótulo, puede alterarse para 'DEPTO'.

- Concluir la operación.

Terminamos nuestro formulario para dar mantenimiento a los empleados.
Ahora nuestro campo de departamentos se actualiza dinámicamente a partir de la tabla SCOTT_DEPT.

Repetir el proceso de creación de formularios para el resto de las tablas, y estaremos aptos para insertar, borrar y alterar registros completamente a través de nuestra interface gráfica.

Paso 5. Crear un reporte

Crearemos un reporte para mostrar un ejemplo más de funcionalidad.

- En la ventana principal de Access, ir a Consultas, y elegir la opción Crear consulta en modo diseño.

- Cerrar la ventana Mostrar tabla y en el menú Exibir seleccionar SQL.

- Escribir la query:

SELECT e.empno, e.ename, e.job, e.sal, d.dname, s.grade
FROM scott_emp e, scott_dept d, scott_salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal

- Cerrar la ventana query y elegir Sí para grabar los cambios, asignando un nombre cualquiera para la consulta, por ejemplo 'Salarios de empleados por departamento'.

- En la ventana principal de Access, ir a Reportes, y elegir la opción Crear reporte usando el asistente.

- En el combo Tablas/Consultas elegir la consulta creada anteriormente, junto con todas las columnas disponibles en la consulta, y avanzar.

- En el nivel de agrupamiento, elegir DNAME (el departamento) y avanzar.

- Elegir el ordenamiento por orden de ENAME creciente.

- Dejar las opciones de diseño por defecto y avanzar hasta visualizar el reporte.

Nuestro reporte está listo luego de 10 minutos con cero esfuerzo de programación, simplemente usando una sencilla consulta SQL.

Combinamos la facilidad de uso y versatilidad de una herramienta como Access con el potencial de una base de datos Oracle. Las posibilidades dependen sólo de nuestra voluntad, pudiendo integrar planillas de Excel, gráficos, multimedia y mejorar la lógica de nuestra aplicacion con VBA (Visual Basic for Applications).

Aumento en esperas log file sync

2009-01-13T11:40:00.000-08:00

Se puede encontrar mucha "blografía" acerca de este clásico problema de tunning. El parámetro log file sync está directamente ligado al proceso interno LGWR que sincroniza el log buffer (área de memoria interna de la SGA) y redo logs en disco (archivos donde son registradas las transacciones), y puede revelar uno de dos problemas (sino ambos): un sub dimensionamiento de la base de datos, o un uso inadecuado de transacciones en las aplicaciones que la usan. En ambos casos, el tiempo de respuesta puede experimentar picos, deteriorando la performance.

Si bien alguien puede pensar en alterar la configuración de Oracle, es mejor comenzar a buscar posibles sesiones ofensoras. Es sabido que el 98% de las veces las aplicaciones son las principales reponsables de los problemas de performance que los usuarios reclaman, y no la base de datos.

Recientemente tuve la oportunidad de ver un problema de performance en una base de datos de producción que comenzó a paralizarse sin explicación aparente. He notado una tendencia de los gerentes a pensar los problemas de performance como problemas de hardware, y que adquiriendo nuevos recursos el problema se soluciona. Hay veces que puede ser cierto -no descartemos posibilidades- pero antes de cualquier conjetura y gasto innecesario, un poco de investigación es primordial. Para eso, Oracle nos provee de algunas herramientas de gran utilidad para identificar comportamientos anómalos, y causas que los producen.

En este caso, fue la herramienta de diagnóstico Statspack quien reveló la verdadera causa del problema.

El primer indicio en el reporte fue en los Top 5 wait events: log file sync al tope de la lista.
Eso significaba que Oracle estaba generando mucho más REDO, y tan agresivamente que la instancia dificilmente podía procesarlo. Existen errores típicos de programación PL/SQL que generan más REDO del necesario, uno de ellos es el clásico commit dentro del loop:

FOR rec IN (SELECT * from TABLA_GRANDE)
-- ..
-- procesamiento del registro rec
COMMIT;
END LOOP;
-- no es aquí donde debería hacerse el commit?

Analisando los resultados del reporte de Statspack, se pudo percibir que no había habido un aumento de COMMITs sino de ROLLBACKs.

Extracto de Statspack:

Load Profile
-----------



% Blocks changed per Read:    4.09    Recursive Call %:                92.21
Rollback per transaction %:   64.85       Rows per Sort:                14.35

....
user commits                                  96,732          161.2          0.4
user rollbacks                               175,390          292.3          0.6
...

El valor de Rollback per transaction indicaba que un 65% de las transacciones terminaban con rollback, y más adelante en el reporte se pudo verificar que efectivamente estaban ocurriendo el doble de rollbacks que de commits. Esto no es normal prácticamente en ninguna base de datos, al menos las que yo he visto. Leyendo reportes anteriores con actividad normal, se comprobó que efectivamente estos números había cambiado (anteriormente el índice era 4%). Sirvió para enfocarse y analisar de lleno las aplicaciones que se estaban ejecutando, y descubrir a través de las sesiones activas que una de ellas generaba mucho rollback. Sucede que el código que esta ejecutaba, había sido levemente modificado (3 líneas) y estaba generando errores inesperados con rollbacks de forma indiscriminada.

Las bases de datos deben servir a las aplicaciones y usuarios de la mejor forma posible. Para eso, no basta simplemente con ampliar recursos de memoria ni ajustar parámetros. Las aplicaciones que afectan la base de datos deben ser las primeras en ser tuneadas, y ni que hablar testeadas antes de colocarlas en producción. Ya sea en proyectos de desarrollo o mantenimiento, los tests de carga son necesarios. Un test de funcionalidad con una única sesión no revela este tipo de defectos, los cuales sumados en un ambiente real se convierten en un gran problema.

Haciendo las veces de investigador, el DBA debe recurrir a las herramientas de diagnóstico como Statspack, tkprof y vistas dinámicas. En caso de tratarse de código malicioso, será fundamental trabajar en equipo junto a los desarrolladores para hallar la fuente del problema.

Iniciar OEM 9i en Linux

2009-01-09T05:41:00.000-08:00

Cómo iniciar el Oracle Enterprise Manager (OEM) 9i en Linux:

cd $ORACLE_HOME
oemapp console

Indices de función para mejorar un LIKE

2008-12-31T05:35:00.000-08:00

Los índices de función son útiles para poder indizar condiciones aplicadas sobre columnas y así acceder rapidamente a los registros.
Un ejemplo típico es el uso de UPPER(columna) o el abuso de LIKE:

SELECT *
FROM my_big_table
WHERE  buffer LIKE 'FF-%';

En este caso, una barrida por toda la tabla (FULL SCAN TABLE) es inevitable.

El uso de LIKE compromete el uso de índices resultando en baja peformance.
Usando la función substr podemos evitar el LIKE en este caso, ya que tenemos siempre le mismo prefijo 'FF-'. Para mejorar la ejecución, podemos crear un índice de función como el que sigue:

CREATE INDEX ix_my_big_table_01 ON my_big_table(substr(buffer,1,4));

La consulta quedaría entonces de esta forma:

SELECT *
FROM my_big_table
WHERE substr(buffer,1,4) = 'FF-';

Para que el índice sea tenido en cuenta por Oracle, necesitamos contar con el privilegio de sistema QUERY REWRITE y dos parámetros de la base seteados: query_rewrite_enabled=TRUE y query_rewrite_integrity=TRUSTED

Ver también:
Mis índices no funcionan!
Indices condicionales
11g y sus índices invisibles

Limpieza de archivos en Unix

2008-11-29T10:44:00.000-08:00

Para eliminar archivos de log inutiles y evitar que nuestro file system se llene con el paso del tiempo, podemos usar el comando find y programarlo para executar diariamente en la crontab de root:

find  -name "*.dmp.log" -mtime +2 -exec rm -f {} \;

En el ejemplo estamos eliminando archivos temporales de log de mas de 2 días atras.

Asimismo, la misma idea puede usarse para compactar archivos con cierta antiguedad:

find  -name "*.dmp"  -exec gzip {} \;

Vea también:
Cómo obtener la fecha de ayer en Unix
Comandos útiles para Unix Shell
Cómo verificar archivos y directorios en Shell

Cómo saber si Pro*C está instalado en Oracle

2008-10-28T06:21:00.001-07:00

Pro*C es un producto que por ser pre-compilador no viene incluído en el sistema operativo, sino en la propia distruibución de Oracle. Es un componente opcional que se puede instalar utilizando el Oracle Universal Installer bajo el check [Oracle 9i Develpment Kit]/[Oracle Programmer]/Pro*C.
Generalmente utilizamos Pro*C en conjunción con algún compilador C nativo como gcc, el cual a diferencia del primero, es incluído por el sistema operativo.

Pro*C, cuando instalado en nuestra base de datos Oracle, es un ejecutable:

$ORACLE_HOME/bin/proc

Si el ejecutable no se encuentra en esa ubicación, verificar si efectivamente el componente no fue incluído en la instalación.

Ejecutar el OUI y confirmar si entre las opciones instaladas el componente no aparece listado.

Cuando operamos bases de datos remotamente, a veces no tenemos ambiente gráfico, y por razones de conectividad, ni siquiera nos es posible exportar el OUI hacia nuestra consola. Deberemos entonces leer el log de instalación de la base de datos (disponible en $ORACLE_BASE/oraInventory/logs), y buscar si una entrada como la siguiente es listada:

Pro*C/C++ 9.2.0.4

En caso de no encontrarlo, entonces deberemos instalarlo usando el OUI.

Application Express y su supervivencia

2008-10-27T07:33:00.000-07:00

Application Express, también conocida como APEX o HTMLDB, es la herramienta de desarrollo web de Oracle, que viene incluída en Oracle XE lista para usar. A la fecha de hoy, la versión actual (3.1.2) puede descargarse gratuitamente e instalarse en todas las versiones de la base de datos a partir de 9i R2 (incluída la versión XE, también gratuita). Aplication Express es un recurso gratuito tanto para desarrollar como para implantar, inclusive en ambientes de producción, no require de licencias ni posee limitaciones de distribución.

Desde 2004 a la fecha, la tecnología que ofrece APEX continúa siendo avanzada y cada vez más rica en recursos (AJAX, Web Services, CSS, Javascript, XML, etc) y es realmente rápido lograr resultados profesionales, pudiendo optar por diversas opciones de arquitectura y no teniendo que pagar absolutamente nada por ello.
¿Porqué entonces APEX continúa en las sombras, no logrando transformarse en una verdadera opción para los desarrolladores open source?

Pese a los esfuerzos de Oracle por disponibilizar gratuitamente esta tecnología, APEX nació con limitantes importantes que impiden su crecimiento en popularidad: no abundan los servicios de hosting gratuito de workspaces de Aplication Express (como sí los hay para otras tecnologías de libre uso). El libre licenciamiento de Oracle Aplication Express no es debidamente acompañado con los permisos de utilización del servidor de aplicaciones y base de datos requerida para estos fines. Para las empresas, no es redituable ofrecer APEX, ya que deben soportar las licencias de una de las bases de datos más costosas del mercado. APEX termina confinado a la privacidad de las intranets corporativas y los computadores portátiles.

¿No sería una buena movida estratégica para Oracle crear un nuevo tipo de licenciamiento que permita implantar bases de datos con mayor capacidad para ofrecer exclusivamente servicios de hosting APEX? ¿Porqué no difundir un producto, cuyo desarrollo le significa una cara inversión (300,000 líneas de código) destinada a satisfacer las necesidades de unos pocos? Con una comunidad tan reducida, el crecimiento de APEX es bastante más lento de lo que podría ser, dejándolo en amplia desventaja respecto a otros lenguajes de desarrollo web. Sin ir más lejos, fue así que Forms perdió vigencia, y cada vez más de estas aplicaciones emigran hacia otros lenguajes web, aún perdiendo parte de la seguridad y consistencia que Forms ofrece.

No se ven planes para intentar captar un público más amplio, sino de terminar de cerrar sus filas reclutando a los desarrolladores de Forms: la próxima versión de Aplication Express incluirá un convertidor de Forms a APEX, con lo que se facilitará la migración de estas aplicaciones a lo que será su sustituto inevitable.

Vea también:
Descargue la última versión de APEX
Cómo saber si tiene APEX instalado

Generando planillas Excel con SQL*plus

2008-09-19T13:08:00.000-07:00

Una de los comandos más interesantes de SQL*plus para formatear salida, es MARKUP HTML. Con el, podemos generar código HTML listo para mostrar en cualquier navegador.

Sintaxis básica: SET MARKUP HTML ON

Ejemplo:

SQL> set markup html on
SQL> spool tablas.html
SQL> select table_name, tablespace_name from user_tables;
SQL> spool off

Visto en el navegador, nuestra consulta de dos columnas es formateada como una tabla HTML.

Lista para Excel

Lo interesante de Excel es que permite ver páginas html como si fuesen planillas. Cuando abrimos el spool generado por SQL*plus, tenemos una planilla Excel pronta para usar.

Como el formato HTML generalmente es abierto por otras aplicaciones, podemos generar el spool como .xlt, y de esta forma abriremos el archivo en Excel con solamente dos clicks.

¿Cómo generar una fecha aleatoria con PL/SQL?

2008-08-01T23:01:00.000-07:00

El paquete dbms_random implementa algunas funciones aleatorias de utilidad para generar números y strings aleatorios, pero no para fechas. Para generar fechas al azar, podemos sumar o restar una cantidad X de días a sysdate. El único detalle respecta al período de fechas que queremos obtener, debemos ajustar adecuadamente el número de días que se van a sumar/restar a la fecha actual.

CREATE TABLE fechas_aleatorias (fecha DATE);

DECLARE
  sd    NUMBER;
  dias  NUMBER := 5000;  --max de dias que se suman/restan a sysdate
  dia   DATE;
BEGIN
  -- inicializacion del seed
  SELECT to_char(systimestamp,'FF') INTO sd FROM dual;
  dbms_random.initialize(sd);

  FOR i in 1..10000
  LOOP
    dia := TRUNC(sysdate) + dbms_random.value(-dias,dias);
    INSERT INTO fechas_aleatorias VALUES (dia);
  END LOOP;
END;

ORA-01440: column to be modified must be empty to decrease precision or scale

2008-07-26T10:12:00.000-07:00

ALTER TABLE t1 MODIFY (nr_pct NUMBER(5,2))
ORA-01440: column to be modified must be empty to decrease precision or scale

¿Cómo disminuir la longitud de una columna con datos y evitar el error?

Para este ejemplo, vamos a agregar una dificultad extra: la columna que queremos alterar es NOT NULL.

Presento dos formas de hacer esto con SQL*Plus:

FORMA 1: VACIANDO Y ALTERANDO

Respaldar la columna

CREATE TABLE t1_backup AS SELECT id, nr_pct FROM t1;

Setear la columna como NULL

ALTER TABLE t1 MODIFY (nr_pct NULL);

Vaciar la columna

UPDATE t1 SET nr_pct=NULL;

Ajustar el tamaño deseado de la columna

ALTER TABLE t1 MODIFY (nr_pct NUMBER(5,2));

Cargar los datos a la columna

UPDATE t1 SET nr_pct=(SELECT nr_pct FROM t1_backup WHERE t1_backup.id=t1.id);

Alterar la columna como NOT NULL nuevamente

ALTER TABLE t1 MODIFY (nr_pct NOT NULL);

Ventajas: Se preserva el orden de las columnas dentro de la tabla
Desventajas: Poco performante para tablas muy grandes (millones de datos), requiere crear un índice en la tabla t1_backup para mejorar la performance

FORMA 2: AGREGANDO COLUMNA EXTRA

Agregar una columna a la tabla con el tamaño deseado

ALTER TABLE t1 ADD (nr_pct_2 NUMBER(5,2) NOT NULL);

Cargar la nueva columna con los datos

UPDATE t1 SET nr_pct_2=nr_pct;

Eliminar la columna original

ALTER TABLE t1 DROP COLUMN nr_pct;

Renombrar la nueva columna al nombre de la original

ALTER TABLE t1 RENAME COLUMN nr_pct_2 TO nr_pct;

Ventajas: Más performante que la forma 1 para tablas grandes. Más fácil de realizar.
Desventajas: Sólo aplicable a 9i o superiores (8i no posibilita el renombrado de columnas).
La columna alterada quedará al final de la tabla si hacemos un DESC.

Cómo obtener la fecha de ayer en Unix

2008-07-08T11:33:00.000-07:00

Esta es una tarea sencilla en Linux. Para obtener el día anterior simplemente usamos la función date:

date --date='1 day ago'

Esta funcionalidad tiene varias formas que flexibilizan la tarea de obtener una fecha relativa del pasado o futuro. Sólo basta dar una mirada a la documentación del man.

El problema ocurre si usamos Sun Solaris: este sistema operativo no tiene una modalidad similar para date. Es frustrante para un programador casual como yo, que algo tan sencillo no esté implementado en el sistema operativo.
He visto algunos foros en la web donde se pueden encontrar diversos Shell scripts para calcular el día anterior dependiendo de si el mes tiene 30 o 31 dias, si es febrero, si es año bisiesto... y la verdad que la idea de tanta complejidad para algo tan simple no me satisface.

Una solución directa y fácil de obtener el dia anterior en Solaris es la siguiente: agendar en el programa crontab (o scheduler unix de preferencia) a las 23:59, un simple script que escriba la hora actual en un archivo:

#/bin/ksh
date > ayer.txt

Listo!
Ahora nuestros scripts pueden acceder en forma simple a la fecha de ayer.

Vea también:
Limpieza de archivos en Unix
Comandos útiles para Unix Shell
Cómo verificar archivos y directorios en Shell

Obtener la versión de la base desde PL/SQL

2008-07-02T10:52:00.000-07:00

Si queremos obtener cual es la versión de la base de datos para hacer una u otra cosa, podemos utilizar el paquete dbms_utility y el procedimiento db_version. El mismo nos retorna un string del tipo 9.2.0.4, el cual podemos consultar para programar a conveniencia.

  dbms_utility.db_version(v_version, v_compatible);

Una observación: cuidado con utilizar comandos que no existan en otras versiones, para eso está la compilación condicional introducida en Oracle 9i R2. Esta es una simple función que nos retorna la versión de la base actual que nos puede servir por ejemplo para reconstruir nombres de archivos que dependan de la versión utilizada.

El siguiente bloque PL/SQL es un ejemplo de como puede utilizarse db_version para condicionar la programación.


DECLARE
   v_version        varchar2 (32); 
   v_compatible     varchar2 (32); 
BEGIN  
  dbms_utility.db_version(v_version, v_compatible);
  
  if substr(v_version,1,2) = '10' then 
     ...
  elsif substr(v_version,1,1) = '9' then 
     ...
  elsif substr(v_version,1,3) = '8.1' then
     ...
  elsif substr(v_version,1,3) = '8.0' then
     ...
  elsif substr(v_version,1,1) = '7' then   
     ...
  end if; 

  ...
  
END;

Ver también:
Compilación condicional en 9i

SP2-1503 Unable to initialize Oracle call interface

2008-06-12T10:10:00.000-07:00

SP2-1503: No es posible inicializar la interfase de llamada Oracle
SP2-0152: ORACLE tal vez no esté funcionando adecuadamente

Acabamos de instalar nuestro cliente Oracle en Windows Vista, configuramos el tnsnames.ora y al querer conectarnos a la base de datos obtenemos el error.

Una posible explicación, es que acabamos de instalar un cliente Oracle para Windows XP, y este no es compatible con Vista. Es necesario descargar e instalar el software de Oracle específico para ese sistema operativo.

Si se ejecuta PL/SQL Developer en estas condiciones, luego de pedir los datos para el login retorna un error nada explicativo: un pequeño popup con mensaje vacío y un botón OK.

Un consejo: al obtener errores de conexión con herramientas externas, testear la conexión a través de SQL*Plus, para poder ver errores directos del sistema operativo o de la base de datos. Si SQL*Plus se conecta exitosamente, entonces el problema estará en la herramienta.

Cómo saber la última fecha en que un tablespace fue alterado

2008-06-04T06:23:00.000-07:00

El único lugar donde encontramos esta información es el alert log file. Este archivo registra todas las alertas críticas de la base de datos, incluídas las alteraciónes de tablespaces. Es solo mirar este archivo, hacer una búsqueda por el nombre del tablespace y obtendremos todos los datos de la modificación.

El alert log se localiza en la ruta: ${ORACLE_HOME}/admin/${ORACLE_SID}/bdump
y su nombre generalmente es: alert_${ORACLE_SID}.log

La restricción, es que únicamente el administrador de la base de datos tendrá acceso a este archivo.

Comandos útiles para Unix Shell

2008-05-30T08:11:00.001-07:00

Agrego algunos comandos útiles en Unix que siempre vienen bien tenerlos a mano. Hacen parte de las tareas diarias, en la creación de nuevos scripts de respaldo o en el tratamiento de logs que generan nuestros processos.

En este caso uso Solaris y ksh, pero dependiendo de la versión de Unix/Linux que estén utilizando, pueden variar algunos parámetros. De todas formas, la idea sirve para adaptarlos en cada ambiente.

Eliminar archivos gz con mas de 3 días de antiguedad
find . -mtime +3 -name "*.gz" -exec rm {} \;

Ejecutar un shell en background e independiente de la conexión
nohup ./shell.sh &
(este script retorna un número de job, puede ser útil anotarlo)

Para traer el proceso nuevamente al foreground
fg %(num_job)
(el número de job fue el que se retornó al colocar el proceso en background)

Colocar los nombres de archivo de un listado en un array
ARQS=$(cd ${DIR_RECARGA} ; ls *.dat)

Correr un script de sqlplus en Shell
#!/bin/ksh

sqlplus -s /nolog << !EOF
@$ORACLE_HOME/scripts/mi_script.sql

exit;
!EOF

Compactar todos los archivos de un directorio
#!/usr/sbin/ksh
for arch in `ls -t /*.arch tail +5`
do
gzip $arch
done

Ver y buscar en el contenido de un archivo comprimido con gzip
gzcat - CAT para archivos gz
gzgrep - GREP para archivos gz

Ver también:
Cómo verificar archivos y directorios en Shell
Limpieza de archivos en Unix
Cómo obtener la fecha de ayer en Unix

Incorporando Google Maps en Application Express

2008-04-20T04:51:00.000-07:00

Diversos artículos pueden encontrarse en Internet acerca de cómo implementar Google Maps en Oracle Application Express (APEX). Desafortunadamente, la mayoría son algo básicos y se limitan a cargar un mapa dentro de una región, no teniendo interacción con la base de datos.

En esta oportunidad vamos a ver cómo lograr desplegar puntos dinámicamente en un mapa a partir de datos almacenados en una tabla. Únicamente se requieren conocimientos básicos de Application Express, PL/SQL y Javascript.

Este artículo no pretende mostrar todas las funcionalidades de la API de Google Maps, sino resolver el problema puntual de programación en Oracle, luego dependerá de las habilidades Javascript del programador y de conocer a fondo las funcionalidades que brinda esta maravillosa API.

Aclarado esto, comencemos, paso a paso:

Paso 1: Tener APEX instalado.

Lo primero es tener APEX instalado, ya que necesitaremos la URL o IP para el paso siguiente.
Si tienen duda si APEX está instalado en su base de datos, ver mi artículo anterior.

Paso 2: Solicitar una clave en Google Maps.

Ir a la siguiente direccion http://code.google.com/apis/maps/signup.html para registrarnos y solicitar un código que necesitamos para utilizar el servicio. En el registro, se debe ingresar la URL donde será utilizado Google Maps.
Por ejemplo, si utilizamos el espacio gratuito Oracle Apex, podemos ingresar la URL http://apex.oracle.com/pls/otn/. Si se está desarrollando localmente (en la propia máquina) puede registrarse la IP 127.0.0.1, pero cuando finalice el desarrollo habrá que solicitar una nueva clave con la URL de producción, ya que Google requiere que el sitio registrado sea de acceso público.
El código generado será referenciado desde de nuestro script.

Paso 3: Manos a la obra con APEX!

En nuestra nueva aplicación APEX, como primer paso debemos crear una página.

Editar los atributos de la página y en el campo Enfoque de Cursor elegir el valor No enfocar Cursor.

En la sección Cabecera HTML, incluir el siguiente script reemplazando nuestra clave:

<script src="http://maps.google.com/maps?file=api&v=2&key=la_clave_de_google_maps&hl=es"
type="text/javascript"></script>
<script type="text/javascript">

//<![CDATA[

function load() {
if (GBrowserIsCompatible()) {
miMapa();
}
}

//]]>
</script>

En este javascript acabamos de definir la función load, la cual invoca a otra función miMapa que crearemos más adelante.

En el campo Atributo de Cuerpo HTML de Página, pegar lo siguiente:

onload="load();" onunload="GUnload();"

Se puede observar cómo en el evento onload de la página, se invoca a la función load incluída en el cabezal HTML.

Hecho esto, guardar los cambios efectuados en la página

Ahora queda la parte interesante, ya que deberemos crear la función miMapa la cual ejecutará la carga del mapa y los puntos de referencia extraídos de la base de datos. Esta función será generada por un bloque anónimo PL/SQL el cual será ejecutado cada vez que se carga la página.
Para este ejemplo, se creó una sencilla tabla geopuntos la cual contiene información sobre los puntos de referencia que queremos desplegar en nuestro mapa:

id_punto     NUMBER(10)    NOT NULL
latitud      NUMBER(18,15) NOT NULL
longitud     NUMBER(18,15) NOT NULL
descripcion  VARCHAR2(100)

La precisión de latitud y longitud es adecuada para almacenar coordenadas geográficas de Google Maps.
Cada punto puede enriquecerse con valiosa información como pueden ser imagenes, archivos, links, íconos, etc.
Se recomienda consultar la extensa documentación y tutoriales que brinda Google Maps en su sitio oficial.

En el área Presentación de Página, en la sección Procesos, vamos a implementar nuestro bloque PL/SQL.
Crear un nuevo proceso de tipo PL/SQL. En Atributos de Proceso, ingresar un nombre cualquiera y elegir el valor En Carga, Antes de Cabecera. Clickear siguiente, y en Proceso, incluir el siguiente bloque:

DECLARE
  CURSOR cr_puntos IS select * from puntosmapa;
BEGIN
  htp.p('<script type="text/javascript">');
  htp.p('//<![CDATA[');  
  
  htp.p('function crearMarca(lat, long, descr) {');
  htp.p('  var point = new GLatLng(lat,long);');
  htp.p('  var opts = {title: descr};');
  htp.p('  var mark = new GMarker(point,opts);');
  htp.p('  GEvent.addListener(mark, "click", function() {
    mark.openInfoWindowHtml(''<div><B>'' + descr + ''</B><BR>Lat: '' + lat +
 ''<BR>Long: '' + long + ''</div>'');');
  htp.p('  });');
  htp.p('return mark;');
  htp.p('}');  
  
  htp.p('function miMapa() {'); 
  htp.p('var map = new GMap2(document.getElementById("map"));');
  htp.p('map.addControl(new GLargeMapControl());');
  htp.p('map.addControl(new GMapTypeControl());');
  htp.p('map.addControl(new GOverviewMapControl());');
  htp.p('map.setCenter(new GLatLng(-22.915739,-43.22912), 11);');
  
  -- Recorro cursor e inserto los puntos
  FOR punto IN cr_puntos
  LOOP
    htp.p('var marker = crearMarca('||to_char(punto.latitud,'999.9999999999999999')||','
   ||to_char(punto.longitud,'999.9999999999999999')||',"'||punto.descripcion||'");');  
    htp.p('map.addOverlay(marker);');
  END LOOP;
  htp.p('}');

  htp.p('//]]>');
  htp.p('</script>');
END;

Este es el punto neurálgico de la aplicación: en este bloque PL/SQL resolvemos la consulta a la base de datos, la creación del mapa y la publicación de los puntos de referencia extraídos. Notar el uso de la función PL/SQL htp.p(). Así como DBMS_OUTPUT.PUT_LINE envia caracteres a la salida en pantalla, la función htp.p envía cadenas de caracteres al browser, y esto precisamente lo que necesitamos ya que queremos construir el Javascript a ser ejecutado en la carga de la página.

Puede apreciarse la sencillez de esta implementación, en pocas líneas crea un mapa, lo centra y carga los controles básicos de zoom. La particularidad en este caso es el FOR LOOP, el cual recorre un cursor definido sobre la tabla, y en cada iteración define una marca "publicando" Javascript en tiempo de ejecución. Cada referencia tiene un toolTip cuando se pasa el mouse por encima y un globo informativo que se abre al clickear sobre la marca.

Y eso es todo, tras guardar el proceso (dejar el resto de los valores por defecto), hemos concluído nuestra aplicación y ya está lista para ser explorada.

Las posibilidades son muchísimas, y cuanto más se profundice sobre la documentación, mejores funcionalidades podremos lograr en APEX.

Un ejemplo implementado

Hice una página de demostración para que puedan ver que realmente funciona. En mi tabla de datos tengo almacenados puntos turísticos de Río de Janeiro, ciudad donde vivo actualmente :)

Para saber las coordenadas geográficas de un punto en la tierra, podemos utilizar por ejemplo la página Geocoder.

Hay mucha documentación y ejemplos disponibles sobre Google Maps y realmente es fácil comenzar a obtener resultados sorprendentes.

Finalmente le agradezco a Emilio Le Mener por su ayuda en Javascript.

Espero que este tutorial haya sido útil y puedan comenzar a implementar sin problemas con APEX y Google Maps.

Probar la Demo

Ver también:
Google Maps API
Presentación Google Developer Day 2007

Cómo saber si Application Express (APEX) está instalado en Oracle

2008-04-18T00:05:00.000-07:00

Ejecutar la siguiente consulta en SQL*Plus con un usuario con rol DBA:

SELECT username
FROM dba_users
WHERE username IN ('FLOWS_010500','FLOWS_010600','FLOWS_020000','FLOWS_020200','FLOWS_030000', 'FLOWS_030100');

Si no se obtiene ninguna fila como resultado, entonces APEX no está instalado en la base de datos.

Oracle libera parches de seguridad para todos sus productos

2008-04-17T08:34:00.000-07:00

Este 15 de abril fueron publicados parches para todos los productos Oracle, incluída la base de datos, la E-Business Suite, PeopleSoft, Siebel y otros productos.

En cuanto a la base de datos, los parches abarcan todas las versiones desde la 9.0.1.5, y algunos de los bugs que se corrigen son vulnerabilidades graves que permiten explotar la base de datos aún sin autenticación. Este es un asunto de alerta máxima y no debe demorarse la decisión de actualizar el software. La instalación es sencilla y no toma mucho tiempo.

El Application Server 9i Release 1, version 1.0.2.2 también presenta vulnerabilidades en el JInitiator 1.3.1.14, Enterprise Manager 1.0.2.2 y el Enterprise Portal 9.0.4.3. Estos agujeros de seguridad pueden ser explotados sin necesidad de usuario y password.

Oracle recomienda fuertemente descargar estos patches (gratuitamente desde otn.oracle.com) y aplicarlos en todos los casos para evitar riesgos ya detectados.

Ver también:
Oracle Critical Patch Update Advisory - April 2008

Sobre comentarios y cómo actualizar secuencias

2008-04-14T05:27:00.000-07:00

Cuando se realizan cargas de datos en un esquema existente y no se utilizan las secuencias para asignar los valores de llave primaria, se produce un desfasaje entre identificadores y secuencias. Este desfasaje provoca errores de clave primaria ya existente, ya que los valores retornados por secuencia.NEXTVAL, pueden ya existir entre los datos recientemente cargados.

Para sincronizar las secuencias con los identificadores de llave primaria, puede crearse un script en PL/SQL el cual recree cada secuencia con su valor inicial según el máximo identificador de la tabla correspondiente. Para poder realizarlo, necesitamos primero tener una correspondencia entre tabla y secuencia, y en Oracle esta dependencia no existe.

Es una buena oportunidad para comenzar a adoptar la buena práctica de usar comentarios sobre los objetos. Muchas veces, en el proceso de creación de una base de datos, este paso es omitido y considerado innecesario. Supuestamente, los objetos "no necesitan describirse" fundamentandose que el diseño es lo suficientemente explicativo y que los creadores van a permanecer durante todo el proceso de desarrollo, por lo tanto no vale la pena perder el tiempo describiendo los objetos. ¿No suenan estas excusas a holgazanería?

No se deben ignorar estas máximas: 1) el diseñador puede abandonar el proyecto y 2) las personas externas que miren nuestro modelo pueden no entenderlo.

En este sentido, el responsable de base de datos debe velar porque el modelo quede totalmente documentado y que no dependa de las personas que lo crearon, en definitiva que sea lo más auto-explicativo posible.

Alguien puede considerar suficiente que la base de datos quede documentada en un diagrama o en un documento de word, pero ningún esfuerzo es suficiente si podemos hacer un poquito más. Los comentarios sobre objetos son almacenados en el diccionario de datos, y van a ser exportados junto a los objetos cuando estos sean migrados. Sin necesidad de consultar diagramas ni documentos, un programador en apuros puede hacer un DESCRIBE sobre una tabla en SQLPLUS, y saber al instante donde encontrar el dato que busca. Los comentarios ayudan a disipar las dudas más rapido, sobre todo a los que no están empapados en el modelo.

Ahora si, volviendo al tema inicial, ¿cómo relacionamos una tabla con su secuencia? Precisamente, colocando en el comentario de la columna, el nombre de la secuencia que se utiliza:

SQL> COMMENT ON cuentas.id_cuenta IS 'CUENTA_SEQ'

Acabamos de documentar que para la columna id_cuenta de la tabla cuentas, se debe utilizar la secuencia cuenta_seq. Observar que el efecto de esta sentencia es únicamente documental, todavía puede utilizarse la secuencia de la forma que se desee. La ventaja es que ahora podemos consultar la tabla de diccionario dba_col_comments en nuestros scripts.

A modo de nota, mencionamos que tambien se pueden comentar tablas y vistas, con esta sintaxis:

SQL> COMMENT ON cuentas 'Tabla para el registro de cuentas corrientes personales'

Finalmente, el bloque que actualiza las secuencias utilizando los comentarios sobre columnas.

DECLARE
  v_max NUMBER(10) := 0;
  v_desc varchar2(50);
BEGIN

  FOR t IN (SELECT u.table_name,
                   u.column_name,
                   uc.comments secuencia
            FROM   user_tab_columns  u,
                   user_col_comments uc
            WHERE  u.table_name = uc.table_name
            AND    u.column_name = uc.column_name
            AND    u.column_name LIKE 'ID_%')
  LOOP
      EXECUTE IMMEDIATE 'SELECT MAX(' || t.column_name || ') FROM ' ||
t.table_name INTO v_max;

      dbms_output.put_line('Eliminando secuencia '|| t.secuencia||'...');
      v_desc := 'DROP SEQUENCE '|| t.secuencia;
      EXECUTE IMMEDIATE 'DROP SEQUENCE '|| t.secuencia;

      EXECUTE IMMEDIATE 'CREATE SEQUENCE '|| t.secuencia ||' INCREMENT BY
1 START WITH '|| (NVL(v_max,0) + 1);
      dbms_output.put_line('Secuencia '|| t.secuencia||' recreada.');

  END LOOP;
END;

Se requiere el permiso CREATE SEQUENCE por parte del ejecutor de este script.

Más control sobre los LOOPS en 11g

2008-03-28T13:31:00.000-07:00

En PL/SQL podemos salir de un LOOP con la cláusula EXIT. Típicamente la usamos para la condición de salida de un loop:

 BEGIN
   LOOP
       ...  
       EXIT WHEN condición ;
   END LOOP;
   -- el control sigue aquí
   ...
 END;

Ahora la versión 11g agrega un nivel intermedio: CONTINUE. Con esta sentencia, podemos cancelar la iteración del loop actual y pasar a la siguiente, sin abortar el loop. Puede usarse simplemente CONTINUE o CONTINUE WHEN. En este último caso podremos evitar el uso de la sentencia IF para controlar la condición de salida de la iteración.

 BEGIN
   LOOP
     ...
    CONTINUE WHEN condicion;  --vuelve al comienzo del loop

    -- si condicion=true, esta parte no se ejecuta
    ...
   END LOOP;
 END;

Los programadores conservadores se preguntarán para que necesitamos esta sentencia, despues de todo siempre nos hemos arreglado con la cláusula IF-THEN-ELSE de modo de avanzar a la siguiente iteración. Es cierto, no lo necesitamos para hacer cosas que antes no podíamos, pero sí para simplificar el código y facilitar la lectura.

Un ejemplo con CONTINUE

Hay un caso típico donde CONTINUE puede beneficiarnos en la claridad de código para el seguimiento de la lógica: cuando tenemos que hacer múltiples validaciones antes de procesar un registro.

Imaginemos nuestro loop actual (muy simplificado), en donde para cada registro hacemos una serie de validaciones antes de procesarlo. Si una validación falla, debemos pasar al siguiente registro. Para resolver esta lógica, debemos crear una estructura anidada de IFs, ya que debo evitar que se ejecute el resto del loop si una de las validaciones falla.

LOOP
 EXIT WHEN condicion;
 i:=i+1;

 a := valida_1(T(i));
 IF (a) THEN
   b := valida_2(a);
   IF (b) THEN
     ....
     IF (c) THEN

        .....
                procesoRegistro(T(i));
        .....
     END IF;
   ELSE
       ....
   END IF;
 END IF;

END LOOP;

Notemos como los múltiples IF anidados complican el seguimiento del flujo. Muchas veces nos hemos visto complicados al tener que agregar una nueva validación, ya que hay que agregar un nuevo IF al loop, y debo mantener consistentes los correspondientes END IF que deshacen el nivel de anidación.

Ahora veamos como puede verse mejorado con CONTINUE-WHEN:

LOOP
  EXIT WHEN condicion;
  i:=i+1;
  
  a := valida_1(T(i));
  CONTINUE WHEN NOT a;

  b := valida_2(a);
  CONTINUE WHEN NOT b;

  ....

  procesoRegistro(T(i));
  
END LOOP;

La mejoría es evidente, conseguimos disminuir la anidación (a 1) y evitar el uso repetitivo de IF/END-IF, reduciendo la posibilidad de cometer errores de lógica al momento de modificar.

CONTINUE es un recurso que evidentemente solo puede utilizarse en Oracle 11g, sin embargo, siendo conscientes que nuestro código no va a compilar en versiones anteriores, es una práctica que contribuye a la legibilidad del código, con todos los beneficios que eso trae aparejado.

Ver también sobre 11g
11g y sus índices invisibles

NATURAL JOIN antinatural

2008-03-26T11:36:00.000-07:00

El NATURAL JOIN en Oracle tiene un bug. Las versiones 9i y 10g retornan extraños productos cartesianos cuando ejecutamos joins naturales con varias tablas a la vez.

El join natural
Un natural join toma las columnas de igual nombre entre dos tablas y las utiliza para realizar un join. ¿Cuál es el beneficio? No hay que nombrar las columnas en el join.
Si bien puede sonar fantástico el ahorrarnos de escribir las columnas en el JOIN, en Oracle el natural join produce resultados inesperados que veremos más adelante.

Un caso de prueba
Tengo tablas que describen clientes, órdenes, y libros. Deseo realizar una consulta joineandolas a todas.

SQL> desc customers
Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
CUSTOMER# NUMBER(4)        
LASTNAME  VARCHAR2(10) Y   
FIRSTNAME VARCHAR2(10) Y   
ADDRESS   VARCHAR2(20) Y


SQL> desc orders
Name Type Nullable Default Comments
--------- --------- -------- ------- --------
ORDER# NUMBER(4)
CUSTOMER# NUMBER(4) Y
ORDERDATE DATE Y


SQL> desc orderitems
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
ORDER# NUMBER(4)
ISBN VARCHAR2(10)
QUANTITY NUMBER(3) Y


SQL> desc books
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
ISBN VARCHAR2(10)
TITLE VARCHAR2(30) Y
PUBDATE DATE Y
COST NUMBER(5,2) Y

A considerar:

Todas las tablas tienen primary key, foreign keys, índices y estadísticas.
El JOIN que deseo hacer puede verse mirando las cuatro tablas desde arriba hacia abajo.
Las columnas con igual color determinan las llaves del JOIN.
La consulta que quiero realizar es --> Cuáles son los libros que ordenó JAKE LUCAS?

Resultado con INNER JOIN

SQL> SELECT isbn, title
 2  FROM ((customers INNER JOIN orders USING (customer#))
 3        INNER JOIN orderitems USING (order#))
 4        INNER JOIN books USING (isbn)
 5  WHERE firstname='JAKE' AND lastname='LUCAS';

ISBN       TITLE
---------- ------------------------------
2491748320 PAINLESS CHILD-REARING
9247381001 HOW TO MANAGE THE MANAGER
2491748320 PAINLESS CHILD-REARING

3 rows selected.

Es el resultado esperado. El cliente JAKE LUCAS ordenó los 3 libros que aparecen. ¿Qué sucede si ejecutamos la misma consulta pero con NATURAL JOIN?

Resultado con NATURAL JOIN

SQL> SELECT isbn, title
 2  FROM customers NATURAL JOIN orders
 3                 NATURAL JOIN orderitems
 4                 NATURAL JOIN books
 5  WHERE firstname='JAKE' AND lastname='LUCAS';

ISBN       TITLE
---------- ------------------------------
1059831198 BODYBUILD IN 10 MINUTES A DAY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
8843172113 DATABASE IMPLEMENTATION
8843172113 DATABASE IMPLEMENTATION
8843172113 DATABASE IMPLEMENTATION
3437212490 COOKING WITH MUSHROOMS
3437212490 COOKING WITH MUSHROOMS
3437212490 COOKING WITH MUSHROOMS
3957136468 HOLY GRAIL OF ORACLE
1915762492 HANDCRANKED COMPUTERS
...

64 rows selected.

Retorna 64 filas, algo inquietante. Se estará realizando un producto cartesiano? Veamos el plan de ejecución:

SQL> set autotrace traceonly explain

SQL> SELECT isbn, title
 2  FROM customers NATURAL JOIN orders
 3                 NATURAL JOIN orderitems
 4                 NATURAL JOIN books
 5  WHERE firstname='JAKE' AND lastname='LUCAS';


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=3 Bytes=204)
  1    0   NESTED LOOPS (Cost=9 Card=3 Bytes=204)
  2    1     MERGE JOIN (CARTESIAN) (Cost=9 Card=1 Bytes=57)
  3    2       MERGE JOIN (Cost=6 Card=1 Bytes=22)
  4    3         TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (TABLE) (Cost=2 Card=1 Bytes=18)
  5    4           INDEX (FULL SCAN) OF 'SYS_C00138991' (INDEX (UNIQUE)) (Cost=1 Card=20)
  6    3         SORT (JOIN) (Cost=4 Card=21 Bytes=84)
  7    6           TABLE ACCESS (FULL) OF 'ORDERS' (TABLE) (Cost=3 Card=21 Bytes=84)
  8    2       BUFFER (SORT) (Cost=6 Card=14 Bytes=490)
  9    8         TABLE ACCESS (FULL) OF 'BOOKS' (TABLE) (Cost=3 Card=14 Bytes=490)
 10    1     INDEX (RANGE SCAN) OF 'IX_BOOKS' (INDEX) (Cost=0 Card=2 Bytes=22)

Efectivamente, en la línea 2 del plan aparece un MERGE JOIN (CARTESIAN) lo cual nos indica que un producto cartesiano se llevó a cabo. Esto no debería suceder, ya que las columnas para vincular las tablas con NATURAL JOIN existen y fueron verificadas en la primera consulta, usando la cláusula USING del INNER JOIN.

Resultado con NATURAL JOIN variando las columnas del SELECT
Si bien lo anterior no era normal, es aún más desconcertante lo que obtenemos si variamos las columnas del select: ¡La cantidad de registros también varía!

SQL> SELECT #order
 2  FROM customers NATURAL JOIN orders
 3                 NATURAL JOIN orderitems
 4                 NATURAL JOIN books
 5  WHERE firstname='JAKE' AND lastname='LUCAS';


42 rows selected.

SQL> SELECT isbn
 2  FROM customers NATURAL JOIN orders
 3                 NATURAL JOIN orderitems
 4                 NATURAL JOIN books
 5  WHERE firstname='JAKE' AND lastname='LUCAS';


64 rows selected.

SQL> SELECT #customer
 2  FROM customers NATURAL JOIN orders
 3                 NATURAL JOIN orderitems
 4                 NATURAL JOIN books
 5  WHERE firstname='JAKE' AND lastname='LUCAS';


896 rows selected.

SQL> SELECT *
 2  FROM customers NATURAL JOIN orders
 3                 NATURAL JOIN orderitems
 4                 NATURAL JOIN books
 5  WHERE firstname='JAKE' AND lastname='LUCAS';

3 rows selected.

Unicamente cuando seleccionamos todas las columnas (*) obtenemos el resultado correcto. ¡Esto debería desalentar a cualquiera a usar NATURAL JOIN!

Bug reconocido
La verdad es, que este bug fue reconocido por Oracle en Metalink, el soporte oficial. Lo que se informa es, que este bug se cree que será eliminado a partir de la versión 11.2. Pero eso deja de ser un problema si eliminamos hoy el uso de natural join, definitivamente.

Nunca usar NATURAL JOIN
El NATURAL JOIN es una sentencia que está en Oracle desde la versión 9i en un esfuerzo por cumplir con el estándar ANSI SQL. Dentro del propio mundo del SQL, el NATURAL JOIN es uno accesorio inútil como pocos, del cual podemos prescindir totalmente.

Más allá de que no funcione correctamente en este RDBMS, imaginemos que si lo hace y que en Oracle 11g R2 tenemos este bug solucionado: el NATURAL JOIN funciona de maravillas.
Ahora, que sucede con mi procedimiento si el día de mañana agregan una columna de nombre quantity a mi tabla books?
Como orderitems ya tiene una columna quantity, se van a retornar resultados inesperados, ya que la tablas se van a combinar por isbn y quantity (lo cual no tiene sentido). Un cambio leve en las estructuras modifica el comportamiento de mi JOIN, y en este caso no se trata de un bug sino que sería bastante lógico que lo hiciera.

Usar NATURAL JOIN también nos quita claridad en nuestro código: cada vez que lo encontramos en una consulta compleja perdemos tiempo buscando en las estructuras cuáles son las columnas que coinciden.

Lo mejor en todos los casos es usar INNER JOIN o la notación original de Oracle por medio de comparadores en el where. Con ellas el programador explicita cuáles son las columnas que deben entrar en juego, sin dejar nada librado al azar.

En conclusión, usar NATURAL JOIN es totalmente desaconsejado en SQL, ya que nuestras consultas dependen del nombrado de columnas en las tablas relacionadas. Un cambio insignificante como agregar una columna a una tabla, podría hacer dejar de funcionar mi aplicación.

Compilación condicional en 9i

2008-03-24T14:51:00.000-07:00

La compilación condicional permite indicarle al compilador los fragmentos de código que debe compilar en nuestro código PL/SQL, según las condiciones que el programador indique utilizando directivas especiales.
Ahora, si bien la compilación condicional fue presentada con Oracle 10g R2, es también posible contar con esta característica en 9i y 10g R1, gracias a la aplicación de patches sobre la base de datos.

En Oracle 10g y superiores
En Oracle 10g ya viene incorporada a partir de la versión 10.1.0.4 y activada por defecto, por lo tanto no debemos hacer nada para comenzar a utilizarla.

En Oracle 9i
Para contar con ella en 9i debemos primero tener instalado el patch 9.2.0.6 o superiores. Luego debemos setear el parámetro indocumentado _plsql_conditional_compilation=TRUE. Este parámetro es del sistema y no es dinámico, por lo que el DBA deberá incluirlo en el archivo de parámetros y reiniciar la instancia.

En Oracle 8i e inferiores
No es soportada.

Una aplicación de la compilación condicional bastante práctica y recomendable es la de hacer que nuestro código PL/SQL compile en cualquier versión, utilizando los features propios de cada release cuando es posible, todo en el mismo código. Esta buena práctica tiene dos objetivos: el primero es hacer que nuestro paquete sea reutilizable (por ejemplo un paquete de funciones de uso general), y el segundo que nuestro código no genere un impacto al momento de migrar de versión.

Un ejemplo de compilación condicional
En este ejemplo, vamos a realizar un procedimiento para hacer un spool de determinadas cuentas que cumplen con un patrón en su código.
La compilación condicional va a entrar en juego con la siguiente lógica:
-Si la base es 9i, ejecuta SQL con invocaciones a SUBSTR para verificar el patrón
-Si la base es 10g, ejecuta SQL con expresiones regulares ya que es más performante

Primero compilamos la especificación de nuestro paquete, que no tiene nada de especial:

CREATE OR REPLACE PACKAGE pack_cuentas
IS
PROCEDURE list_cuentas (p_max IN VARCHAR2);
END pack_cuentas;
/

Luego compilamos el body, donde está la diferencia:

CREATE OR REPLACE PACKAGE BODY pack_cuentas
IS
PROCEDURE spool_cuentas (p_max IN VARCHAR2)
IS
BEGIN
 $IF DBMS_DB_VERSION.VER_LE_9
 $THEN
    FOR cue IN (SELECT cod_cuenta FROM cuentas
             WHERE activo='Y' AND SUBSTR(cod_cuenta,1,2)='00'
             AND SUBSTR(cod_cuenta,3,1) IN ('0','1','2','3')
             AND SUBSTR(cod_cuenta,LENGTH(cod_cuenta),-1)='A')
    LOOP
       dbms_output.put_line(cue.cod_cuenta);
    END LOOP;
 $ELSE
    DECLARE
       v_pattern VARCHAR2(20) := '00[0-'||p_max||']-[0-9]+A';
    BEGIN
       FOR cue IN (SELECT cod_cuenta FROM cuentas
             WHERE activo='Y' AND REGEXP_LIKE (cod_cuenta,v_pattern))
       LOOP
          dbms_output.put_line(cue.cod_cuenta);
       END LOOP;  
    END;
 $END
END spool_cuentas;
END pack_cuentas;
/

Las directivas especiales $IF $THEN $END hacen el truco. Observar que este mismo código se compila en Oracle 9i, donde la función REGEXP_LIKE retornaría un error de compilación, sin embargo el fragmento de código que no corresponde a la versión ni siquiera es analizado por el compilador.
Para poder diferenciar entre las versiones 9i, 9iR1, 9iR2, 10g, 10gR1 y 10gR2 contamos con el paquete DBMS_DB_VERSION, el cual incluye constantes para cada versión, como la que usé en el ejemplo. En el ejemplo usamos la constante ver_le_9 que implica 'menor o igual a 9i', pero también tenemos para indicar menor o igual a un release como por ejemplo ver_le_9_2.

CREATE OR REPLACE package dbms_db_version is
version constant pls_integer := 10; -- RDBMS version number
release constant pls_integer := 2; -- RDBMS release number
ver_le_9_1 constant boolean := FALSE;
ver_le_9_2 constant boolean := FALSE;
ver_le_9 constant boolean := FALSE;
ver_le_10_1 constant boolean := FALSE;
ver_le_10_2 constant boolean := TRUE;
ver_le_10 constant boolean := TRUE;
end dbms_db_version;
/

Ahora podremos arreglar nuestro viejo código para que sea más eficiente en las versiones siguientes y que no tengamos que hacer cambios al momento de migrar, en definitiva adelantarnos y minimizar el retrabajo en el futuro.

Cómo verificar archivos y directorios en Shell

2008-03-19T13:47:00.000-07:00

Estos son mis recursos favoritos cuando necesito comandos en Unix para verificar la existencia de carpetas, saber si existen archivos o si están vacíos. Acudo a ellos comúnmente en scripts de respaldo de bases de datos, antes de invocar a RMAN o EXP bajo Linux y después de que se realizan los mismos para verificar los resultados.
También recurro al tamaño en bytes de archivos o de todos los archivos de un directorio, especialmente para generar reportes o logs en nuestros scripts.

Verificar si una carpeta existe

  if [ -d archivo ]
then
# Codigo si existe
echo existe
else
# codigo si no existe
echo no existe
fi

Verificar si un archivo de salida no esta vacío

  if [ -s archivo ]
then
# Codigo si existe y no es vacio
...
else
# codigo si no existe o es vacio
...
fi

Verificar si un archivo es escribible

  if [ -w archivo ]
then
# Codigo si existe y es escribible
...
else
# codigo si no existe o no es escribible
...
fi

Verificar la integridad de un archivo comparando checksum

  check1=`md5sum archivo_origen | cut -d' ' -f1`

check2=`md5sum archivo_destino | cut -d' ' -f1`

if [ $check1 -eq $check2 ]
then
# Integridad del archivo correcta
...
else
# Error de checksum
...
fi

Obtener el tamaño de un archivo en bytes

  x=`du -m archivo | cut -f1`

Sumar la cantidad total de bytes en un directorio

  suma=0
for arch in *
do
if [ ! -d $arch ]
then
  tamano=`du -b $arch | cut -f1`
  let suma=$suma+$tamano
fi
done
echo $suma

Ver también
Comandos útiles para Unix Shell
Limpieza de archivos en Unix
Cómo obtener la fecha de ayer en Unix

Error ORA-942 while gathering statistics

2008-03-17T08:49:00.000-07:00

Este error puede suceder cuando un usuario no DBA quiere setear AUTOTRACE en su sesión.

SQL> set autotrace traceonly statistics;
Error ORA-942 while gathering statistics

SP2-0611: Error enabling STATISTICS report

Si es un usuario con acceso limitado, entonces es de esperar que no tenga grants sobre las vistas que se necesitan para leer las estadísticas generadas por AUTOTRACE. Se requieren permisos explícitos de lectura sobre las vistas v$_session, v_$sesstat y v_$statname.

Para facilitar la asignación de estos grants, el administrador cuenta con un script para crear un rol especial y así poder extenderlo a los usuarios.

Script:
$ORACLE_HOME/sqlplus/admin/PLUSTRCE.SQL

Cómo hacer para que un usuario pueda usar AUTOTRACE

1. En el sistema operativo, posicionarse sobre el directorio con el script.

    lfer@linux>  cd $ORACLE_HOME/sqlplus/admin

2. Loguearse a SQL*plus con usuario 'AS SYSDBA'

3. Ejecutar en SQL*plus:

    SQL> @PLUSTRCE.SQL

4. Otorgar el rol PLUSTRACE a todos los usuarios que deseen usar AUTOTRACE:

    grant PLUSTRACE to USUARIO;

Ver también:
SQLPlus Users Guide and Reference 10g sobre AUTOTRACE

Cómo detectar caracteres extraños o no imprimibles

2008-03-12T10:52:00.000-07:00

Muchas veces pasa que en las cargas de datos aparecen caracteres de control como ^M o símbolos ilegibles, y no es deseable que estos queden almacenados entre los datos.
¿Cómo detectar si existen?
Es sencillo si contamos con expresiones regulares en SQL como en 10g, pero en 9i igualmente podemos crear una función que recorra cada uno de los caracteres y verifique si pertenece al juego de caracteres 'raros'.
Mostraré una sencilla implementación compatible con todas las versiones de Oracle, pero puede considerarse utilizar compilación condicional para utilizar expresiones regulares cuando sea posible.
El código:

CREATE OR REPLACE FUNCTION buscar_no_imprimible(v_cadena VARCHAR2) 
RETURN BOOLEAN IS
  v_ret BOOLEAN := FALSE;    
  v_iter NUMBER := 1;    
  v_ascii_min NUMBER := 33;  
  v_ascii_max NUMBER := 126;
BEGIN
  WHILE (v_iter <= LENGTH(v_cadena))
    LOOP
      IF ASCII(SUBSTR(v_cadena,v_iter,1))
      NOT BETWEEN v_ascii_min AND v_ascii_max THEN
        v_ret := TRUE;
     EXIT;   
      END IF;        
      v_iter := v_iter + 1; 
    END LOOP;    
    RETURN(v_ret);
END buscar_no_imprimible;

Esta función booleana retorna TRUE si la cadena contiene un caracter no imprimible, FALSE en caso contrario. Una rápida mirada en una tabla ASCII nos dice que el rango de caracteres válidos se encuentra entre las representaciones decimales 33 y 126.

Si se desean considerar caracteres del ASCII extendido (por ejemplo vocales con acentos), bastará ajustar el procedimiento para incluir el conjunto de caracteres deseado.

Ver también:
Como eliminar acentos del español
Limpiando código DDL
Compilación condicional en 9i

11g y sus Indices Invisibles

2008-03-10T13:40:00.000-07:00

Los índices invisibles, disponibles a partir de Oracle Database 11g, según la documentación oficial, son una interesante alternativa al borrado de índices o seteo como "unusable". Pero, son realmente una alternativa?

Según la información disponible, un índice invisible será ignorado por el optimizador al momento de evaluar los planes de ejecución, excepto que alteremos el valor de sesión (o sistema) OPTIMIZER_USE_INVISIBLE_INDEXES, pero en todo momento el estado del índice será válido. Esto nos brinda múltiples beneficios.

Un índice unusable es una forma sutil de dejar un índice "fuera de combate" temporalmente en TODAS las sesiones. Un índice invisible en cambio es selectivo: podemos verlo (y usarlo) en una sesión mientras en el resto hacer como si no existiera.

Un índice invisible puede volverse visible sin costo alguno, con una simple alteración del mismo: ALTER INDEX mi_indice VISIBLE. Un índice unusable en cambio deberá ser reconstruído, ya que las actualizaciones de datos sobre la tabla no fueron reflejadas mientras el índice estuvo en ese estado. Los índices invisibles SIEMPRE son mantenidos por Oracle con cada sentencia DML.

Con estas afirmaciones, podemos concluir que los índices invisibles llegan para sustituir el estado unusable?
-No! El propósito es completamente diferente. Los índices invisibles evitarán, por ejemplo, que tengamos que 'inutilizar' índices para poder realizar performance tunning, pero en ciertos casos particulares seguiremos necesitando del estado unusable.

Cuando tengamos que realizar una carga masiva de datos, tendremos que alterar el índice como 'unusable' para realizar la carga eficientemente, ya que la invisibilidad de índices de 11g tiene efecto sobre las consultas SELECT y no sobre las demás sentencias DML. El índice invisible continúa aportando overhead en cada modificación de datos.

La clave de este novedoso estado está en el testeo de consultas. Por ejemplo, podremos probar cómo se comporta un SELECT con determinado índice, sin que el ambiente en el cual lo estoy probando se entere. Creo un índice invisible, seteo el valor de OPTIMIZER_USE_INVISIBLE_INDEXES como true, y testeo mi aplicación sin efectos colaterales.

Otro beneficio, razonando en forma opuesta: ¿Cómo funcionaría mi aplicación si determinado índice no existiera?
Alteramos el índice como 'invisible', seteamos en nuestra sesión OPTIMIZER_USE_INVISIBLE_INDEXES = false, y probamos. Los demás usuarios mientras tanto, continuarán utilizándolo normalmente.

Los índices invisibles son un valor agregado a nuestra forma de trabajo, ya que nos proveen transparencia sobre el ambiente. Nos permiten realizar nuestras pruebas tranquilos de que nadie está siendo afectado.

Ver también:
Indices condicionales
Mis índices no funcionan!
Indices de función para mejorar un LIKE
Oracle Database Administrator's Guide 11g sobre índices invisibles

SOUNDEX en español

2008-03-08T13:19:00.000-08:00

SOUNDEX, función que ha estado presente en Oracle desde tiempos remotos, es un clásico algoritmo fonético utilizado para indexar nombres propios, asociando palabras que suenen igual a pesar de ocasionales diferencias en su escritura.

El algoritmo SOUNDEX fue concebido teniendo en cuenta la fonética del idioma inglés, por lo que hace inadecuado su uso en el castellano. Realizando adaptaciones propias del idioma español, implementé una versión en PL/SQL llamada SOUNDESP, la cual respeta la esencia del algoritmo original.

Los pasos básicos son:

Retener la primera letra de la cadena. Tener en cuenta las letras dobles como CH y LL.
Remover todas las ocurrencias de las letras siguientes a partir de la segunda posición: a, e, i, o, u, h, w, y (cuando suena como vocal i )
Asignar números a las siguientes letras (luego de la primera):
- b, f, p, v = 1
- c, g, j, k, q, s, x, z = 2
- d, t = 3
- l = 4
- m, n = 5
- r = 6
- ll, y, ch = 7
Si hay números consecutivos, dejar solamente uno en la serie.
Retornar los cuatro primeros caracteres, si son menos de cuatro completar con ceros.

SOUNDESP es un proyecto abierto y es bienvenido cualquier comentario para mejorar su implementación.
Ultima versión: 1.8 (04-MAR-2010)

Descargar Código Probar SOUNDESP

NOTA: Para descargar el código correctamente, haga click derecho y elija Guardar destino

Ver también:
Wikipedia: Soundex

Insertar o actualizar un único registro con MERGE

2008-02-27T11:36:00.000-08:00

La versión 9i de Oracle Database traía entre sus SQL features, la incorporación de una novedosa sentencia: MERGE (ahora parte del estándar ANSI SQL). Hasta ese momento, cuando se quería insertar/actualizar datos en una tabla no vacía, había que revisar si el registro ya existía para aplicar INSERT o UPDATE, manejándolo proceduralmente con algún lenguaje, por ejemplo PL/SQL. Con MERGE, podemos tener esta lógica en una única sentencia SQL, simplificando el código y haciendo la tarea más performante.

La sintáxis de MERGE está pensada para que la fuente de datos que se va a insertar sea una tabla o una consulta, de esta manera:

MERGE INTO [tabla_destino]
USING ([tabla o vista o consulta])
ON ([condición de existencia de registro])
WHEN MATCHED THEN [sentencia de actualización]
WHEN NOT MATCHED THEN [sentencia de inserción];

Hay veces en que los datos a incorporar no provienen de ninguna tabla, sino que se trata de un único registro enviado por una aplicación (por ejemplo a través de parámetros). Una forma de poder aprovechar la sentencia MERGE en estos casos, es construir una consulta sobre la tabla DUAL que convenientemente contiene una única fila.

Partiendo de una tabla tabla_destino, donde queremos hacer la actualización de datos:
CREATE TABLE tabla_destino (a int, b varchar2(10));

Si:

Nuestro registro a insertar es {param_a, param_b}
La condición de que el registro exista es la columna a
Lo que queremos actualizar en caso que el registro exista es la columna b

...entonces usamos MERGE así:

1 MERGE INTO tabla_destino td
2 USING (SELECT param_a a FROM dual) d
3 ON (td.a = d.a)
4 WHEN MATCHED THEN UPDATE SET td.b = param_b
5 WHEN NOT MATCHED THEN INSERT (td.a, td.b) VALUES (param_a, param_b);

En la línea 2 (USING) seleccionamos el registro que participa en la condición que el registro exista.
En la línea 3 especificamos cual es la condición que se considera que el registro ya existe
Las líneas 4 y 5 actualizan o insertan el registro.

Una forma simplificada y equivalente es la siguiente:

MERGE INTO tabla_destino td
USING dual d
ON (td.a = param_a)
WHEN MATCHED THEN UPDATE SET td.b = param_b
WHEN NOT MATCHED THEN INSERT (td.a, td.b) VALUES (param_a, param_b);

Se recomienda el uso de MERGE ya que es una única sentencia SQL y naturalmente está optimizada por el motor. Hay excepciones como es el caso de cargas masivas de datos con millones de registros donde la performance de MERGE se degrada. Allí existen otras alternativas más eficientes.

Ver también:
Oracle 9i SQL Reference para MERGE
Oracle 10g SQL Reference para MERGE
Update condicional

Patches críticos en enero para todos los productos Oracle

2008-01-29T04:39:00.000-08:00

Acaban de ser liberados patches críticos que afectan a los principales productos de Oracle: Oracle Database (9i, 10g, 11g), Application Server (9, 10g R2 y R3), Collaboration Suite 10g, E-Bussiness Suite (11i y 12) y People Soft PeopleTools.

Se recomienda fuertemente que estos patches sean aplicados, ya que reducen potencialmente las posibilidades de ataques recientemente descubiertos.

En la Base de Datos se descubrieron 8 puntos vulnerables en los siguientes módulos: XML DB, Advanced Queuing, Oracle Spatial, Ultra-Search y en el propio CORE de 11g.
En el Application Server los fixes afectan al J-Initiator, BPEL, Forms y JDeveloper 10g, Internet Directory.
Como parte de la Collaboration Suite, Oracle Ultra-Search 10.1.2 es el único componente afectado.

Vea también:
Oracle Critical Patch Update Advisory - January 2008
Oracle Critical Patch Update Advisory - April 2008

Sesiones de Búsqueda

2008-01-28T12:33:00.000-08:00

Ya lo habiamos comentado hace algún tiempo, la consola de administración de Oracle 10g en español tiene algunos errores de traducción. Este es de los más feos que he visto.

Si en algún momento buscan el listado de sesiones de usuario en la instancia, no esperen hallarla con un nombre coherente.

Les doy una pista, en la version en inglés se llega a a través del link Session Search...

Espera encontrar Búsqueda de Sesiones? Mal!, no realice la traducción correctamente. Hallará en su lugar Sesiones de Búsqueda. Una preciosura. Ahora ejecute una búqueda en blanco para obtener el listado.

Cadena de conexión con jdbc thin

2007-12-26T06:06:00.000-08:00

Error al testear la conexión JDBC en JDeveloper 10g ->

Test Failed: La dirección URL de Oracle especificada no es válida

Un método que puede solucionar este frustrante mensaje es usar la cadena de conexión que incluye la descripción del servicio (tal como la colocaríamos en el archivo tnsnames.ora)

jdbc:oracle:thin:@(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = puerto))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = base_de_datos)
)
)

Si todavía no funciona es recomendable probar la conexión a través de un cliente Oracle, configurando el archivo tnsnames.ora. Al momento que esta conexión logre conectarnos, solo resta reemplazar en la cadena de conexión la misma descripción del servicio del tnsnames (arriba en azul).

Indices condicionales

2007-11-11T17:18:00.000-08:00

Hay tablas para las cuales desearíamos hacer valer condiciones de unicidad pero dadas ciertas condiciones que dependan de alguna otra columna de control.

Un ejemplo típico se da cuando mantenemos registros en una tabla con borrado lógico, y queremos que exista un único registro activo (pudiendo aparecer el registro inactivo más de una vez). Digamos que tenemos la tabla t, con dos columnas fundamentales: un id y un indicador de registro activo Y/N. Queremos que el Id sea único solamente cuando Activo es 'Y'.

Muchas veces, mucha gente se enfrenta a este problema y lo resuelve creando una tabla auxiliar para hacer valer la condición de unicidad, almacenando en ella las tuplas que no desea repetir para lograr dicho control.
Todo el problema que implica crear (y mantener) estructuras adicionales y la complejidad de manipulación, puede evitarse utilizando lo que siempre estuvo al alcance: el índice de función y la sentencia CASE.

SQL> CREATE TABLE t (sec int primary key, id VARCHAR2(100), activo VARCHAR2(1));

Table created.

SQL> CREATE UNIQUE INDEX ix_t ON t
2 (CASE WHEN activo='Y' THEN id ELSE NULL END);

Index created.

SQL> insert into t values (1, 'COD1', 'Y');

1 row created.

SQL> insert into t values (2, 'COD1', 'N');

1 row created.

SQL> insert into t values (3, 'COD1', 'N');

1 row created.

SQL> insert into t values (4, 'COD1', 'Y');
insert into t values (4, 'COD1', 'Y')
*
ERROR at line 1:
ORA-00001: unique constraint (LFER.IX_T) violated

Los índices condicionales no son un feature especial de Oracle, sino que se construyen utilizando los mismos índices de función que usualmente definimos para todas las filas de una tabla. En este caso, la función CASE permite que el índice único se construya sobre un subconjunto de registros, aquellos en el que el campo activo sea igual a 'Y'.

Ver también:
11g y sus índices invisibles
Mis índices no funcionan!
Indices de función para mejorar un LIKE

Variables en SQL*Plus (parte 3)

2007-11-07T16:18:00.000-08:00

Anteriormente repasando variables en sqlplus, vimos las variables de sustitución y las de usuario. Finalmente llegamos al tercer tipo de variables, las variables bind (o de ligadura).

VARIABLES BIND

Estas variables tienen la particularidad de poder ser definidas en sqlplus, usadas y/o asignadas dentro de nuestros programas PL/SQL y luego leídas una vez terminado el programa. Son convenientes cuando queremos tener comunicación entre scripts de sqlplus y bloques de PL/SQL.

Las variables bind se definen así:

SQL> VARIABLE bvar NUMBER;

Pueden utilizarse también los tipos: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, BINARY_FLOAT y BINARY_DOUBLE

Si queremos asignar estas variables antes de llamar a nuestros bloques de PL/SQL, debemos hacerlo desde otro bloque BEGIN END o simplemente usando la sentencia EXEC:

SQL> exec :bvar := 1;

PL/SQL procedure successfully completed.

Las variables dentro de PL/SQL se referencian con dos puntos seguidos del nombre de la misma.
Ahora ya podemos utilizar las variables dentro de nuestro programa e imprimir el resultado:

SQL> BEGIN
   2   IF :bvar = 1 THEN
   3     :bvar := 0;
   4   ELSE
   5     :bvar := 1;
   6   END IF;
   7 END;
   8 /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
0

SQL> /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
1

Y con esto culminamos el repaso de variables en sql*plus. Finalmente como complemento, veremos brevemente los cursores referenciales los cuales son también útiles para recuperar un conjunto de tuplas de nuestros SELECTs, desde los programas PL/SQL hacia fuera, y así tener más flexibilidad.

REFCURSORS

Se declaran como una variable bind, pero colocamos REFCURSOR en el tipo:

SQL> VARIABLE rcur REFCURSOR;

SQL> begin
2 OPEN :rcur FOR select segment_name, segment_type from user_segments order by segment_name;
3 end;
4 /

PL/SQL procedure successfully completed.

A diferencia de los cursores tradicionales que se abren y requieren del uso de fetch, al abrir estos cursores bind ya son ejecutados automáticamente y sus resultados almacenados en memoria. Solo resta imprimir las filas de la consulta.

SQL> print rcur

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
ACUMULADO_CONSUMO    TABLE
BIG_TABLE            TABLE
BIG_TABLE_PK         INDEX
CLIENTE              TABLE
COUNTRY_C_ID_PK      INDEX
CUENTA               TABLE
INVENTARIO           TABLE
IX_T                 INDEX
MUNICIPIO            TABLE
NDX_ACUMCON          INDEX
NDX_ACUMRENT         INDEX
NDX_ACUMTERC         INDEX
NDX_ACUMVAR          INDEX

13 rows selected.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 2) - Sobre variables de sustitución

Update condicional

2007-10-23T20:25:00.000-07:00

¿Cómo se hace un UPDATE condicional?
Digamos que tengo una tabla t con 3 campos a, b y c. Quiero que si a=1 entonces actualizo b con algún valor, y si a=2 actualizo c.

Alguien de mi equipo se encontró con este dilema y lo resolvió de forma muy práctica: dos updates, uno para actualizar aquellos valores con a=1 y otro para actualizar los a=2:

1 BEGIN
2    UPDATE t
3   SET b=valor
4   WHERE a=1;
5
6   UPDATE t
7   SET c=valor
8   WHERE a=2;
9 END;

Otro programador del equipo refutó: -¿Para qué ejecutar dos sentencias update si en su lugar puedes hacer una?

1 BEGIN
2   UPDATE t
3   SET B=DECODE(A,1,valor,2,B),
4   C=DECODE(A,1,C,2,valor);
5 END;

¿Quién de los dos tiene razón?

...depende.

Dado que la performance parece ser lo que está en juego, la primera pregunta que cabe es:

¿Tiene la tabla t índice por A?

Si la tabla no tiene índice, entonces debemos recorrerla por entero. En el primer caso recorremos la tabla dos veces, mientras que en el segundo caso lo hacemos solamente una! Por lo tanto la primera solución en este escenario es el doble más lenta que la segunda. Al notar que la tabla no tiene índices, el Programador 2 asiente la cabeza con orgullo.

Inmediatamente el Programador 1 reacciona: -Pongámosle un índice a t entonces!
(el ávido Programador 1 sabe que la segunda consulta no puede sacar provecho de un índice por lo que su victoria parece asegurada)

Y aquí es donde cabe la segunda pregunta:

¿Cuál es la cardinalidad de la columna A?

Sería de gran ayuda saber si 1 y 2 son la mayoría de los valores que hay en la columna A, porque en ese caso, el optimizador va a continuar prefiriendo recorrer toda la tabla! Si la cardinalidad de los valores 1 y 2 son un porcentaje muy bajo del conjunto de valores distintos, entonces recién ahí los índices van a ser utilizados.
Cuando el índice es utilizado por la escasa presencia de valores 1 y 2, los tiempos de la primer solución es abismalmente inferior.

Conclusión:

La solución 1 es mejor si existen índice sobre la columna A y su cardinalidad es alta (hay pocos 1 y 2 en A).
La solución 2 es mejor si no existen índices o existe y la cardinalidad de A es baja para valores 1 y 2 (significa que 1 y 2 ocurren mucho en A).

Ver también:
Mis índices no funcionan!
Indices condicionales

tic... toc

2007-09-26T20:56:00.000-07:00

¿Cómo medir la performance de un código PL/SQL?

Muchas veces nos encontramos con procedimientos lentos de muchos pasos, con llamadas a funciones, DML, loops con cursores, etc, que queremos revisar. Como SYSDATE nos da la cantidad de segundos como mínima fracción de tiempo, no nos es de mucha ayuda consultarlo entre líneas.

Afortunadamente, contamos con la función get_time del paquete dbms_utility para obtener la cantidad de centésimas de segundo que transcurren entre dos instantes de tiempo.
A sabiendas de eso, debemos incluir los statements que invoquen a dichas funciones y desplieguen los tiempos por la salida, en un archivo o en una tabla de logs.

Con el fin de evitar duplicar código y además aprovechar las bondades del cacheado de paquetes en el área de memoria compartida, creé un pequeño paquete exclusivo para ese fin. El mismo tiene dos procedimientos: tic y toc. Cuando quiero comenzar a medir tiempo, invoco a tic (con un mensaje para registro). Cuando quiero medir el tiempo transcurrido, uso toc. Así de simple.

CREATE OR REPLACE PACKAGE crono AS
  v_tic PLS_INTEGER;
  evento VARCHAR2(100);
  PROCEDURE tic(p_evento IN VARCHAR2);
  PROCEDURE toc;
END crono;
/

CREATE OR REPLACE PACKAGE BODY crono AS
PROCEDURE tic(p_evento IN VARCHAR2) IS
BEGIN
   v_tic := DBMS_UTILITY.GET_TIME;
   evento := p_evento;
   DBMS_OUTPUT.PUT_LINE('TIC: '||p_evento);
END tic;

PROCEDURE toc IS
   df pls_integer := CAST(DBMS_UTILITY.GET_TIME AS PLS_INTEGER)-v_tic;
BEGIN
   DBMS_OUTPUT.PUT_LINE('TOC: '||evento||', T(1/100s): '||df);
END toc;
END crono;
/

El uso del paquete en un procedimiento cualquiera puede ser:

SQL> begin
2     crono.tic('creo tabla');
3     execute immediate 'CREATE TABLE testtable as select * from user_tables';
4     crono.toc;
5     crono.tic('elimino registros');
6     execute immediate 'delete from testtable';
7     crono.toc;
8     crono.tic('hago commit');
9     commit;
10     crono.toc;
11     crono.tic('borro tabla');
12     execute immediate 'drop table testtable';
13     crono.toc;
14 end;
15 /
TIC: creo tabla
TOC: creo tabla, T(1/100s): 34
TIC: elimino registros
TOC: elimino registros, T(1/100s): 5
TIC: hago commit
TOC: hago commit, T(1/100s): 0
TIC: borro tabla
TOC: borro tabla, T(1/100s): 50

Procedimiento PL/SQL terminado correctamente.

Aunque esta experiencia es meramente demostrativa, notar que no pasó un segundo en toda la operación, y sin embargo estamos sacando medidas ilustrativas en cada paso.

Es una solución práctica y disponible para todos los usuarios de la base de datos, solo tengo que dar permisos de ejecución a los desarrolladores o a public. Un sinónimo puede simplificar aún más las llamadas a tic y a toc.

Pese al mínimo costo de salida por pantalla, una desventaja es que el procedimiento tiene que terminar exitosamente para obtener las medidas (dbms_output despliega su salida al final del proceso). Una alternativa para lograr la medición "on the run", es implementar la salida a archivo (utilizando el paquete utl_file), o insertando en una tabla (con una transacción autónoma para no hacer commit sobre la actual).
Estos cambios pueden ser transparentes al usuario, ya que solo hará falta recompilar el body del paquete. Las ventajas están a la vista.

Ver también:
Uso del paquete utl_file

El usuario Scott no existe

2007-09-22T08:39:00.000-07:00

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Dependiendo de las opciones de instalación o de los schemas que vengan instalados en algunas versiones del RDBMS (como Express por ejemplo), el usuario más famoso de Oracle, Scott, puede no estar presente. Citando el caso de Oracle XE, el único schema de ejemplo que viene instalado por defecto es HR.

Pero no hay problema, lo único que tenemos que hacer para instalar el schema Scott con sus clásicas tablas EMP, DEPT, BONUS y SALGRADE, es correr (en SQL*Plus) el script utlsampl.sql que está en el directorio del servidor $ORACLE_HOME/RDBMS/ADMIN.

Como se requiere de ciertos permisos especiales que un usuario corriente no tiene, deberá hacerlo el DBA.

Limpiando código DDL

2007-09-10T16:35:00.000-07:00

A propósito de Un extractor de DDL en archivos separados, me preguntaron como evitaba exportar las cláusulas de storage en Oracle 9i, únicamente dejando las que especifican los tablespaces.

SQL> create table t (a int);

Tabla creada.

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

CREATE TABLE "LFERNANDEZ"."T"
( "A" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

En la práctica, las cláusulas PCTFREE, PCTUSED, STORAGE, etc, no son útiles durante el desarrollo, sino cuando los objetos son puestos en producción. Es allí donde el detalle en la especificación de storage de los objetos tiene un impacto significativo sobre la performance de los sistemas.

Repasando las opciones disponibles en el paquete dbms_metadata y en particular la función set_transform_param, encontramos que podemos generar el código de asignación de tablespace pasando el valor 'TABLESPACE' como segundo parámetro y TRUE como tercero:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', TRUE);

Desafortunadamente, esta opción es ignorada cuando deshabilitamos todo el STORAGE usando:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE);

No tenemos alternativa entre: o generar todo el storage, o no generarlo en absoluto.

El camino que termina siendo elegido es el de generar todo el código DDL y luego procesar el archivo de salida con algun lenguaje con buenas cualidades para procesamiento de texto, como por ejemplo Perl o AWK.

No se requiere ser experto en AWK para programar un sencillo script que elimine algunas líneas de archivos:

BEGIN {flag=2}
/^ *) PCTFREE/{print ")"; flag=0}
/^ *PCTFREE/{flag=0}
flag==0 && $0~/)$/{flag=1}
flag==2
flag==1{flag=2}

Esta es la idea: uso flags para saber cuándo imprimir y cuándo no, luego tengo dos posibles comienzos con PCTFREE, el primero para tablas comunes y el segundo para el caso de tablas particionadas (las cuales no son antecedidas por un paréntesis).

Cualquier comentario para mejorar la performance de este script será bienvenido!

Ver también
Un extractor de DDL en archivos separados

ORA-00600 y recolección de estadísticas con ORA-00933

2007-08-15T14:04:00.000-07:00

Esto puede ocurrir en 9iR2:
Al ejecutar un select se produce el error inesperado

ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []

Esto puede solucionarse generando las estadísticas nuevamente, invocando el paquete dbms_stats.
Al hacer eso, puede ocurrir lo siguiente al ejecutar el procedimiento dbms_stats.gather_table_stats (o gather_schema_stats):

SQL> exec dbms_stats.gather_table_stats(user,tabname => 'FACTURADOR', estimate_percent => 20, block_sample => false, method_opt => 'FOR TABLE FOR ALL COLUMNS SIZE 1', cascade => true);

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 1

Verificar que este error no se produce si invocamos el procedimiento con cascade => false.

Esto esta relacionado con los parámetros de NLS como NLS_LANGUAGE, NLS_SORT y NLS_NUMERIC_CHARACTERS. Seguramente no tienen valores por defecto, los cuales se necesitan para la generación de histogramas.

La vista NLS_SESSION_PARAMETERS nos muestra cuáles son los valores actuales que se están usando (recordar que éstos tienen prioridad sobre los parámetros de la base de datos).

SQL> select PARAMETER, VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER IN ('NLS_LANGUAGE', 'NLS_SORT', 'NLS_NUMERIC_CHARACTERS'); NLS_SESSION_PARAMETERS;

PARAMETER VALUE
------------------------- --------------------
NLS_LANGUAGE AMERICAN
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY

Si alguno de estos parámetros no aparece como arriba, será necesario hacer un ALTER SESSION para arreglarlo y probar generar las estadísticas nuevamente con cascade => true.

Un extractor de DDL en archivos separados

2007-08-08T14:09:00.000-07:00

¿Cómo podemos exportar todos los objetos de un esquema de Oracle en archivos separados para poder versionar el código?

Algunos de los IDEs comerciales más populares de PL/SQL traen esta funcionalidad (lo pueden investigar), pero me parece una buena oportunidad para poder hacerlo sin ayuda de ninguna otra herramienta que... PL/SQL!

Para poder cristalizar esto, ejecutaremos desde sql*plus bloques de PL/SQL que realicen lo siguiente:
1) Extraer el DDL de los objetos - usaremos el paquete dbms_metadata para obtener el DDL de tablas, indices y constraints. También leeremos la tabla de diccionario user_source para capturar el código de procedimientos, funciones, paquetes y triggers.
2) Escribir en archivos el código generado - invocaremos al paquete utl_file para crear archivos en el sistema operativo.

Este procedimiento funciona en 9i, XE o 10g, tanto para Unix como para Windows. Los archivos quedarán alojados localmente en el servidor (el usuario necesitará los debidos permisos de acceso a estas carpetas).

Nota para usuarios Oracle XE:
El paquete utl_file no vine compilado por defecto luego de la instalación. Es necesario ejecutar el siguiente script con el usuario sys:
$ORACLE_HOME/RDBMS/ADMIN/utlfile.sql
Luego deberán revisar con el usuario sys si quedaron paquetes, vistas o procedimientos mal compilados y en tal caso, recompilarlos.

Bueno, ahora sí vamos con los pasos necesarios:

Paso 1) Crear directorios en el servidor - El usuario de la base de datos necesita privilegios para crear directories de Oracle, de lo contrario deberá solicitar al DBA que lo haga por él o le indique alguno que pueda usar. De lo contrario no vamos a poder escribir con el paquete utl_file.

Las carpetas deben existir en el sistema operativo y luego crearse los objetos directorios en Oracle, asociados a las mismas.
Por ejemplo, luego de crear las carpetas en Unix, ejecutar lo siguiente en SQL*Plus:

create directory tables as '/oracle/source/tables';
create directory indexes as '/oracle/source/indexes';
create directory procedures as '/oracle/source/procedures';
create directory functions as '/oracle/source/functions';
create directory packages as '/oracle/source/packages';
create directory packagebodies as '/oracle/source/packagebodies';
create directory triggers as '/oracle/source/triggers';
... etc

Paso 2) Especificar la información a ser generada con dbms_metadata - La función set_transform_param nos permite especificar que detalle de código debe generarse.

Ejecutar lo siguiente en SQL*Plus:
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER', TRUE ) ;
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR', TRUE ) ;
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', FALSE) ;

Paso 3) Finalmente, la parte interesante... codificar el PL/SQL! :)

Voy a mostrar dos ejemplos: el primero para capturar tablas y el segundo para capturar los procedimientos. El caso para capturar DDL de índices es completamente análogo al de tablas, y el caso de funciones, paquetes, body de paquetes, triggers es similar al de procedimientos.

Ejecutar en SQL*Plus:

1 DECLARE
2    f UTL_FILE.FILE_TYPE;
3 BEGIN
4    FOR cr IN (SELECT table_name FROM USER_tables)
5    LOOP
6      f := UTL_FILE.FOPEN ('TABLES', cr.table_name || '.sql', 'w');
7      UTL_FILE.PUT_LINE (f, dbms_metadata.get_ddl('TABLE', cr.table_name));
8      UTL_FILE.FCLOSE (f);
9    END LOOP;
10 END;
11 /

Las líneas 2, 6, 7 y 8 muestran el uso del paquete utl_file en cuatro pasos básicos: se declara el manejador, se abre el archivo para escritura, se escribe y finalmente se cierra.

La línea 4 selecciona todas las tablas de mi esquema.

En cada iteración del FOR, abrimos un nuevo archivo (línea 6) con el nombre de la tabla, y escribimos en el directorio TABLES (creado en el paso 1) la salida del procedimiento dbms_metadata.get_ddl.

Para capturar los índices es análogo, cambiando en la línea 7 el parámetro 'TABLE' por 'INDEX' y cambiando también si se desea el directorio destino en la línea 6.

Ahora veremos el caso de escribir código de usuario leyendo la tabla user_source. Tenemos que tener algunos detalles en cuenta, ya que la tabla user_source contiene todo el código separado una línea por fila. Vamos a ir escribiendo el archivo de a una línea y tendremos que saber cuándo termina un procedimiento y comienza otro.

1 DECLARE
2    f UTL_FILE.FILE_TYPE;
3    nombre VARCHAR2(4000) := NULL;
4 BEGIN
5    FOR cr IN (SELECT name, text FROM USER_SOURCE WHERE type='PROCEDURE' order by name, line)
6    LOOP
7      IF (nombre IS NULL OR cr.name <> nombre) THEN
8        IF (nombre IS NOT NULL) THEN
9          UTL_FILE.FCLOSE (f);
10       END IF;
11       f := UTL_FILE.FOPEN ('TABLES', cr.name || '.sql', 'w');
12       UTL_FILE.PUT (f, 'CREATE OR REPLACE ');
13     END IF;
14     nombre := cr.name;
15     UTL_FILE.PUT (f, cr.text);
16   END LOOP;
17   IF UTL_FILE.IS_OPEN(f) THEN
18     UTL_FILE.FCLOSE (f);
19   END IF;
20 END;
21 /

En la línea 3 declaramos una variable para leer cada línea.

En la línea 5 seleccionamos el tipo de objeto que queremos capturar. De la misma forma lo haremos para triggers, funciones, paquetes, etc.

Las líneas 7 y 8 tratan la apertura y cierre del archivo cuando se termina un procedimiento; al ir leyendo línea a línea de todos los objetos, necesito saber cuando comienza y cuando termina un procedimiento para podeer abrir un nuevo archivo de escritura.

En la línea 12 le agrego 'CREATE OR REPLACE' ya que la tabla user_source no almacena esta directiva.

Observar en las líneas 12 y 15 que utilizamos la función PUT en lugar de PUT_LINE. Esto es porque las líneas de user_source ya almacenan un retorno de carro por lo cual no es necesario que use PUT_LINE, de lo contrario voy a escribir un renglón por medio.

Con estos ejemplos es posible capturar todo el código de nuestro esquema y almacenar cada objeto en un archivo diferente. Traté de simplificar el código a costa de sacrificar algunas buenas prácticas, pero en este caso no quise complicar la visibilidad de la idea por lo cual cada uno podrá mejorar su propia implementación.

SQL*loader: Field in data file exceeds maximum length

2007-07-27T17:41:00.000-07:00

Recientemente tuve la oportunidad de ver un problema en un aplicativo que utilizaba SQL*loader 9.2.0.6 para cargar datos de un archivo plano en una tabla, originalmente separados por pipes y eventualmente conteniendo espacios en uno de sus campos.

Un ejemplo del contenido del archivo de datos (los pipes van en color para identificar mejor los campos):

A1|3|02/12/98|NN|A Suspender |34
A2|2|02/12/98|NS|No se presentó, fue enviado nuevo aviso (**) |27
A3|3|02/14/98|NS||9

(**) La segunda línea de datos contiene luego del texto y antes del siguiente pipe, más de 300 espacios.

El control file utilizado para cargar ese archivo tenía esta forma:

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(

  SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO  DATE  "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION  "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

Al campo descripcion se le había agregado la función TRIM para eliminar los espacios sobrantes, sin embargo, cuando se ejecutaba SQL*loader la carga fallaba y parecía ignorar la función:

Record 2: Rejected - Error on table TMP_SUSPENSION, column DESCRIPCION.
Field in data file exceeds maximum length

Table TMP_SUSPENSION:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

La causa del problema no era que la función TRIM no estaba cortando los caracteres ni que el campo en la tabla no tuviera el largo suficiente, sino la mala práctica de no especificar tipos en los campos del control file. Recordemos que los campos que se definen allí no corresponden a la tabla destino sino a lo que se espera encontrar en el archivo fuente. Cuando no se especifica tipo y largo en un campo del control file, Oracle asume el largo por defecto de 255 caracteres. Por lo tanto, al encontrar mas de 255 caracteres entre dos pipes, se rechaza el mismo antes siquiera de aplicar la función TRIM. De la forma que opera SQL*loader, primero se validan los tipos en el archivo, y luego se aplican las funciones sobre los campos identificados.

Como solución, se arregló el control file para que tuviera el tipo especificado de un largo mayor al previsto. Esta simple buena práctica es muy recomendada para SQL*loader.

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(

  SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO  DATE  "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION  CHAR(600) "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

Sobre estimar estadísticas

2007-07-22T16:34:00.000-07:00

¿Que estadísticas conviene tomar? ¿La tabla completa o un porcentaje? ¿Qué valor uso?

Esta pregunta es relevante para tablas muy grandes o esquemas populosos donde el tiempo de extracción debe ser optimizado. Obviamente tomar las estadísticas completas es lo mejor por precisión, y en esquemas chicos nos puede llevar algunos pocos minutos, pero algunas veces tenemos ciertos requerimientos de performance o de utilización de recursos ya que es una operación que realiza mucha lectura física (I/O).

Para tablas y esquemas grandes yo suelo tomar un 20 por ciento para el estimado, con lo cual logro mis estadísticas de 'buena calidad'.

Oracle recomienda utilizar el paquete dbms_stats para tomar estadísticas de tablas, índices, esquemas o de la base de datos completa. Extrae más información que el comando ANALIZE además de otras características que podemos aprovechar como el procesamiento en paralelo.

Se puede invocar de la siguiente manera:

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'OUT_TAB',estimate_percent => 20, method_opt => 'FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES', cascade => true );

Algo interesante de este paquete es que nos permite que Oracle calcule un valor adecuado para el porcentaje estimado, y esto se hace poniendo dbms_stats.auto_sample_size en estimate_percent.

Si hacemos la prueba podemos constatar observando la tabla user_tables que Oracle toma las estadísticas al 100 por ciento hasta cierta cantidad de filas que depende de cada base de datos.

A tales efectos puede utilizarse una consulta como ésta:

SELECT table_name, num_rows, sample_size, TRUNC(sample_size*100/DECODE(num_rows,0,1,num_rows),2) porciento
FROM user_tables;

A modo de ejemplo, en una base 9iR2 sobre un esquema de desarrollo con estadísticas estimadas al 20 por ciento, constaté que hasta las tablas con 12500 filas Oracle tomaba el 100 por ciento de las filas como muestra, y luego a partir de ahí tomaba el 20 por ciento que yo le había solicitado.

También se puede comprobar que usando dbms_stats.auto_sample_size para ciertos casos, Oracle calcula la tabla entera sin importar la cantidad de tuplas.
Esto se debe a que Oracle utiliza un algoritmo para obtener un número adecuado: parte de cierta cantidad de tuplas y en base a ellas analiza si la calidad de las mismas es buena, si no lo es toma un nuevo porcentaje y así sucesivamente hasta llegar a un 25 por ciento, luego del cual decide computar la tabla entera.
Cuando esto sucede, los requerimientos de performance o recursos utilizados sufren; por este motivo en esos casos es mejor especificar el valor fijo.

Ver también:
Falla de estadísticas con ORA-00933
Error ORA-942 while gathering statistics

Variables en SQL*Plus (parte 2)

2007-07-19T01:00:00.000-07:00

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.
En la primera parte repasamos las variables de usuario, aquí van las variables de sustitución.

VARIABLES DE SUSTITUCION

Las variables de sustitución suelen confundirse con las variables de usuario, más que nada porque ambas aparecen precedidas con un símbolo ampersand (&). Sin embargo, hay diferencias en la definición, asignación y uso de las mismas. Como vimos en la parte 1, las variables de usuario se declaran explícitamente con el comando DEFINE, tienen asociado un tipo y se les pueden asignar valores en el script como lo hacemos en cualquier lenguaje de programación.
Las variables de sustitución en cambio cuando están indefinidas, solicitan el valor al usuario cada vez que aparecen, y el resultado es exactamente el mismo que si escribiéramos el texto directamente.

Por ejemplo: en la siguiente consulta en cada aparición de variable será solicitado el valor al usuario. Notar que aunque la variable COLUMNA aparece repetida, se trata como si fueran diferentes y el valor es solicitado dos veces.

SELECT &COLUMNA, SUM(MONTO)
FROM cuentas
GROUP BY &COLUMNA;

Enter value for columna: ID_REGION
old 1: SELECT &COLUMNA,
new 1: SELECT ID_REGION,
Enter value for columna: ID_REGION
old 1: GROUP BY &COLUMNA
new 1: GROUP BY ID_REGION

Mientras que el símbolo ampersand (&) declara una variable sustitución temporal (vale una única vez), dos ampersand consecutivos (&&) definen una variable permanente evitando que el usuario deba ingresar nuevamente su valor.

Scripts parametrizados

Las variables de sustitución se utilizan típicamente para pasar parámetros a un script a través del comando START. Dentro del script las variables deben enumerarse secuencialmente &1, &2, etc, y éstas serán asignadas en orden con los parámetros pasados en la llamada.

Ejemplo: Un script al que le pasamos dos parámetros, un nombre de columna y la tabla

programa.sql
SET VERIFY OFF
SELECT &&1, SUM(MONTO)
FROM &2
GROUP BY &1;

La llamada se realiza de la siguiente manera:

SQL> START programa.sql ID_REGION cuentas

La variable 1 tiene dos ampersand por lo que queda definida a la primera vez que aparece.
SET VERIFY OFF elimina el feedback de old y new values.
Las variables de sustitución 1 y 2, al igual que las variables de usuario también se pueden borrar de memoria, por ejemplo incluyendo la línea UNDEFINE 1 en el script.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 3) - Sobre variables bind

Announcing Oracle Database 11g

2007-07-14T09:37:00.001-07:00

Institucional de la 11g lanzada el pasado 11 de julio en NY.

Cuestiones de idioma

2007-07-09T05:56:00.000-07:00

Muchas veces necesitamos comparar días de la semana con SQL y usamos funciones como to_char, to_date, next_day, y otras. Con ellas podemos obtener el número de día de la semana o el nombre del día o del mes, y usarlo para evaluar alguna condición.
En estos casos deberíamos preguntarnos si nuestro código depende del idioma en que esté configurado el cliente o la base de datos. Si esto es así, estamos haciendo que nuestras aplicaciones sean dependientes del lugar donde se instalen y eso no es deseable.

Recordando que el orden de preferencia para los seteos del NLS es (de mayor a menor):

1) NLS explicitado en función
2) NLS configurado en la sesión del usuario
3) NLS configurado en el cliente
4) NLS configurado en la base de datos

...es una buena práctica 'asegurar' el idioma o territorio a utilizar cuando usamos una función que depende de eso.
Yo prefiero que mis aplicaciones dependan lo menos posible de una configuración especial en el cliente o la base de datos, por lo tanto siempre voy a aplicar 1) o 2).

Ejemplo 1: Días de la semana como palabra

Obtener las marcas de los empleados los días sábados:

SELECT timestamp, employee
FROM timer_tbl
WHERE to_char(timestamp,'DAY') = 'SATURDAY';

En este caso la consulta depende del idioma que este configurado en la sesión o en el cliente o la base de datos, y rezaría para que siempre fuera inglés!
Para evitar ese tipo de ligaduras con la región, puedo pasarle a to_char el parámetro NLS_DATE_LANGUAGE y entonces estoy en el caso 1) de máxima prioridad.
Me quedaría:

SELECT timestamp, employee
FROM timer_tbl
WHERE to_char(timestamp,'DAY','NLS_DATE_LANGUAGE=AMERICAN') = 'SATURDAY';

Ejemplo 2: Número de día de la semana

En algunos territorios como Portugal y USA el primer día de la semana es el domingo, en otros como España y América del Sur es el lunes. Esto hace que la máscara 'D' de to_char sea diferente según el territorio.
El número de dia de la semana depende del parámetro NLS_TERRITORY, pero no lo puedo pasar como tercer parámetro, no es válido.

En ese caso tendré que alterar la sesión para asegurar que el día 1 sea el lunes para mi aplicación.

SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> select to_char(sysdate,'D DAY') HOY from dual;

HOY
-----------------------------
1 MONDAY

Ver también:
Acentos del idioma español

INNER/OUTER JOINS (ANSI)

2007-07-05T17:16:00.000-07:00

La pregunta del día es:
¿Usando la notación ANSI para joins, es lo mismo agregar condiciónes adicionales al where que en la cláusula ON?

Si no se entendió la pregunta, pongamos un ejemplo...

¿Son estas dos consultas equivalentes?

SELECT d.departamento_id, e.nombre
  FROM (departamento d INNER JOIN empleado e
    ON d.departamento_id = e.departamento_id)
WHERE d.seccion_id = e.seccion;

SELECT d.departamento_id, e.nombre
  FROM (departamento d INNER JOIN empleado e
    ON d.departamento_id = e.departamento_id
  AND d.seccion_id = e.seccion);

Pensemos esto, si usáramos el viejo estilo de join (la sintaxis ANSI apareció en 9i), tendríamos todas las condiciones juntas, sin orden específico ni marca especial del tipo "esta condición es de join".
Sucede que luego que Oracle parsea la consulta, analiza todas las condiciones que haya encontrado y según lo que tiene sobre la mesa decide cuál aplicar primero. El hecho de que nosotros incluyamos una condición en el ON o en el where, no va a ser un factor decisivo sino una diferencia documental que aporta claridad. Hagamos sino la prueba de dejar una de las cláusulas de un join compuesto fuera del ON, y colocarla en el where.... el resultado va a ser el mismo!

Si alguien quiere pruebas experimentales concretas podrá comparar los planes de ejecución y notar que internamente es exactamente lo mismo.

Variables en SQL*Plus (parte 1)

2007-06-28T15:59:00.000-07:00

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.

Aquí va la primera de ellas, más adelante revisaremos las otras.

VARIABLES DE USUARIO

Se definen de la siguiente forma:

SQL> DEFINE u_nombre = ANDREA

Para confirmar que haya quedado el valor guardado:

SQL> DEFINE u_nombre
DEFINE U_VAR1 = "ANDREA" (CHAR)

El comando DEFINE nos permite definir una cadena que se sustituye textualmente en cualquier lugar de nuestro script. Aunque quede definida como CHAR realmente no importa el tipo ya que la sustitución se realiza antes de ejecutar el código y además no incluye comillas. Por estas razones podemos jugar a 'construir' el script con nuestras variables de sustitución.
Si queremos usar la entrada como varchar le agregamos las comillas nosotros, si queremos usarla como número no las agregamos. Para recuperar el contenido de la variable usamos el símbolo ampersand.

Por ejemplo: SELECT * FROM empleado WHERE nombre = '&u_var1';

Ahora definimos una variable con un número...

SQL> define u_edad = 25
SQL> define u_edad
DEFINE U_EDAD = "25" (CHAR)

...y la usamos de la misma forma pero sin incluir las comillas:

SELECT * FROM empleado WHERE edad = &u_edad;

Las variables de usuario son útiles cuando se define su valor al comienzo de un script y luego se utiliza varias veces en el código.

No solamente podemos sustituir valores de columnas, sino también nombres de columnas o de tablas, incluso fragmentos de código, permitiéndonos hacer scripts más dinámicos.

Un uso interesante es 'aceptar' el nombre de una columna y luego usarla para parametrizar el ORDER BY de un reporte.

Ver también:
Variables en SQLPlus (parte 2) - Sobre variables de sustitución
Variables en SQLPlus (parte 3) - Sobre variables bind

Webstats

2007-06-27T11:24:00.001-07:00

La gente de MOTIGO Webstats toma las estadísticas de este blog, pero estoy comenzando a sospechar que sus números no son muy confiables...

For Export/Import

2007-06-27T08:32:00.000-07:00

La base de datos Oracle XE es cada vez más popular en los hogares de los programadores que en sus trabajos utilizan la 9i.
Estos exportan un schema versión 9i y lo llevan a su flamante XE portátil. Hasta aquí no hay mayores dificultades, la instalación y configuración de XE se realiza sola, y basta leer el manual Database Utilities para aprender a utilizar exp e imp.

Un archivo dump de 9i se puede importar en 10g sin especificar ningún parámetro extra.

El asunto es que para migrar de la casa al trabajo con el programa exp tendremos un problema: el archivo dump generado por XE (10g) no se puede importar en 9i, no es compatible.

Import: Release 9.2.0.1.0 - Production on Thu Jun 28 18:31:04 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

La solución es exportar desde XE con un exp 9i. El imp y el exp deben tener la misma versión cuando exportamos algo que es mas nuevo que la base destino.

No es necesario que instalemos una base de datos 9i en casa, alcanza simplemente con instalar el cliente el cual se puede descargar gratuitamente desde:

http://www.oracle.com/technology/software/products/oracle9i/htdocs/winsoft.html

Luego de instalar el cliente 9i, hay que tener en cuenta que éste utiliza su propio tnsnames.ora, por lo que vamos a tener que anexarle el servicio de nuestra base de datos XE. Finalmente ejecutaremos el export del cliente (por ejemplo: c:\oracle\ora92\bin\exp.exe) y exportaremos un archivo dump versión 9i que podrá ser importado sin problemas por otra 9i.

Un detalle: tendremos que ser cuidadosos al ejecutar programas como sql*plus, exp, imp. Cada uno de ellos referirán a sus respectivos tnsnames.ora donde están registradas nuestras bases de datos. Como al iniciarse siempre muestran la versión, sabremos cual tnsnames.ora están leyendo.

Problemas de TNS utilizando DB links

2007-05-23T10:43:00.000-07:00

Al definir un DB link y utilizarlo en una consulta, puede obtenerse el error:

ORA-12154: TNS:could not resolve service name

Nada parece estar mal:

Creamos el link utilizando el service name del tnsnames.ora
Verificamos que la entrada del servicio está justamente definida en el archivo
Revisamos que nuestra aplicación esté leyendo el tnsnames.ora correcto (en caso de que tengamos muchos)
El usuario y password del link funciona OK si me conecto por SQL*plus
Tengo el privilegio CREATE DATABASE LINK
Al crear el link me retorna que el link fue creado exitosamente

Al utilizar el link en un select, se obtiene el error mencionado.
La razón por la cual no encuentra el conector aún la desconozco, pero la forma de evitar el problema es:

En lugar de definir el link de esta forma:

create public database link uat1prod_lnk
connect to oramain
identified by "0racl3"
using 'produat1db';

donde 'produat1db' es el nombre del service name para ese conector (tnsnames.ora)

Definirlo así:

create public database link uat1prod_lnk
connect to oramain
identified by "0racl3"
using '(description=(address=(protocol=TCP)
(host=192.168.3.32)(port=1521))(connect_data=(sid=uat1db)))';

donde 'uat1db' es el nombre del SID de la base de datos.

Crearlo de esta manera corrige el problema. Otra ventaja adicional que me proporciona esta sintaxis, es independencia del tnsnames.ora.
Lo único que necesito es: IP, puerto y SID de la base de datos.

PL/SQL haragán

2007-05-18T15:16:00.000-07:00

¿Es PL/SQL 'lazy' para las comparaciones?
Es decir, para evaluar (expr_a AND expr_b): si expr_a es falso entonces ¿se evalúa expr_b?
Asimismo en (expr_a OR expr_b): si expr_a es verdadero entonces ¿se evalúa expr_b?

Opción 1 -> consultar la documentación

Opción 2 -> ¡probarlo!

SQL> create table t (a int);
Table created.
SQL> create or replace function fA return boolean is
2 begin
3 insert into t values (1);
4 return (false);
5 end;
6 /

Function created.

SQL> begin
2 if ((1=0) and fA) = true then
3 NULL;
4 end if ;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select * from t;

no rows selected

Conclusión: PL/SQL es lazy. No se insertó un 1 en la tabla ya que la función fA nunca se ejecutó.

¿Y en SQL? ¿Son las condiciones 'lazy' en las consultas?

Lo podemos probar con otra función similar:

SQL>
create or replace function fB return number is
2 begin
3 insert into t values (0);
4 return (0);
5 end;
6 /

En SQL no podría ejecutar esta función, ya que realiza una operación DML:

SQL> select * from dual where ((0 = 0) and (fB = 0));

ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "EMP01.FB", line 3

Sin embargo, si cambiamos AND por OR, notamos que la función fB ni siquiera es evaluada:

SQL> select * from dual where ((0 = 0) or (fB = 0));

D
-
X

1 row selected.

Conclusión: SQL también es lazy. Si se hubiera evaluado la segunda expresión, entonces tendría que haber retornado el error ORA-14551.

ORA-01461: can bind a LONG value only for insert into a LONG column

2007-05-13T15:49:00.000-07:00

Este error puede ocurrir cuando se insertan o actualizan valores en la base de datos desde alguna aplicación cliente, como por ejemplo Forms o Java.

En mi caso utilizando Forms 10g, intentaba grabar en un textbox dos mil y tantos caracteres en un formulario, obteniendo repetidamente el error. El campo de formulario estaba definido como CHAR de largo máximo 4000 y la columna de la base de datos era VARCHAR2 de 4000; entonces donde estaba el problema?

El problema resultó en el character set de la base de datos y el character set del cliente.
La base de datos tenía un character set multibyte, es decir que puede utilizar varios bytes para representar un caracter. El cliente (en mi caso Application Server 10g), estaba configurado con un character set single byte. Resultado: el cliente enviaba 1 byte y la base de datos almacenaba en 3 bytes. Al enviar más de la tercera parte del tamaño de la columna, se produce un bug (conocido) de Oracle en el cual supone que se le está enviando un LONG. Forms internamente utiliza bind variables para las operaciones en formularios, por lo tanto se produce el error: can bind a LONG value only for insert into a LONG column.

La solución simple es modificar el character set del cliente, en este caso del Application Server. Particularmete elegí el mismo que tenía la base de datos (UTF8).

Los datos quedan intactos, y el cambio en mi cliente es transparente. Ahora cliente y servidor tienen los mismos juegos de caracteres, y evita que se produzcan las conversiones que llevan al bug mencionado.

Para conocer el character set de la base de datos, puede ejecutarse esta consulta desde SQL*Plus:

SELECT VALUE FROM v$nls_parameters WHERE PARAMETER = 'NLS_CHARACTERSET';

Manipulando HTML en SQL*plus

2007-05-10T18:00:00.000-07:00

"Necesito ejecutar unos scripts SQL*plus que insertan líneas de código HTML en una tabla. El problema que tengo es que cuando los valores contienen el símbolo ampersand, Oracle me pide que ingrese un valor. Como hago para evitarlo?"

Es común en HTML encontrar cosas del estilo:

<font><B>Acentuación en la Gramática</B></font>

El símbolo ampersand en SQL*Plus suele utilizarse como prefijo standard para indicar un parámetro de sustitución en un script.

Por lo pronto, el único problema que presenta el código HTML en SQL*Plus, es para escribir el símbolo ampersand (suerte que las comillas en HTML son dobles y no simples).

Bueno, quien me planteó el problema no tenía intenciones de modificar los scripts originales, así que le di una solución simple: crear un script que invoque a los demás (con @), y que al comienzo del mismo contenga la línea:

SET DEFINE OFF

Este comando de SQL*Plus inhibe los parámetros de entrada en SQL*Plus, por lo cual va a ignorar todos los ampersand que encuentre.

Aunque la solución es simple y funciona para muchos casos, es también un poco drástica: inhibe los verdaderos parámetros que contengan nuestros scripts. Si nuestro script recibe un parámetro &valor y en la siguiente línea inserta un texto que contiene un &aacute, entonces no hay manera de que SQL*plus se de cuenta cuál es parámetro y cuál no. Conclusión: los scripts deben alterarse.

Para esto hay algunas alternativas. La primera, es cambiar el prefijo para definir parámetros, por algún símbolo que no sea caracter especial en HTML, por ejemplo, el símbolo de pesos.

SET DEFINE $

De esta manera los ampersand de HTML serán ignorados.

Una segunda alternativa, es utilizar el caracter de escape (por defecto '\') para 'escapar' los ampersand del código. Luego los ampersand no son incluídos al guardarse en la tabla. Por lo general tendremos muchos menos parámetros que símbolos ampersand, así que particularmente elijo la primer opción.

En conclusión, cambiar el prefijo de parámetros con SET DEFINE $ es la opción más flexible. Usar SET DEFINE OFF al comienzo del script nos inhibe los parámetros, pero si no los necesitamos, se transforma en la opción más sencilla de implementar.

Fallo de librerías en instalación de 10g en SUSE 10

2007-05-08T19:47:00.000-07:00

Talvez hayan tenido este mismo problema.
Mientras se instala Oracle 10.2.0 en Suse 10.0 64 bits, el Oracle Universal Installer falla en un 64% de completado, cuando esta enlazando las librerías. Al mirar el log generado, aparece lo siguiente:

INFO: ./x86_64-suse-linux/bin/ld: skipping incompatible /usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../libpthread.so when searching for -lpthread
/usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../../x86_64-suse-linux/bin/ld: skipping incompatible /usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../libpthread.a when searching for -lpthread
/usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../../x86_64-suse-linux/bin/ld: cannot find -lpthread

INFO: collect2:
INFO: ld returned 1 exit status

INFO: make: *** [ctxhx] Error 1

El problema que ocurre es que Oracle necesita algunas librerías de 32 bits, y la instalación por defecto de Suse tiene todas de 64 bits. Es necesario instalar las de 32 también.

Son estas dos:

1) glibc-devel-2.3.4-2.25 (i386)
2) compat-libstdc++-33-3.2.3-47 (x86_64)

Si alguna de estas librerías están disponibles en versiones más nuevas, no hay que elegirlas, hay que instalar exactamente las versiones que acabo de marcar. Las versiones más nuevas no funcionan.
Asimismo, si ya estuviera instalada la librería compat-libstdc++-33-3.2.3-47.3, hay que desinstalarla e instalar la que sirve: compat-libstdc++-33-3.2.3-47.

Realmente es el único problema serio que me encontré, el resto mas o menos está en el manual de instalación.

Mis índices no funcionan!

2007-05-01T11:41:00.000-07:00

TOP FIVE de casos en los cuales los índices que creamos 'inexplicablemente' no funcionan.

1 - CONSULTAS QUE RETORNAN MUCHOS REGISTROS
Este es el caso de consultas que retornan muchos registros. Según algunos autores, más del 5 por ciento del total de filas, pero en realidad es un porcentaje variable que se infiere según varios parámetros como cardinalidad, cantidad de filas, distribución en los bloques, etc.
En este tipo de operaciones, es mas 'barato' para la base de datos leer toda la tabla y colocarla en memoria que acceder a cada registro mediante el índice. Hay que pensar que un índice es una estructura más que debemos consultar para recién llegar al bloque que contiene nuestro registro y traerlo a memoria, por lo cual ese acceso trae un costo adicional para cada fila. Si consultamos pocos registros, el incremento del costo por acceso a índice se absorve con el tiempo total. Si consultamos muchos registros, el overhead incrementa significativamente el tiempo de completitud.
No está mal que los índices no se usen y se produzcan 'FULL SCAN TABLES'. El optimizador sabe los elementos que tiene a mano, cual es el costo asociado a cada método y por lo tanto puede resolver qué usar en cada caso.

2 - SE APLICAN FUNCIONES SOBRE COLUMNAS INDIZADAS
Cuando aplicamos cualquier función en una columna indizada en la cláusula WHERE, el índice deja de tener efecto.
Un simple TRUNC sobre una columna fecha, o un TRIM en una columna VARCHAR2, deja sin efecto a los índices sobre esas columnas.

SELECT nombre, TRIM(apellido)
FROM socios
WHERE SUBSTR(codigo,1,3) = 'DNO'
AND apellido > 'P';

En este ejemplo, un índice sobre la columna codigo no es de utilidad, ya que la función SUBSTR lo anula. Un índice sobre apellido puede funcionar sin problemas ya que no hay ninguna función aplicada en la cláusula WHERE (notar que sí la hay en el SELECT).

3 - CONVERSIONES IMPLICITAS
Por efecto del punto anterior, hay algunos casos en los que no tenemos en cuenta los tipos de datos en el WHERE, y dejamos que se realicen conversiones automáticas. Esto no es preferible, ya que Oracle siempre convierte la columna al tipo del elemento comparante.

Por ejemplo, una consulta como la siguiente:

SELECT nombre, TRIM(apellido)
FROM socios
WHERE nro_socio = '2232';

internamente es traducida como:

SELECT nombre, TRIM(apellido)
FROM socios
WHERE TO_CHAR(nro_socio) = '2232';

...y el índice sobre nro_socio es inhibido por la conversión implícita.

Siempre es bueno convertir explícitamente este tipo de condiciones en donde intervienen diferentes tipos de datos.
Otras opciones son: estandarizar el almacen de datos (por ejemplo guardando siempre los valores con la función aplicada), usar una función de conversión sobre el literal en lugar de en la columna, crear índices de función.

4 - EL PREDICADO NO ES ADECUADO
Por ejemplo, tenemos un índice compuesto formado por las columnas a, b y c. Se quiere realizar una consulta incluyendo en la cláusula WHERE la condicion b (por la cual queremos usar el índice), pero no se incluye a!
Aunque la columna a almacene el mismo valor para todos los registros, Oracle necesita explícitamente todas las columnas previas entre las condiciones del where. De la misma manera, si queremos usar una condición sobre c, deberíamos incluir en el where condiciones para a y b.
Otro caso es cuando tenemos un OR en el where, un distinto != sobre la columna indizada, o cuando la columna aparece a ambos lados de una expresión.

5 - ESTADISTICAS OBSOLETAS (O INEXISTENTES)
El optimizador de la base de datos construye un plan de ejecución basado enteramente en estadísticas de los objetos, almacenadas en el dicionario de datos. La recolección de estas estadísticas debe realizarse periódicamente para que Oracle sepa cuales son los costos asociados a cada método de acceso a los registros, y de esta manera elegir la mejor opción. Al insertar y eliminar datos frecuentemente, cambian las estadísticas quedan obsoletas, y esto puede traer aparejado un impacto directo en la performance de las consultas.

Pueden recolectarse estadísticas de todo el esquema, de una tabla, de un índice, del sistema. El paquete DBMS_STATS contiene procedimientos para hacer el trabajo: GATHER_TABLE_STATS, GATHER_INDEX_STATS, GATHER_SCHEMA_STATS.
Para saber si una tabla tiene estadísticas, puede utilizarse la siguiente consulta:

SELECT last_analyzed
FROM user_tables
WHERE table_name = 'mi_tabla';

Retorna la fecha de la ultima recolección. Si retorna NULL, entonces nunca fueron recolectadas.

Ver también:
Indices condicionales
11g y sus índices invisibles
Indices de función para mejorar un LIKE

Generar spools de más de 2 Gigas en Unix

2007-04-27T19:42:00.000-07:00

Hay varias formas de generar spools con sql*plus desde Unix. La forma tradicional (o directa) es escribir un shell que realice lo siguiente:

-Invocar a sql*plus en modo embebido.
-Ejecutar spool y realizar el SELECT.
-Finalizar el spool con spool off.
-Salir de sql*plus.
-Zipear el archivo de salida (recomendado si se va a enviar a otro destino).

El problema que presenta este enfoque, es que en algunos sistemas operativos, puede existir el límite de 2 Gigas de tamaño para archivos, por lo cual nuestro script va a alimentar el spool hasta llegar a ese tamaño. En el mejor de los casos obtendremos un error inesperado, en otros quizás ni nos enteremos.

Una forma alternativa (y para mi gusto más elegante), es utilizar el comando mknod de Unix. Este comando, entre otras funcionalidades, permite crear un pipe de tipo FIFO que funciona como un dispositivo que utiliza el buffer del sistema operativo y permite conectarlo directamente con un programa de compresion como compress o gzip, evitando que se utilice el file system para almacenar el spool sin comprimir. También es posible conectarlo al programa split para que particione el archivo zip, si este continúa superando el límite máximo.

Básicamente las tareas que hay que escribir en nuestro shell son:
-Borrar pipes que hayan quedado anteriormente (puede fallar si ya existe)
-Crear el pipe
-Asignarle permisos al pipe (con chmod)
-Invocar en modo background al programa destino (el que va a recibir el spool). Este podría ser gzip, split, o ambos.
-Invocar a sqlplus para que realice el spool
-Borrar el pipe creado

Resulta poco intuitivo llamar primero al programa destino y luego en definitiva a quien genera el spool, pero veamos un ejemplo de código para visualizarlo más claramente.

Este script genera un spool y lo comprime en un zip:

1   #/bin/ksh
2   rm -f ${ARCHIVO}.pipe 2>/dev/null
3   mknod ${ARCHIVO}.pipe p
4   gzip -f < ${ARCHIVO}.pipe > ${ARCHIVO}.txt.Z 2>>${LOG} &
5   sqlplus -s usuario/password << EOF > /dev/null 2>> ${LOG}
6   spool ${ARCHIVO}.pipe
7   select * FROM user_objects;
8   spool off
9   EOF
10  rm -f ${ARCHIVO}.pipe 2>/dev/null

En la línea 2 eliminamos pipes que hayan podido quedar abiertos por alguna razón. De quedar abierto obtendríamos un error al crearlo de nuevo.

En la línea 3 creamos el pipe. El primer parámetro es el nombre y el segundo el tipo (p=FIFO)

En la línea 4 invocamos en background a gzip (notar el & al final de la linea). El pipe (el cual todavia no ha recibido datos), va a ser la entrada fuente para gzip. Luego gzip generará el archivo .txt.Z.

Las líneas 5 a 8 llaman a sqlplus y comienzan a enviar datos al pipe, luego gzip que esta corriendo en background zipea el contenido, sin necesidad de tener un archivo en file system.

La línea 10 elimina el pipe.

Si el archivo final .zip alcanza los 2 Gigas, vamos a tener el mismo problema, gzip no va a poder crearlo. Es necesario adicionarle el comando split y conectarlo con gzip para que parta el archivo en secciones de un tamaño manejable.
Para eso, hay que insertar entre la línea 3 y la 4 del script anterior, la siguiente línea:

3.5 split -b 1024m < ${ARCHIVO}.txt.Z &

Conviene leer primero la línea 4 y luego la 3.5, observar que ambos programas corren en background.

La línea 4 es la misma, genera el archivo .txt.Z.

La línea 3.5 recibe el archivo .txt.Z y lo divide en archivos de 1024 megas, el tamaño es en bytes (especificado por el parámetro b).

Sea cual sea el programa que genera el archivo, siempre podemos manipular con pipes los datos sin llegar a utilizar un archivo del file system para pasos intermedios.

Próximos pasos:
MAN del comando mknod
MAN del comando split

Ahí Vamos!

2007-04-23T21:02:00.000-07:00

Como siempre, hay una primera vez para todo!!
La idea de comenzar mi blog surgió por sugerencia de mi amigo Fede Brubacher, adicto a RoR y otras tecnologías emergentes. El, que me conoce y ha sufrido mi orientación a las bases de datos relacionales, movilizó algunos mecanismos de motivación y curiosidad, con el simple hecho de mostrarme su trabajo:
http://fbrubacher.com/

"Dedicado a aquellos que con sus blogs técnicos me ahorraron tantas horas de trabajo y esfuerzo"

Sin más reflexiones doy por inagurado este blog.

Sobre el autor

2007-04-23T14:42:00.000-07:00

Lucio Fernández Herrera
Ingeniero de Sistemas (UDELAR, 2003)
Oracle OCP DBA Certified (2006)
Consultor Oracle Independiente

Perfil Técnico

:: Bases de datos ::
Oracle databases 8i/9i/10g/11g, MySQL, SQL Server

:: Tecnologías ::
Unix, PL/SQL, Apex, Forms, PHP, J2EE, Data Guard, Sun Cluster

:: Áreas de especialización ::
Desarrollo, análisis y diseño, administración, datawarehousing

Me desempeño como DBA y especialista en PL/SQL en proyectos con J2EE. Actualmente resido en Rio de Janeiro, Brasil.
Realizo consultorías de instalación, configuración, administración, tunning, entre otras diversas áreas de desarrollo.
En los diversos proyectos en los cuales he participado aparece un común denominador que es la falta de un conocimiento avanzado de la herramienta que se está utilizando. Esto lleva a efectos tales como la mala performance de los sistemas y sobreesfuerzo de las personas. En ese sentido el propósito de este blog es dar mi pequeño aporte para aliviar y ayudar a que el trabajo sea cada vez más efectivo.
En la carrera de bases de datos, mi motivación es seguir creciendo y aprendiendo día a día en un campo que debe soportar y acompañar a las nuevas tecnologías.

Contactar al autor