Parametros EXCLUDE e INCLUDE en DataPump

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

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

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

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

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 )

as

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?

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

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

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

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

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], [], [], [], [], [], [], []

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

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

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

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.