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.

Claúsula SELECT INTO

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

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

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

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

Copyright (c) 2000, 2005, Oracle. All rights reserved.

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