ORACLE Tips

Como salimos de: Import file: expdat.dmp >

2012-10-25T08:17:00.001-07:00

Hoy quería compartir con ustedes el hecho de que cuando escribimos mal el path del archivo .dmp para realizar el import, como sabrán nos aparece en nuestro prompt lo siguiente:

IMP-00002: failed to open /prueba.dmp for read
Import file: expdat.dmp >

Imagino que todos probamos con “quit”, “exit”, Ctrl+C, etc…

Pues bien, la respuesta para salir de dicho prompt no es ninguna de esas alternativas sino que, salimos del mismo con Ctrl D.

Espero les haya servido.

Saludos!

Gondalf

String de conexión JDBC para Oracle RAC

2012-05-17T06:40:00.001-07:00

Quizás varios de uds conocen la clásica conexión por JDBC a una single instance de Oracle, pues bien, si necesitamos conectarnos a un RAC con x cantidad de nodos dicho string de conexión sufre algunas modificaciones.

La clásica conexión a una single instance es

jdbc:oracle:thin:@:1521:

La conexión a un RAC presenta las siguientes diferencias:

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service)
(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC))))

Espero les haya servido.

Saludos!

Gondalf.

Empezando a trabajar con Fechas

2012-04-26T13:36:00.001-07:00

Seguramente tuvimos varios problemas al tratar con fechas en Oracle. Se nos complico en sumar o restar horas, o cambiar los días.

Lo que vamos a tratar ahora es de dar unos ejemplos para que no vuelva a suceder. Antes de empezar, tengamos en cuenta que Oracle trabaja con la aritmética en función de un 1 día.

Teniendo entendido este concepto nos va a resultar más fácil la explicación.

Veamos algunos ejemplos:

- Sumar días:
'fecha' + 'días a sumar'
Ejemplo: select sysdate + 1 from dual;

- Sumar horas:

'fecha' + 'horas a sumar / 24'
Ejemplo: select sysdate + 3/24 from dual;

- Sumar minutos:
'fecha' + 'minutos a sumar / 1440'
Ejemplo: select sysdate + 25/1440 from dual;

- Sumar segundos:

'fecha' + 'minutos a sumar / 86400'
Ejemplo: select sysdate + 1/86400 from dual;

Espero les haya servido.

Saludos!

Gondalf.

Pd: En el próximo post trabajaremos más a fondo con las fechas viendo algunas funciones que nos facilitaran mucho trabajo.

ORA-02287: sequence number not allowed here

2012-01-18T11:47:00.000-08:00

Comencemos por la causa del mismo., curre cuando intentamos utilizar una secuencia donde no está permitido hacerlo.

Para ser un poco más explícitos, NO podemos utilizar secuencias en los siguientes casos:

- Dentro de una sentencias SELECT:

* En la claúsula WHERE

* En las claúsulas GROUP BY u ORDER BY
* En una claúsula DISTINCT

* Utilizando UNION o INTERSECT o MINUS
* En un Sub-Query

- En una VISTA o SNAPSHOT
- En una condición de DEFAULT o CHECK de una tabla

(Cabe aclarar que existen más excepciones pero que estas son las mas representativas)

Algunas posibles soluciones para nuestro problema serian:

- Si necesitamos utilizar nuestra secuencia en un WHERE o bien un ORDER BY o un Sub-Query, lo que hacemos es asignarla a una variable previa y dicha variable es la que podemos utilizar en los caso mencionados.

- Si necesitamos utilizar la secuencia para ser insertada tras la creación de las filas de una tabla lo que podemos hacer es utilizar un trigger "before insert" para que realice el trabajo.

- Si quisiéramos una secuencia en una vista podríamos crear la mismas sin la secuencia y luego al momento de llamarla utilizarla (ej: select a, b, "secuencia" from "nuestra vista").

- Si quisiéramos usar la secuencia en un query con una DISTINCT lo que podemos hacer es utilizar este en un Sub-Query y la secuencia en el query principal

Espero que les haya servido

Saludos!
Gondalf.

Parametros EXCLUDE e INCLUDE en DataPump

2011-11-15T05:24:00.000-08:00

Los parametros EXCLUDE e INCLUDE están disponibles con DataPump (expdp, impdp), los mismos pueden ser utilizados para limitar lo que se importa o exporta, ya sean tablas, índices, triggers, funciones, vistas, procedures, paquetes o secuencias (creo que no me olvido de ningún otro).

La sintaxis de estos parámetros es la siguiente:

EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

Ejemplos:

expdp < paramentro varios > SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN (’EMPLEADOS’,'DEPARTAMENTOS’)”;

En este ejemplo vemos que excluimos exportar todas las secuencias y tablas que se llamen EMPLEADOS o DEPARTAMENTOS del schema backup

impdp < paramentro varios > SCHEMAS=backup INCLUDE=TABLE:”=’OLD_TABLE’”

En este ejemplo vemos como hacemos un import incluyendo solo la tabla de nombre OLD_TABLE.

Espero les haya servido

Saludos!

Gondalf

Diferencias entre los CharacterSet WE8ISO8859P1 y WE8MSWIN1252

2011-11-10T07:53:00.000-08:00

Según oracle tenemos que el characterset definido en WE8ISO8859P1 es un “subgrupo” del characterset WE8MSWIN1252, es decir que este ultimo posee characters adicionales al primero.

Hilando un poco más fino, podemos decir que el characterset WE8MSWIN1252 posee 27 caracteres adicionales.

A continuación vemos la lista con las 27 diferencias:

Win-1251	Unicode Char	Charact	Descripción
0x80	0x20AC	[€] [€]	EURO SIGN
0x82	0x201A	[‚] [‚]	SINGLE LOW-9 QUOTATION MARK
0x83	0x0192	[ƒ] [ƒ]	LATIN SMALL LETTER F WITH HOOK
0x84	0x201E	[„] [„]	DOUBLE LOW-9 QUOTATION MARK
0x85	0x2026	[…] […]	HORIZONTAL ELLIPSIS
0x86	0x2020	[†] [†]	DAGGER
0x87	0x2021	[‡] [‡]	DOUBLE DAGGER
0x88	0x02C6	[ˆ] [ˆ]	MODIFIER LETTER CIRCUMFLEX ACCENT
0x89	0x2030	[‰] [‰]	PER MILLE SIGN
0x8A	0x0160	[Š] [Š]	LATIN CAPITAL LETTER S WITH CARON
0x8B	0x2039	[‹] [‹]	SINGLE LEFT-POINTING ANGLE QUOTATION MARK
0x8C	0x0152	[Œ] [Œ]	LATIN CAPITAL LIGATURE OE
0x8E	0x017D	[Ž] [Ž]	LATIN CAPITAL LETTER Z WITH CARON
0x91	0x2018	[‘] [‘]	LEFT SINGLE QUOTATION MARK
0x92	0x2019	[’] [’]	RIGHT SINGLE QUOTATION MARK
0x93	0x201C	[“] [“]	LEFT DOUBLE QUOTATION MARK
0x94	0x201D	[”] [”]	RIGHT DOUBLE QUOTATION MARK
0x95	0x2022	[•] [•]	BULLET
0x96	0x2013	[–] [–]	EN DASH
0x97	0x2014	[—] [—]	EM DASH
0x98	0x02DC	[˜] [˜]	SMALL TILDE
0x99	0x2122	[™] [™]	TRADE MARK SIGN
0x9A	0x0161	[š] [š]	LATIN SMALL LETTER S WITH CARON
0x9B	0x203A	[›] [›]	SINGLE RIGHT-POINTING ANGLE QUOTATION MARK
0x9C	0x0153	[œ] [œ]	LATIN SMALL LIGATURE OE
0x9E	0x017E	[ž] [ž]	LATIN SMALL LETTER Z WITH CARON
0x9F	0x0178	[Ÿ] [Ÿ]	LATIN CAPITAL LETTER Y WITH DIAERESIS

Esto quiere decir que si necesitamos utilizar por ejemplo el simbolo del Euro y nuestra base esta configurada en P1 deberiamos modificar el characterset de la base siguiente los siguientes pasos:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> Alter DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET WE8MSWIN1252;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

Espero les haya servido.

Saludos!
Gondalf

Un poquito de PL/SQL, Packages

2011-11-08T05:32:00.000-08:00

Un paquete es una agrupación lógica codigo PL/SQL.

Una de las ventajas que nos brinda como varios lenguajes de programación es la MODULARIDAD. Permitiendo agrupaciones lógicas y un diseño más sencillo, eficaz y un mejor rendimiento.

Otra ventaja es la de permitirnos la SOBRECARGA* de modo que podemos crear procedimientos o funciones con el mismo nombre y con diferentes parámetros en el mismo paquete

DESCRIPCION

Un paquete está compuesto de dos partes:

- Especificación
- Cuerpo

A su vez este puede contener métodos privados y públicos, todo procedimiento declarado en la "Especificación" tiene carácter público; mientras que todo procedimiento declarado y desarrollado en el cuerpo tiene carácter privado.

Las variable definida dentro de un subprograma son locales, mientras que las definidas fuera son globales.

SINTAXIS

Cabecera:

CREATE [o REPLACE ] PACKAGE "nombre_del_paquete"
IS | AS
< declaración de variables públicas >
< declaración de los miembros y funciones públicas >
END "nombre_del_paquete";

Cuerpo:

CREATE [ o REPLACE ] PACKAGE BODY "nombre_del_paquete"
IS | AS
< declaración de las variables privadas >
< cuerpo de los miembros y funciones >
END nombre_del_paquete;

(*)La sobrecarga de métodos es la técnica mediante la cual se utiliza el mismo nombre para diferentes subprogramas dentro de un paquete.
La diferencia entre los métodos es el número y orden de parámetros.

Espero les haya servido.

Saludos!
Gondalf.

Diferencias entre las lecturas "Sequential" y "Scattered" de los DBfiles

2011-08-19T11:52:00.000-07:00

Ambos eventos significan tiempo esperado de E / S para completar las solicitudes de lectura.

- Lectura "sequential" de los DBfile:

Una operación de lectura de datos sequential lo hace dentro de memoria contigua (p3=1, pero pueden ser multiples bloques).
I/Os de single block son normalmente resultado del uso de índices.

Este evento es tambien utilizado durante los rebuilding de los controlfile y las lecturas de los datafile headers (P2=1).
Por lo general, este evento indica disk contention sobre las ecrituras de los índices.

- Lectura "scattered" de los DBfile:

Este tipo de lectura es similar a la anterior a excepción de que la sesión lee multiples bloques de datos y los "dispersa" (traduccion
literal de scattered) en diferentes y discontinuos buffers en la SGA.
Normalmente indican disk contention sobre full table scans. Rara vez, la datos proveninetes de un full table scan pueden ser puestos dentro de una buffer area contigua, entocnes estas esperas podrían mostrarse como lecturas "sequential" en lugar de "scattered".
Sin embargo estas lecturas nunca leeran solo un bloque (p3 siempre es >= 2)

Con el siguiente query podemos observar la diferencia de espera entra las lecturas sequential y scattered:

SELECT a.average_wait "SEQ READ", b.average_wait "SCAT READ"
FROM sys.v_$system_event a, sys.v_$system_event b
WHERE a.event = 'db file sequential read'
AND b.event = 'db file scattered read';

SEQ READ              SCAT READ
------------          ------------
.68                       1.5

Espero les haya servido.

Saludos!
Gondalf.

Order By Dinámico

2011-07-07T11:59:00.000-07:00

Gente, en esta oportunidad quería compartir con uds el hecho de generar un ORDER BY dinámico.

Qué quiere decir esto?, sería poder ordernar una consulta con el parámetro que le pasamos a través de un procedure/function.

Vamos directamente a la creacion del procedure:

create or replace procedure dinamic_text ( pCol in varchar2 )

type tCurr is ref cursor;

pCursor tCurr;

begin

open pCursor for 'select * from tablaTest order by ' || pCol;

loop

end loop;

close pCursor;

end;

Haciéndoles un pequeño resumen, lo que hicimos fue definir un cursor, le asignamos un string con nuetro select y le concatenamos

la variable que pasamos por parametro la cual debe contener el nombre de una de las columnas de la tabla, en este caso "tablaTest".

Espero les haya servido

Saludos!

Gondalf

Pd: gracias Jona, ultimamente estás con desarrollos raros, ja

Cómo determinar si solo son números?

2011-06-24T11:58:00.000-07:00

Este post está destinado a compartir con uds. 2 formas para determinar si una cadena está formada solo por números.

1) La primera de estas 2 formas es a través de la siguiente función:

CREATE OR REPLACE FUNCTION es_numero(v_valor IN VARCHAR2) RETURN NUMBER IS

v_numero NUMBER;

BEGIN

v_numero := TO_NUMBER(v_valor);

RETURN 0;

EXCEPTION WHEN VALUE_ERROR THEN

RETURN 1;

END;

El comportamiento de la función es devolver un 0 si la cadena que le pasamos esta compuesta solo por numero y un 1 si no lo está.

Lógicamente deberán usar la función en un WHERE con la condición = 0 o = 1.

2) La segunda forma que quiero compartir con uds. es a través de expresiones regulares.

Para esto la expresión regular que utilizamos es REGEXP_LIKE junto

con '^[0-9]+$', esto indica que evaluará en la cadena que le pasemos si está compuesta únicamente por números del 0 al 9 incluyendolos.

REGEXP_LIKE nos devuelve 'true' or 'false'.

Ejemplos:

SELECT *

FROM dual

WHERE REGEXP_LIKE ('788asd975', '^[0-9]+$');

Devolverá FALSE

SELECT *

FROM dual

WHERE REGEXP_LIKE ('asdasd', '^[0-9]+$');

Devolverá FALSE

SELECT *

FROM dual

WHERE REGEXP_LIKE ('788975', '^[0-9]+$');

Devolverá TRUE

Espero les haya servido

Saludos!

Gondalf

Borrar filas duplicadas

2011-06-21T11:23:00.000-07:00

