Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Área de Tecnologías de la Información y las Comunicaciones Aplicadas Universidad de Murcia
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
ATICA arrow DBCONNECTOR arrow DBA - Convertir Tabla Normal en Particionada
21.09.2017
 
 
DBA - Convertir Tabla Normal en Particionada Imprimir E-mail

Si queremos "convertir" una tabla normal T1 en particionada, podemos hacerlo creando una tabla T2 igual que la original, pero particioinada, y después usaremos el paquete DBMS_REDEFINITION para que T2 pase a ser T1 con todos los datos, índices y constraints que tenía T1, y finalmente borraremos T2.

En primer lugar, necesitamos los permisos que nos dice el Manual de Oracle.


EJEMPLO SIMPLE

- Creamos la tabla T2 particionada (con las mismas columnas que tenga T1)

create table t2
  (username varchar2(30),
   user_id number,
   created date)
  partition by hash(user_id) partitions 8;

Table created.

- Iniciamos el proceso de "redifinición" de T1 comprobando si es posible hacerlo:

exec dbms_redefinition.can_redef_table( user, 'T1' );

PL/SQL procedure successfully completed.

- Si la comprobación anterior no falla, le decimos a Oracle que vamos a redifinir T1 en base a T2

exec dbms_redefinition.start_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

- Le decimos que copie las dependencias que tenía T1

variable nerrors number
begin
  dbms_redefinition.copy_table_dependents ( user, 'T1', 'T2',
    copy_indexes => dbms_redefinition.cons_orig_params,
    num_errors => :nerrors );
end;
/

PL/SQL procedure successfully completed. 

- Finalmente le decimos que hemos terminado:

exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.


EJEMPLO REAL

Vamos a convertir la tabla LOGATICA_LOGS (no particionada) en particionada, tal y como indica la tabla LOGATICA_LOGS2 (particionada por rango con intervalo de 3 meses):

- Script para redefinir la tabla LOGATICA_LOGS:

SQL> !cat redefinir_logatica.sql
set serveroutput on
exec dbms_redefinition.can_redef_table( 'LOGATICA', 'LOGATICA_LOGS', DBMS_REDEFINITION.CONS_USE_PK );
exec dbms_redefinition.start_redef_table( 'LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2' );

variable nerrors number
begin
  dbms_redefinition.copy_table_dependents ( 'LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2',
    copy_indexes => dbms_redefinition.cons_orig_params,
    ignore_errors => TRUE,
    num_errors => :nerrors );
end;
/

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2')
exec dbms_redefinition.finish_redef_table( 'LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2' );

- Compruebo si el proceso de redefinición es posible

SQL> set serveroutput on
SQL> exec dbms_redefinition.can_redef_table( 'LOGATICA', 'LOGATICA_LOGS', DBMS_REDEFINITION.CONS_USE_PK );
PL/SQL procedure successfully completed.

- Creo la tabla destino (como quedará una vez finalizado el proceso)

SQL> @crea_tab_logatica2.sql
Table created.

- Inicio el proceso de redefinición

SQL> exec dbms_redefinition.start_redef_table( 'LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2' );
PL/SQL procedure successfully completed.

- Copio las dependencias, indicando que ignore los errores (pues la tabla destino, LOGATICA_LOGS2, ya tiene la 3 constraints NOT NULL que existen en LOGATICA_LOGS)

SQL> variable nerrors number
begin
  dbms_redefinition.copy_table_dependents ( 'LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2',
    copy_indexes => dbms_redefinition.cons_orig_params,
    ignore_errors => TRUE,
    num_errors => :nerrors );
end;
/
PL/SQL procedure successfully completed.

- Compruebo los errores para asegurarme que solo estan los 3 NOT NULL

SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

OBJECT_NAME               BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
SYS_C00477007               LOGATICA_LOGS
ALTER TABLE "LOGATICA"."LOGATICA_LOGS2" MODIFY ("LOGS_APLICACION" CONSTRAINT "TM

SYS_C00477006               LOGATICA_LOGS
ALTER TABLE "LOGATICA"."LOGATICA_LOGS2" MODIFY ("LOGS_SECUENCIA" CONSTRAINT "TMP

SYS_C00491401               LOGATICA_LOGS
ALTER TABLE "LOGATICA"."LOGATICA_LOGS2" MODIFY ("LOGS_FECHA" CONSTRAINT "TMP$$_S

- "Refresco" la redefinición

SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2')
PL/SQL procedure successfully completed.

- Termino el proceso

SQL> exec dbms_redefinition.finish_redef_table( 'LOGATICA', 'LOGATICA_LOGS', 'LOGATICA_LOGS2' );
PL/SQL procedure successfully completed.


ENLACES


FAQ

  • Si queremos cancelar el proceso de "redefinición" lo haremos con
    exec dbms_redefinition.ABORT_REDEF_TABLE('NombreUsuario','T1', 'T2');
  • Si el proceso se alarga, podemos "refrescarlo" con
    exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('NombreUsuario', 'T1', 'T2');
  • Podemos obtener una "copia de seguridad" de la sentencia DDL de creación de la tabla con
    SELECT DBMS_METADATA.GET_DDL('TABLE','NombreUsuario', 'T1') FROM DUAL;
  • Y de los índices con
    SELECT DBMS_METADATA.GET_DDL('INDEX','NombreUsuario', 'NombreIndice') FROM DUAL;
  • No se puede "redefinir" la tabla si las columnas no son exactamente iguales, por lo que da un error ORA-42016 si T1 tiene una columna "timestamp with time zone" y en T2 es "timestamp" SIN "time zone".
  • No se puede particionar por una columna "timestamp with time zone", da un error ORA-3001 de "funcionalidad no implementada". Sí podremos usar una columna "timestamp" normal
  • Si la tabla está particionada por rango (o intervalos), y nos interesa particionar los índices únicos de forma local, sería necesario que la clave de particionamiento formase parte de ellos. En el siguiente ejemplo la clave de particionamiento es LOGS_FECHA:

create unique index uk_LOGS_FIND on LOGATICA.LOGATICA_LOGS
(LOGS_APLICACION, LOGS_FECHA, LOGS_NIVEL, LOGS_LOUR_CODIGO)
local tablespace indlogatica storage (initial 2M next 2M maxextents 1 pctincrease 0);


Última modificación ( 27.04.2012 )
 
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Volver al incio del documento Volver al inicio del documento
Área de Tecnologías de la Información y las Comunicaciones Aplicadas