Archivar tabla de log/auditoría en una tabla particionada

Con “tabla de log/auditoría” queremos decir cualquier tabla en la que solo se hacen inserts y que no tiene FKs apuntando a ella, cuyo crecimiento no es constante y cuyos datos queremos ir archivando dependiendo de eventos que no tienen por qué ser periódicos en el tiempo. Por ejemplo, una tabla de log cuyos datos una vez me puede interesar archivar después de la matrícula, en otra ocasión pasado un mes, y otra ocasión pasado 1 año, etc; es decir, cuando sea necesario.

Veamos qué funcionalidades de Oracle podemos utilizar para proceder a este tipo de “archivado”:

En la GT sobre Particionamiento con Oracle 11g, hablamos del particionamiento by system o particionamiento “manual”, que permite crear una tabla particionada a la que añadirle particiones de forma manual, sin ningún criterio automático de reparto de las filas de la tabla entre las particiones que la componen.
Además, dentro de las funcionalidades que proporciona el particionamiento, existe la posibilidad de hacer un exchange partition para intercambiar una tabla con una partición de otra tabla (particionada), de modo que en una sola operación se “traspasan” los datos de la tabla a la partición, sin que los datos se muevan de su sitio en el disco, y tan solo con operaciones a nivel de diccionario de datos (se intercambian las extensiones de ambos segmentos, de modo que la partición apunta a las extensiones que tenía la tabla, y viceversa, realizándose un intercambio de datos sin que éstos se muevan de sitio).

Situación Inicial	Exchange Partition	Intercambio Realizado

De este modo, dada la “Tabla log” (TLOG), sin particionar, podemos:

crear una “Tabla particionada” (TLOGP), particionada by system con una partición, y
pasar todos los datos de TLOG a TLOGP simplemente intercambiando TLOG por la partición de TLOGP haciendo “exchange partition”.

Y posteriormente, cada vez que necesitemos archivar TLOG de nuevo:

añadiremos una partición a TLOGP y
volveremos a pasar todos los datos de TLOG a TLOGP simplemente intercambiando TLOG por la nueva partición de TLOGP haciendo, otra vez, “exchange partition”.

Este proceso se puede ir repitiendo cuando sea necesario, creando una partición nueva cada vez en TLOGP, y repitiendo la operación “exchange partition”, de modo que iremos creando particiones en TLOGP con el contenido de TLOG en el momento de hacer dichas operaciones de intercambio. Y así iremos “archivando” TLOG en TLOGP conforme vaya siendo necesario.

Posibles problemas

Si TLOG tiene índices, después de hacer el “exchange partition” tendremos que hacer un rebuild de dichos índices, pues seguirán apuntando a unas filas que ya no existen en TLOG.
Si TLOG tiene una PK a la que además apuntan FKs de otras tablas, será necesario desactivar dichas FKs para poder hacer el “intercambio”, y además no podremos reactivarlas pues los datos a los que apuntan ya no están en TLOG.

Ejemplo

Ejemplo de “archivado” de la tabla DEPT en DEPT_P:

No se puede crear una tabla particionada por SYSTEM con un “create table as select”

      create table dept_p partition by system (partition p1) as select * from dept where deptno=55; 
                                                          *
      ERROR at line 1:
      ORA-14704: Create table as select disallowed for SYSTEM patitioned tables

Así que sigue los pasos siguientes:

Así que creo una tabla particionada DEPT_P a partir del sql de la tabla original DEPT

      SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT', 'JUANLU') FROM DUAL; 

      DBMS_METADATA.GET_DDL('TABLE','DEPT','JUANLU') 
      --------------------------------------------------------------------------------
     
      CREATE TABLE "JUANLU"."DEPT"
       (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         PRIMARY KEY ("DEPTNO")
      ...

      CREATE TABLE "JUANLU"."DEPT_P" 
       (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13))
      TABLESPACE "USERS"
      partition by system (partition p1);

Si quiero añadir una partición sería tan sencillo como hacer
```
      alter table juanlu.dept_p add partition p2; 
```
Debería bloquear la tabla origen antes de desactivar las FKs que apuntan a la PK de dicha tabla, o de hacer el “exchange”:
```
      lock table juanlu.dept in exclusive mode; 
```