De vez en cuando, por ejemplo por la falta de algún índice unico, puede suceder que insertemos registros duplicados en una de nuestras tablas.

Cuando notamos esta "duplicidad" seguramente querremos borrar todos los duplicados y dejar solo 1 registro, o no?

Habitualmente tenemos inconveniente es realizar este trabajo dado que borramos todos los registros involucrados en la duplicidad.

Para solucionar esto y asi poder borrar todos los duplicados salvo 1, debemos seguir algunos pasos.

Por ejemplo, vamos a cerar la tabla DUPLICADOS la cual va a tener 2 columnas:

SQL> create table duplicados (col1 number, col2 varchar2(20))

Hacemos algunos insert en la misma:

SQL> insert into duplicados values (1, 'primero')

SQL> insert into duplicados values (2, 'segundo')

SQL> insert into duplicados values (3, 'tercero')

Provocamos la duplicación de un registro:

SQL> insert into duplicados values (1, 'primero')

SQL> commit;

Bueno, como vemos tenemos un registro duplicado el cual podemos borrar de la siguiente manera:

SQL> delete from duplicados

2 where rowid not in

3 (select min(rowid)

4 from duplicados

5 group by col1, col2);

Para entender un poco lo que acabamos de hacer debemos prestar atención en el min(rowid), el cual de las dos filas duplicadas

solo va a borrar aquella con el menor rowid. Con esto nos aseguramos de borrar solo 1 de los 2 registros.

Para comprobar que ya no tenemos duplicados en la tabla ejecutamos el siguiente SELECT

SQL> select *

2 from duplicados

3 group by col1, col2

4 having count (*) > 1;

Espero les haya servido

Saludos!

Gondalf

START WITH .... CONNECT BY

2011-06-16T13:44:00.000-07:00

Para poder recorrer estructuras en forma de árbol usamos esta clausula.

Para hacer un ejemplo sencillo, supongamos que tenemos una tabla cargada como un arbol de categorías, en donde una categoría posee un posible padre, el mismo marcado por la columna “parent_id”.

Para recorrer todo una rama empezando por su id podemos hacer lo siquiente:

SQL> SELECT *

2 FROM categorías

3 START WITH id =

4 CONNECT BY PRIOR parent_id = id;

Lo que marca la dirección en que va a ser recorriendo el “arbol” es la claúsula PRIOR.

Si quisiéramos recorrer al revés, es decir desde una hoja hasta la raíz, debemos hacer:

SQL> SELECT *

2 FROM categorías

3 START WITH id =

4 CONNECT BY parent_id = PRIOR id;

A su vez, tenemos la pseudo-columna LEVEL, la cual nos brinda el “numero” de nivel en cual estamos, es decir que es un indicador de la profundidad del árbol en la cual nos encontramos (Oracle maneja hasta un nivel de profundidad de 255).

SQL> SELECT RPAD(' ', 2*(LEVEL-1)) || nombre

2 FROM categorías

3 START WITH parent_id IS NULL

4 CONNECT BY PRIOR parent_id = id;

Esto fue pantallazo general del tema pero sepan que existe mucho mas para investigar del START WITH y el CONNECT BY en Oracle

Espero les haya servido.

Saludos!

Gondalf

Filas Encadenadas

2011-06-15T11:52:00.000-07:00

Qué son????

Cuando el tamaño de una fila supere el tamaño de un bloque de la base de datos, puede generarse una migración o un encadenamiento de la fila.

Cuando hablamos de filas migradas nos referimos a una fila que se mueve completa de un bloque a otro dejando en el bloque original un puntero.

Cuando hablamos de filas encadenadas nos referimos a filas que requieren más de un bloque para su almacenamiento.

Si nos encontramos en presencia de una tabla con gran cantidad de filas encadenadas o migradas quiere decir que la base de datos va a tener que realizar varias lecturas físicas para poder procesar los datos de una misma fila lógica.

Como evitamos entonces el encadenamientos? Seteando un valor adecuado de pctfree en nuestra tabla.
Otra solución es un expdp/impdp de las tablas en cuestion

Si quisiéramos solucionar el problema de filas encadenadas debemos seguir los siguientes pasos:

Paso 1:

Correr el script utlchain.sql el cual crea la tabla CHAINED_ROWS.

Paso 2:

Revisamos la/s tabla para ver si poseen filas encadenadas. De poseerlas se insertaran en la tabla CHAINED_ROWS.

SQL> ANALYZE TABLE LIST CHAINED ROWS INTO chained_rows;

Paso 3:

Creamos una tabla auxiliar la cual llenaremos con las “filas duplicadas” de nuestra tabla

SQL> CREATE TABLE aux AS
2 SELECT * from
3 WHERE rowid IN
4 (SELECT head_rowid
5 FROM chained_rows
6 WHERE table_name = ‘nuestra tabla’);

