DBMS_REDEFINITION - Particionar tabla | ExpoDBA

DBMS_REDEFINITION - Particionar tabla

DBMS_REDEFINITION - Particionar tabla

Este procedimiento muestra como realizar un particionado de una tabla existente sin afectar el entorno productivo ( o afectandolo por unos segundos)


Los pasos a realizar para este procedimiento son:

  • Crear una nueva tabla idem a la tabla origen pero particionada con todas las particiones.
  • Verificar si la tabla puede ser redefinida con este package (procedure DBMS_REDEFINITION.CAN_REDEF_TABLE)
  • Mover los datos de Origen a destino (procedure DBMS_REDEFINITION.START_REDEF_TABLE)
  • Sincronizar tabla y crear indices y constraints en tabla destino (procedure DBMS_REDEFINITION.SYNC_INTERIM_TABLE + creaciones...)
  • Cambiar nombre de tablas y aplicar cambios realiados durante mantenimiento (procedure DBMS_REDEFINITION.FINISH_REDEF_TABLE)
  • Operaciones de limpieza (Borrado de tabla Origen +  renombrado de indices y constraints)


En este ejemplo vamos a particionar la tabla OWNER_TABLAS.TABLA_ORIGEN utilizando como pivote la tabla TABLA_DESTINO
   
Crear una nueva tabla idem a la tabla origen pero particionada con todas las particiones y cualquier otra modificación que se quiera realizar (tablespace, almacenamiento, etc)
En este ejemplo lo muestro con las mismas columnas pero es posible hacerlo agregando, quitando o modificando columnas.

CREATE TABLE OWNER_TABLAS.TABLA_DESTINO ....

Verificar si la tabla puede ser redefinida con este package (procedure DBMS_REDEFINITION.CAN_REDEF_TABLE)

set serveroutput on;
EXEC Dbms_Redefinition.Can_Redef_Table('OWNER_TABLAS', 'TABLA_ORIGEN');

Mover los datos de Origen a destino (procedure DBMS_REDEFINITION.START_REDEF_TABLE)
Activamos parallel para la session así va más rápido

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

Si no hay errores empezamos con la redefinición
Este procedimiento hace lo mismo que un insert as select de una tabla a la otra, pero almacenando todos los cambios que se realicen a partir de este momento en la tabla origen, en una tabla OWNER_TABLAS.MLOG$_TABLA_ORIGEN.

BEGIN
  DBMS_REDEFINITION.start_redef_table(
    uname      => 'OWNER_TABLAS',       
    orig_table => 'TABLA_ORIGEN',
    int_table  => 'TABLA_DESTINO',
    options_flag => dbms_redefinition.cons_use_pk
    );
END;
/

En este momento las tablas están iguales salvo por los registros que se hayan insertado.
Si queremos que los indices tambien esten particionados entonces debemos crearlos ahora con el formato definitivo en la tabla TABLA_DESTINO.

Es importante ir monitoreando la tabla MLOG donde se registran todos los cambios que se fueron generando mientras ejecutamos el cambio.

select count(9) from OWNER_TABLAS.MLOG$_TABLA_ORIGEN;


Sincronizar tabla y crear indices y constraints en tabla destino (procedure DBMS_REDEFINITION.SYNC_INTERIM_TABLE + creaciones...)

Si vemos que se acumulan los registros, entre paso y paso podemos pasar los tablos de los MLOG a la tabla destino
Sincronizamos tabla

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OWNER_TABLAS', 'TABLA_ORIGEN', 'TABLA_DESTINO');
END;
/

Para el resto de los objetos que queremos traspasar a la tabla destino sin ningun tipo de cambio, a partir de 10G tenemos la funcion COPY_TABLE_DEPENDENTS que lo que hace es crear todos los objetos dependientes en el destino, donde si alguno de los indices ya los creamos a mano, van a dar error al querer recrearlos, lo que no es un problema mientras la tabla destino quede como queremos que quede.
Podemos evitar que este procedimiento cree los indices si ponemos copy_indexes => 0. Lo mismo  pasa con los privileges, constraints y triggers