En otra sesión, hay que desactivar las FKs que apuntan a la PK de la tabla origen (hazlo en otra sesión porque las sentencias DDL llevan commit implícito y desaparece el bloqueo anterior, además tb habría que bloquear las tablas cuyas FKs se van a desactivar.

      select 'alter table '||b.owner||'.'||b.table_name||' disable constraint '||b.constraint_name||';'||chr(10)|| 
        'lock table '||b.owner||'.'||b.table_name||' in exclusive mode;' 
      from all_constraints a, all_constraints b 
      where a.owner='JUANLU' and a.table_name='DEPT' 
      and b.r_owner=a.owner and b.r_constraint_name=a.constraint_name; 

      'LOCKTABLE'||B.OWNER||'.'||B.TABLE_NAME||'INEXCLUSIVEMODE;'||CHR(10)||'ALTERTABL 
      --------------------------------------------------------------------------------
      alter table JUANLU.EMP disable constraint SYS_C00116437; 
      lock table JUANLU.EMP in exclusive mode;

Posibles problemas: además del hecho de que estas FKs se van a quedar sin PK cuando hagas el “exchange” de la tabla, tb puedes tener FKs dentro de la propia tabla (esto me ha pasado haciendo la prueba con la tabla EMP). En este caso habría que desactivar la citada FK en la sesión donde está bloqueada la tabla, e inmediatamente después volver a bloquearla:

      alter table JUANLU.EMP disable constraint SYS_C00116436; 
                       *
      ERROR at line 1:
      ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Ahora, en la sesión del “lock table”, podemos intercambiar la partición con la tabla origen sin los índices
```
      alter table juanlu.dept_p exchange partition p1 with table juanlu.dept; 

      Table altered. 
```

Hay que reconstruir todos los índices de la tabla origen pues han quedado en estado UNUSABLE:

      select 'alter index '||a.owner||'.'||a.index_name||' rebuild;' 
        from all_indexes a 
        where a.table_owner='JUANLU' and a.table_name='DEPT'; 

      'ALTERINDEX'||A.OWNER||'.'||A.INDEX_NAME||'REBUILD;' 
      --------------------------------------------------------------------------------
      alter index JUANLU.SYS_C00116434 rebuild; 

      Index altered.

(Más abajo veremos que parte de éste punto no será posible hacerlo, concretamente activar las FKs). Y volveríamos a activar las FKs que apuntan a la tabla origen (realmente no será posible como vemos más abajo), reconstruyendo previamente los índices de cada una de ellas:
```
      select 'alter index '||a.owner||'.'||a.index_name||' rebuild;'||chr(10)|| 
        'alter table '||b.owner||'.'||b.table_name||' enable constraint '||b.constraint_name||';' 
      from all_constraints a, all_constraints b 
      where a.owner='JUANLU' and a.table_name='DEPT' 
      and b.r_owner=a.owner and b.r_constraint_name=a.constraint_name; 

      'ALTERINDEX'||A.OWNER||'.'||A.INDEX_NAME||'REBUILD;'||CHR(10)||'ALTERTABLE'||B.O 
      --------------------------------------------------------------------------------
      alter index JUANLU.SYS_C00116434 rebuild; 
      alter table JUANLU.EMP enable constraint SYS_C00116437; 
                                               *
      ERROR at line 1:
      ORA-02298: cannot validate (JUANLU.SYS_C00116437) - parent keys not found; 
      
```
En cuyo caso, como se observa, dará un error pues los datos han desaparecido al hacer el “exchange”, lo que demuestra que no podemos tener FKs apuntando a la tabla a la que vamos a hacer el “exchange”. Por tanto, podemos eliminar la activación de FKs del paso 7, y en el caso en que en el paso 3 obtengamos algún resultado, y por tanto existe alguna FK apuntando a la tabla candidata al “exchange”, tenemos que cancelar el proceso, o bien, desactivar las FKs ejecutando el paso 4, y no volver a activarlas en el 7. Por último verificaremos que se han liberado los bloqueos que hemos usado, por si acaso algo ha ido mal, que no nos volvamos locos ;)

— Juan Luis Serradilla Amarilla 2013/05/17