Paso 4:

Borramos las filas encadenadas de nuestra tabla

SQL> DELETE FROM
2 WHERE rowid IN
3 (SELECT head_rowid
4 FROM chained_rows
5 WHERE table_name = ‘nuestra tabla’);

Paso 5:

Volvemos a insertar las tablas antes encadenadas en nuestra tabla

SQL> INSERT INTO SELECT * FROM aux;

Paso 6:

Realizar nuevamente la verificación para cerciorarse de que no queden filas encadenadas

SQL> DELETE CHAINED_ROWS WHERE table_name = 'nuestra tabla';

SQL> ANALYZE TABLE nombre_de_la_tabla
2 LIST CHAINED ROWS INTO chained_rows;

SQL> SELECT * FROM chained_rows WHERE table_name = 'nuestra tabla';

Espero les haya servido

Saludos!
Gondalf

Diferentes seteos del parametro AUDIT_TRAIL

2011-06-01T13:43:00.000-07:00

Seteando este parámetro, una vez seteado debemos reiniciar la base, podemos comenzar a auditar objetos en nuestra base de datos.

Los diferentes valores que le podemos setear son:

- none: Desactiva la auditoria en la base de datos.

- os: Activa la auditoria, dejando los registros de auditoría en un archivo en nuestro sistema operativo.

- db: Activa la auditoria, los registros de auditoría son escritos en nuestra base de datos. Mas precisamente en la tabla SYS.AUD$.

- db,extended: Activa la auditoria, dejando los registros de auditoría en nuestra base de datos. Mas precisamente en la tabla SYS.AUD$.
Ademas, completa los campos SQLBIND and SQLTEXT de la tabla SYS.AUD$, cabe aclarar que dichos campos son del tipo CLOB.

- xml: Activa la auditoria, en donde los registros de auditoría son escritos en formato XML en un archivo en el sistema operativo

- xml,extended: Igual a la opción anterior pero agregando los valores SQLBIND and SQLTEXT al archivo.

Espero les haya servido.

Saludos!
Gondalf

ORA-00600:[qcsgpvc3], [], [], [], [], [], [], []

2011-05-31T08:06:00.000-07:00

Si bien estamos en presencia de un un error 600 de oracle, no es nada por lo que debamos alarmarnos.

Se trata de una compilación errónea de un package, por ejemplo, pasarle un argumento como parámetro cuyo nombre sea igual al de una columna de una tabla que se utilice en el mismo.

La solución es tan simple como rever el package.

Este error siempre viene de la mano de un trace en el cual podemos identificar la sesión, les copio un extracto de donde ubicar dicha sesión:
*** ACTION NAME:() 2011-05-30 17:38:14.816
*** MODULE NAME:(TOAD 9) 2011-05-30 17:38:14.816
*** SERVICE NAME:(xxxxx) 2011-05-30 17:38:14.816
*** SESSION ID:(243.17186) 2011-05-30 17:38:14.816
*** 2011-05-30 17:38:14.816

Espero les haya servido.

Saludos!
Gondalf

Global Temporary Table

2011-05-13T12:39:00.000-07:00

El comando "create global temporary tables" crea una tabla temporal para cada sesión. Eso significa que los datos no se comparten entre sesiones y
se eliminan al final de la misma.
Todos los usuarios que tengan permisos sobre la tabla podrán realizar operaciones DML sobre la misma.

Existen 2 tipos de datos temporales de acuerdo al tipo de tabla temporal:

- Datos con la duración de una transacción.
- Datos con la duración de una sesión.

Esto lo podemos hacer mediante las claúsulas:

- ON COMMIT DELETE ROWS: las filas que se insertan en la tabla son removidas de la misma ante un COMMIT o un ROLLBACK.
- ON COMMIT PRESERVE ROWS: las filas insertadas en la tabla permaneceran en la misma hasta que la sesion de usuario finalice.

A modo de ejemplificar esta explicación, veamos un pequeño ejemplo

SQL> create global temporary table prueba ( id int) on commit delete rows;

Table created.

SQL> insert into prueba values (1);

1 row created.

SQL> insert into prueba values (2);

1 row created.

SQL> insert into prueba values (3);

1 row created.

SQL> select * from prueba;

ID
---------
1
2
3

SQL> commit;

Commit complete.

SQL> select * from prueba;

no rows selected

/*****************/

SQL> create global temporary table prueba2 ( id int) on commit preserve rows;

Table created.

SQL> insert into prueba2 values (1);

1 row created.

SQL> insert into prueba2 values (2);

1 row created.

SQL> insert into prueba2 values (3);

1 row created.

SQL> select count(1) from prueba;

COUNT(*)
----------
153

SQL> commit;

