Cómo particionar una tabla existente usando DBMS_Redefinition

Presentaré un ejemplo de cómo particionar una tabla ya existente usando el paquete dbms_redefinition. Dicho paquete nos permite realizar una reorganización de tablas online.

Existen ciertas restricciones por las cuales las tablas de la siguientes características no podrán ser redefinidas online (metalink ID 149564.1):

• [9.0.1]Tables with no primary keys
• Tables that have materialized view logs defined on them
• [9i] Tables that are materialized view container tables and AQ tables
• [10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed
• Tables with fine-grained access control (row-level security)
• Tables with BFILE columns
• Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
• Tables in the SYS and SYSTEM schema
• Temporary tables

Los pasos para particionar una tabla ya existente son los siguientes:

1. Lo primero que vamos a realizar es pasar las estadísticas a la tabla origen, que es la que queremos particionar.

   SQL> exec dbms_stats.gather_table_stats(USER, ‘tabla_origen’, cascade => TRUE);

2.  Crear tabla intermedia para particionar tabla_origen:

   CREATE TABLE schema.tabla_temp_part
   (id NUMBER(10),
   codigo number,
   fecha_alta DATE,
   name VARCHAR2(100)
   ) PARTITION BY RANGE (FECHAALTA)
   SUBPARTITION BY LIST (CODIGO)
   SUBPARTITION TEMPLATE(
   SUBPARTITION codigo_1 VALUES(0),
   SUBPARTITION codigo_2 VALUES(1))
   (
   PARTITION anyo_2008 VALUES LESS THAN (’01/01/2008′),
   PARTITION anyo_2009 VALUES LESS THAN (’01/01/2009′),
   PARTITION anyo_2010 VALUES LESS THAN (’01/01/2010′),
   PARTITION anyo_2011 VALUES LESS THAN (’01/01/2011′)
   );

3. Redefinición de procesos:
   a) Chequear que sea posible realizar la redefinición de procesos:

   SQL> EXEC Dbms_Redefinition.can_redef_table(USER, ‘tabla_origen’);

   b) Si no hay errores comenzamos la redefinición:

   SQL> BEGIN
   DBMS_REDEFINITION.start_redef_table(
   uname => USER,
   orig_table => ‘tabla_origen’,
   int_table => ‘tabla_temp_part’);
   END;

   c) Opcionalmente sincronizar la nueva tabla intermedia antes de crear los índices:

   SQL> BEGIN
   dbms_redefinition.sync_interim_table(
   uname => USER,
   orig_table => ‘tabla_origen’,
   int_table => ‘tabla_temp_part’);
   END;
   /

   d) Crear Constraints e Indexes (las mismas constraints e índices que tenemos en tabla_origen los crearemos en tabla_temp_part, pero con otro nombre, por que si no la creación del objeto fallará, porque ya existe con ese nombre):

   (Tenemos la constraint tabla_origen_pk en tabla_origen)
   SQL> ALTER TABLE tabla_temp_part ADD (CONSTRAINT tabla_origen_pk2 PRIMARY KEY (id));
   (Tenemos el índice idx_tabla_origen en tabla_origen)
   SQL> CREATE INDEX idx_tabla_origen2 ON par_table(codigo,fecha_alta);

   e) Pasar estadísticas a la nueva tabla:

   SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘tabla_temp_part’, cascade => TRUE);

   f) Completar el proceso de redefinición:

   SQL> BEGIN
   dbms_redefinition.finish_redef_table(
   uname => USER,
   orig_table => ‘unpar_table’,
   int_table => ‘par_table’);
   END;
   /

   En este punto la tabla temporal (tabla_temp_part) se ha convertido en la tabla origen (tabla_origen) y sus nombres han sido cambiados en el diccionario.

   g) Remover la tabla inicial la cual ahora está cambiada de nombre a tabla_temp_part.

   SQL> DROP TABLE tabla_temp_part;

   h) Renombrar todas las constraints e índices a sus nombre originales.

   ALTER TABLE tabla_origen RENAME CONSTRAINT tabla_origen_pk2 TO tabla_origen_pk;
   ALTER INDEX idx_tabla_origen2 RENAME TO idx_tabla_origen;

   i) Check whether partitioning is successful or not:

   SQL> SELECT partitioned
   FROM user_tables
   WHERE table_name = ‘tabla_origen’;
   SQL> SELECT partition_name
   FROM user_tab_partitions
   WHERE table_name = ‘tabla_origen’;