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

Creación de un SQL Tuning Advisor

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

= 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

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

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

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.