Commit complete.

SQL> select * from prueba;

COUNT(*)
----------
153

Espero les haya servido.

Saludos!
Gondalf.

Convertir un LONG RAW a un VARCHAR

2011-05-06T12:46:00.000-07:00

Para poder visualizar una columna LONG RAW o bien si la queremos comparar contra otro varchar, lo que podemos hacer es lo siguiente:

Paso 1:

Creamos el paquete ULT_RAW, para debemos buscar su script de creacion en el direcotrio $ORACLE_HOME\rdbms\admin\.
En este directorio vamos utilizar dos .sql, uno es utlraw.sql y el otro prvtrawb.plb

Con estos scripts vamos a crear el paquete que nos va a permitir convertir nuestros LONG RAW

SQL> @utlraw.sql

Package created.

No errors.

SQL> @prvtrawb.plb

Package body created.

No errors.

Synonym created.

Grant succeeded.

Paso 2:

Ahora estamos en condiciones de realizar un mini-script el cual nos permita justamente ver/comparar nuestro dato

declare
salida varchar(4000);
var long raw;
cursor cur1 is select "columna tipo long raw" from "tabla";
begin
open cur1;
loop
fetch cur1 into var;
exit when cur1%notfound;
salida:=UTL_RAW.CAST_TO_VARCHAR2(var);
dbms_output.put_line(salida);
end loop;
end;

Debemos reemplazar solo "columna tipo long raw" y "tabla", por el nombre de nuestra tabla y del campo a evaluar.

Espero les haya servido

Saludos!
Gondalf.

Table Partitioning

2011-03-30T13:08:00.000-07:00

Las tablas particionadas son un feature el cual nos permite particionar una tabla siguiendo un determinado criterio.

Por ejemplo imaginemos una tabla de facturas, en donde tenemos el detalle de nuestra facturación a lo largo de 5 años, 2005, 2006… 2009, si quisiéramos hacer:

SELECT SUM(precio) FROM facturación WHERE ano = 2006;

En este ejempo se deberia recorrer toda la table (imaginemos que hablamos de 30 millones de registros en total,es mucho no?), por este motivo un criterio posible para particionar la tabla seria por el año de efectuada la fatura

Luego de esta pequeña intro, veamos algunos ejemplos:

1-) Creación de tablas particionadas

CREATE TABLE facturacion (id number, precio number(10,2), ano (number 4))
PARTITION BY RANGE (ano)
PARTITION p1 VALUES LESS THAN (2007) TABLESPACE tab1,
PARTITION p2 VALUES LESS THAN (2009) TABLESPACE tab2,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tab3;

Con este simple script creamos una tabla particionada con 3 particiones teniendo como criterio de separación el campo “ano”, todas estas particiones las estamos almacenando en tablespaces separados (aunque esto no es necesario es aconsejable)

Existen distintos tipos de “partition” pero lo vamos a dejar para otro post ya que la intención de este es que sea sencillo como para empezar a comprender el tema.

2-) Cambiar una tabla particionada

Podemos agregar una partición adicional a nuestra tabla particionada, para esto debemos utilizar la clausula SPLIT PARTITION

Por ejemplo podemos agregar una partición a nuetra tabla de ejemplo:

ALTER TABLE facturacion ADD PARTITION p4 VALUES LESS THAR (2010);

Cabe aclarar que podríamos determinar el tablespaces que almacenará la nueva partición.

3-) Eliminar particiones

ALTER TABLE facturacion DROP PARTITION p2;

En este caso si existiese un indice deberiamos hacerle un REBUILD.

Tenemos otra forma para no rebuildear el índice y es:

ALTER TABLE facturacion DROP PARTITION p2 UPDATE GLOBAL INDEX;

4-) Unir particiones

Si quisieramos unir 2 o mas particiones debemos hacer lo siguiente

ALTER TABLE facturacion MERGE PARTITION p2 AND p3 INTO PARTITION pnueva;

5-) Modificar particiones

Para agregar o quitar valores de una particion utilizamos la clausula

MODIFY PARTITION …. ADD/DROP VALUES

ALTER TABLE nacionalidad MODIFY PARTITION sud ADD VALUES (‘ARGENTINA’, ‘PERU’);

6-) Separar particiones

Podemos separar una particion en una o mas particiones de la siguiente manera:

ALTER TABLE facturacion SPLIT PARTITION p1 INTO
(PARTITION p11 VALUES LESS THAN (2006)
(PARTITION p11 VALUES LESS THAN (MINVALUE));

7-) Truncar una partición

ALTER TALE facturacion TRUNCATE PARTITION p11;

Espero les haya servido.

Saludos!

Gondalf.

Claúsula SELECT INTO

2010-12-02T12:32:00.000-08:00

Se utiliza para almacenar lo que retorna el select en variables predefinidas.