set serveroutput on;
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname => 'OWNER_TABLAS',
    orig_table => 'TABLA_ORIGEN',
    int_table => 'TABLA_DESTINO',
   copy_indexes => 1,
   copy_triggers => TRUE,
   copy_constraints => TRUE,
   copy_privileges=> TRUE,
   ignore_errors => TRUE,
   num_errors => num_errors);
   dbms_output.put_line ('Errores: '||num_errors);
END;
/

Luego de ejecutar el comando anterior, podemos verificar si hubo errores al copiar los objetos dependientes, podemos consultar esta tabla

col ddl_txt for a60
set pages 200
select object_name, object_type, base_table_name, ddl_txt
 from DBA_REDEFINITION_ERRORS;

Si se encuentran errores, entonces hay que verificar que objetos se crearon y cuales no para poder corregir antes del traspaso.

Para ver cuales son los objetos que quedaron registrados para hacer elpasaje, podemos consultar esta vista.

select OBJECT_TYPE, OBJECT_NAME, INTERIM_OBJECT_NAME
from DBA_REDEFINITION_OBJECTS
where BASE_TABLE_OWNER = 'OWNER_TABLAS'
and BASE_TABLE_NAME = 'TABLA_ORIGEN';

Si alguno de los objetos, los creamos a mano porque el proceso de COPY_TABLE_DEPENDENTS no pudo crearlos (verificar en DBA_REDEFINITION_ERRORS) o porque queriamos particionados los indices o modificar alguno de los objetos dependientes y los creamos a mano en un paso anterior, entonces podemos registrarlos para que cuando se haga el pasaje definitivo se agreguen estas modificaciones tambien

 

Nota: Si no se creo ningun objeto dependiente a mano, obviar este paso

BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
   uname         => 'OWNER_TABLAS',
   orig_table    => 'TABLA_ORIGEN',
   int_table     => 'TABLA_DESTINO',
   dep_type      => DBMS_REDEFINITION.CONS_INDEX,
   dep_owner     => 'OWNER_TABLAS',
   dep_orig_name => 'INDICE_EN_TABLA_ORIGEN',
   dep_int_name  => 'INDICE_EN_TABLA_DESTINO');
END;
/

Ahora es momento de verificar que ambas tablas tengan las mismas constraints.
Hay que observar con cuidado las diferencias porque pueden ser creadas con distintos nombres, por lo que se mostraran como diferencias, pero en realidad son las mismas.
De todos modos verificar que esten igual en ambas tablas en todo su entorno

select constraint_type, count (9)
from (
select  CONSTRAINT_TYPE
from dba_constraints
where table_name = 'TABLA_ORIGEN'
minus
select CONSTRAINT_TYPE
from dba_constraints
where table_name = 'TABLA_DESTINO')
group by constraint_type;

Volvmeos a sincronizar las tablas para que no se acumulen registros.
Sincronizamos tabla

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OWNER_TABLAS', 'TABLA_ORIGEN', 'TABLA_DESTINO');
END;
/

Hasta acá hicimos todo el trabajo sin ocupar UNDO, solo creamos indices (uso de sort, TEMP y espacio en el tablespace que corresponde) objetos de diccionario y la tabla destino con su correspondiente espacio.

 

Cambiar nombre de tablas y aplicar cambios realiados durante mantenimiento (procedure DBMS_REDEFINITION.FINISH_REDEF_TABLE)

/*EN ESTE PUNTO PODEMOS VOLVER TODO ATRAS EJECUTANDO LO SIGUIENTE*/
*********************************************************************************************************************************
Este es un importante punto de control, si queremos, podemos volver todo atras, borrar los objetos nuevos, eliminar la MLOG y dejar todo como estaba antes de empezar.
Si eso es lo que queremos, podemos ejecutar:

 