A modo de ejemplo

SQL> declare
1 var dual.dummy%type;
2 begin
3 select dummy into var
4 from dual;
5
6 dbms_output.put_line('Name: '||var);
7 end;
8 /

En este caso lo que devuelve el select, es decir el valor de la columna “dummy”, va a parar a la variable de nombre “var” que fue definida con el tipo de dato dual.dummy%type, es decir que adopta el tipo de dato de la columna dummy de la tabla dual.

Otra forma de utilizarlo es cuando se devuelve toda una fila, es decir:

SQL> declare
1 var dual%rowtype;
2 begin
3 select * into var
4 from dual;
5
6 dbms_output.put_line('Name: '||var.dummy);
7 end;
8 /

En este caso, dentro de la variable var tenemos toda la fila devuelta por el select, y a cada campo de la fila que devolvió lo podemos acceder mediante ., como es el caso de var.dummy.

Cabe aclarar que también podemos utilizar variables de los tipos de datos que nos provee la base, por ejemplo varchar2, integer, clob, etc.

Aunque como estamos viendo con esta sentencia, solo podemos devolver una fila, lo que si podemos es devolver múltiples columnas y asignarlas a múltiples variables. Por ejemplo

SQL> declare
1 var1 prueba.columna1%type;
2 var2 prueba.columna2%type;
3 begin
4 select columna1, columna2 into col1, col2
5 from prueba;
6
7 dbms_output.put_line('Name: '||var1||''||var2||);
8 end;
9 /

Si devolviésemos más de 1 fila en nuestro select, tendríamos el siguiente error:

declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

Otro posible error se presentaría en el caso de que nuestro select no devuelva filas

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Cabe aclarar que ambos errores se puede salvar dentro de nuestro PL/SQL con la estructura “WHEN …. THEN”

Espero les haya servido

Saludos!
Gondalf.

Library cache lock

2010-10-15T12:12:00.000-07:00

Este post si bien es cortito creo que es de bastante de utilidad.

Con los siguientes querys vamos a ser capaces de identificar las sesiones que están generando el evento “Library cache lock”

SQL> select saddr from v$session where sid in (select sid from v$session_wait where event like 'library cache lock');

Sesión Bloqueante

SQL> SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
2 WHERE SADDR in
3 (SELECT KGLLKSES FROM X$KGLLK LOCK_A
4 WHERE KGLLKREQ = 0
5 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
6 WHERE KGLLKSES = 'el saddr del primer query' /* BLOCKED SESSION */
7 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
8 AND KGLLKREQ > 0)
9 );

Sesión Bloqueada

SQL> SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
2 WHERE SADDR in
3 (SELECT KGLLKSES FROM X$KGLLK LOCK_A
4 WHERE KGLLKREQ > 0
5 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
6 WHERE KGLLKSES = 'el saddr del primer query' /* BLOCKING SESSION */
7 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
8 AND KGLLKREQ = 0)
9 );

Espero les haya servido.

Saludos!
Gondalf.

Insert sobre una columna CLOB

2010-09-27T12:36:00.000-07:00

Paso 1) Creamos una tabla modo de prueba (que contenga una columna CLOB)

SQL> CREATE TABLE prueba
2 (titulo VARCHAR2(40),
3 author VARCHAR2(40),
4 texto CLOB);

Table created.

Paso 2) Realizamos el insert que queremos realizar, llamando a la función EMPTY_CLOB() la cual deja un puntero vacio a un objeto CLOB.

SQL> INSERT INTO prueba VALUES ('Hobbit','Tolkien',EMPTY_CLOB());

1 row created.

SQL> commit;

Paso 3) Updeteamos la columna CLOB utilizando PL/SQL

DECLARE
my_text_handle CLOB;
my_buffer VARCHAR2(32767);
my_add_amt NUMBER := 0;
my_offset INTEGER := 1;

BEGIN
my_buffer := 'prueba con clob';
my_add_amt := length(my_buffer);

SELECT text
INTO my_text_handle
FROM prueba
WHERE title = 'Hobbit' FOR UPDATE;

DBMS_LOB.WRITE(my_text_handle, my_add_amt, my_offset, my_buffer);

COMMIT;

END;

PL/SQL procedure successfully completed.

SQL> select texto from prueba;

TEXT
-----------------------
Prueba con clob

Espero les haya servido

Saludos!
Gondalf

ORA-16826: apply service state is inconsistent with the DelayMins property

2010-09-16T07:48:00.000-07:00

Quería compartir con uds la solución a este particular error que se genera al utilizar Dataguard.

El origen del error está asociado a dos posibles causas:

1- El “Apply service” se levantó con la especificación de la opción “real time apply” o con la opción NODELAY siempre que el parámetro DelayMins es mayor a 0

2- El “Apply service” se levantó sin la especificación de la opción “real time apply” o sin la opción NODELAY siempre que el parámetro DelayMins es 0

Pues bien, sabiendo ahora como se origina el error podemos decir que la solución es bastante sencilla, lo único que necesitamos realizar es poner la configuración del dataguard bróker en el estado disable para luego hacer un reenable del mismo.

A continuación mostraremos los pasos a seguir:

Paso 1: Nos conecatamos al bróker

testbd@serverTEST ] dgmgrl

DGMGRL for HPUX: Version 10.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys
Password:
Connected.
DGMGRL>

Paso 2: Ponemos en disable la configuración

DGMGRL> disable configuration
Disabled.

Paso 3: Ponemos en enable nuevamente la configuracion y el error desaparecerá

DGMGRL> enable configuration
Enabled.

En este momento cuando hagan un “show configuration” verán que el estado de la misma es SUCCESS.

Espero les haya servido

Saludos!
Gondalf

Creación de un SQL Tuning Advisor

2010-08-20T11:28:00.000-07:00

==================================

= Eliminamos la tarea de tuning

==================================

exec dbms_sqltune.drop_tuning_task('task1');

==================================

= Creamos la tarea de tuning

==================================

DECLARE

tarea_tuning VARCHAR2(30);

sql CLOB;

BEGIN

sql := 'SELECT * from dual';

tarea_tuning := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => sql,

user_name => USER,

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'task1',

description => 'prueba de tarea de tuning');

END;

/* hay un parametro mas de bind_list */

==================================

= Verificamos que la tarea este creada

==================================

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = USER;

======================================

= Ejecutamos la tarea

======================================

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'task1' );

END;

===================================

= Controlamos el estado de la tarea

===================================

SELECT task_id, status FROM USER_ADVISOR_TASKS WHERE task_name = 'task1';

======================================

= Controlamos el progreso del SQL Tuning Advisor

======================================

SELECT sofar, totalwork

FROM V$ADVISOR_PROGRESS

WHERE task_id = (SELECT task_id FROM USER_ADVISOR_TASKS WHERE task_name = 'task1');

=====================================

= Mostramos el resultado del SQL Tuning Advisor

=====================================

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'task1') FROM DUAL;

Numero del día

2010-08-20T06:55:00.000-07:00

A muchos les habrá pasado que de acuerdo a como se conecten a la base de datos, sea desde una terminal, desde el server, a través de un ODBC cambia la manera en que Oracle les devuelve el numero de día de una fecha en particular.

Esto sucede ya que una terminal puede tomar otro NLS_LANG distinto al del servidor.

A continuación vamos a ver como cambia el numero de día (por lo general se utiliza el domingo o el lunes como 1) de acuerdo al cambio de parámetro en el NLS_LANG

ora1@test ] export NLS_LANG='AMERICAN';
ora1@test ] sqlplus / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 10.2.0.4.0 - Production

SQL> select to_char(sysdate, 'D') from dual;

T
-
2

ora1@test ] export NLS_LANG='ENGLISH'
ora1@test ] sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 20 10:40:43 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 10.2.0.4.0 - Production

SQL> select to_char(sysdate, 'D') from dual;

T
-
1

Espero les haya servido

Saludos!
Gondalf.

Resultado de un query de varias filas concatenadas en una

2010-06-10T14:12:00.000-07:00

La idea de este post es acercarles una función que quizás en algún momento les sea de utilidad.

El propósito de la función es concatenar el resultado de un query que devolvió varias filas en solo 1,
la función es la siguiente:

CREATE OR REPLACE FUNCTION concatfilas(qry IN VARCHAR2) RETURN VARCHAR2 IS
filaFinal VARCHAR2(4000);
result VARCHAR2(4000);
cur sys_refcursor;
BEGIN OPEN cur FOR qry;
LOOP
FETCH cur INTO result;
EXIT WHEN cur%NOTFOUND;
IF filaFinal IS NULL THEN
filaFinal := resul;
ELSE
filaFinal := filaFinal ',' result;
END IF;
END LOOP;
RETURN filaFinal;
END;
/

Ejemplo

SQL> create table prueba (nombre varchar2(30));

Table created.

SQL> insert into prueba values(‘Luis’);

1 row created.

SQL> insert into prueba values(‘Bruno’);

1 row created.

SQL> insert into prueba values(‘Matias’);

1 row created.

SQL> insert into prueba values(‘Gonzalo’);

1 row created.

SQL> commit;

1 row created.

SQL> select concatfilas('SELECT nombre FROM prueba') as nombres
2 FROM dual;

NOMBRES
------------------------------------------------------------------
Luis, Bruno, Matias, Gonzalo

Espero les haya servido

Saludos!
Gondalf.