BEGIN dbms_redefinition.abort_redef_table( uname => 'OWNER_TABLAS', orig_table => 'TABLA_ORIGEN', int_table => 'TABLA_DESTINO'); END; /

Si ejecutamos esto, se borrará la tabla intermedia, todos sus objetos dependientes y quedará todo el ambiente justo como antes de empezar.
*********************************************************************************************************************************
Si decidimos continuar ejecutamos:

--Terminar redefinition
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('OWNER_TABLAS', 'TABLA_ORIGEN', 'TABLA_DESTINO');
END;
/

Este ultimo procedimiento genera un lock EXCLUSIVE por un lapso muy corto de tiempo de la tabla.


Internamente el procedimiento hace lo siguiente:
Los procesos siguen accediendo a la tabla_origen, en el momento que ejecutamos FINISH_REDEF_TABLE , nuestra sesion entra en espera por el lock exclusive, cuando lo gana, bloquea la tabla_origen, hace un rename de la tabla_origen (al nombre de la destino) y un rename de la tabla destino (al nombre de tabla_origen).
Lo mismo hace con los indices y constraints, y al terminar, pasa los datos que quedaron pendientes en la tabla MLOG y libera el lock.
Al liberar el LOCK la tabla destino ahora tiene el nombre de la origen y la de origen el nombre de la destino, por lo tanto las transacciones empiezan a caer todos sobre la tabla particionada.
De esta manera la operatoria continúa con total normalidad sobre la nueva tabla particionada.

 

 

Operaciones de limpieza (Borrado de tabla Origen +  renombrado de indices y constraints)

Verificamos que no nos queden Packages invalidos.

--Compilar objetos invalidos
spool compila.sql
select 'alter '|| object_type || ' '|| owner||'.'|| object_name ||' compile;'
from dba_objects
where status = 'INVALID'
and object_type <> 'PACKAGE BODY';
spool off
@compila

spool compila.sql
select 'alter package '|| owner||'.'|| object_name ||' compile body;'
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY';
spool off
@compila

Ahora podemos borrar la tabla que utilizamos como intermedia (que a esta altura es la no particionada)

--Borrar tabla intermedia
drop table OWNER_TABLAS.TABLA_DESTINO;

Es posible que al querer borrar la tabla intermedia, nos encontremos que su PK es referenciada por FK de otras tablas que quedaron obsoletas.


Ejemplo:
Teniamos una FK en una tabla X que apuntaba a la PK de tabla_origen
Al ejecutar el redefinition se creo una nueva FK en tabla X que apunta a la PK tabla tabla_destino.
Cuando se hace la conversión de nombres, ambas FK continuan activas, por lo tanto, antes de borrar la tabla_destino, es necesario borrar las FK que apuntan a tabla_destino en la tabla X, ya que fueron renombradas por las de tabla origen.

VERIFICAMOS POR INDICES INVALIDOS

En caso de que haya invalidos, hacmos un rebuild de los mismos

select owner, index_name, 'NONE' partition, 'NONE' subpartition
from dba_indexes
where status = 'INVALID'
union all
select index_owner owner, index_name index_name, partition_name partition, 'NONE' subpartition
from dba_ind_partitions
where status = 'UNUSABLE'
union all
select index_owner owner, index_name index_name, partition_name partition, SUBPARTITION_NAME subpartition
from dba_ind_subpartitions
where status = 'UNUSABLE';

Verificamos que no hayan quedado  constraints DISABLED en tablas que no sea la de pivote.

--Check constraints
select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
from dba_constraints
where status = 'DISABLED'
and owner = 'OWNER_TABLAS';

Si todo salió bien, entonces debemos tener la tabla TABLA_ORIGEN particionada.

De todos modos, no todos los ambientes son iguales por lo que recomiendo encarecidamente que se verifique el procedimiento en un ambiente de pruebas con un entorno similar al productivo, al menos en cuestion de objetos y dependencias para verificar como funciona y no tener sorpresas en el entorno productivo.