Particionando uma tabela on-line com o pacote DBMS_REDEFINITION

Olá,

No artigo de Fevereiro/2011 eu apresentei uma breve introdução sobre tabelas particionadas e alguns exemplos práticos de algumas das operações básicas que podemos realizar sobre as partições mas mesmas. Neste artigo irei demonstrar como poderemos particionar uma tabela (on-line) fazendo uso do pacote DBMS_REDEFINITION disponível desde a versão do Oracle 9i. Para isso irei criar duas tabelas na qual a tabela filha (alvo da particionamento), possuirá além de índices, uma Foreign key constraint para a tabela pai. Vamos então ao exemplo prático.

C:\>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Dom Abr 3 14:08:20 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table pai (cod_grupo number constraint pk_pai primary key);

Tabela criada.

SQL> create table filho
  2   (id number constraint pk_filho primary key,
  3    cod_grupo number not null,
  4    data date not null
  5   );

Tabela criada.

SQL> create index idx_filho_codgrupo on filho (cod_grupo);

Índice criado.

SQL> alter table filho 
  2  add constraint fk_filho_pai
  3  foreign key (cod_grupo) references pai;

Tabela alterada.

SQL> select * from pai;

 COD_GRUPO
----------
        10
        20
        30

SQL> select * from filho;

        ID  COD_GRUPO DATA
---------- ---------- -------------------
         1         10 03/04/2011 14:10:19
         2         10 03/04/2011 14:10:20
         3         10 03/04/2011 14:10:21
         4         20 03/04/2011 14:10:22
         5         20 03/04/2011 14:10:23
         6         20 03/04/2011 14:10:24
         7         30 03/04/2011 14:10:25
         8         30 03/04/2011 14:10:26
         9         30 03/04/2011 14:10:27

9 linhas selecionadas.

Após a criação das tabelas, eu inseri alguns registros de teste como demonstrado acima.

SQL> select constraint_name,constraint_type 
  2    from user_constraints 
  3   where table_name='FILHO';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C004411                    C
SYS_C004412                    C
PK_FILHO                       P
FK_FILHO_PAI                   R

Acima estão listadas todas as constraints da tabela FILHO.

SQL> select index_name 
  2    from user_indexes 
  3   where table_name='FILHO';

INDEX_NAME
------------------------------
PK_FILHO
IDX_FILHO_CODGRUPO

Acima estão listadas todas os índices da tabela filho. Bom, agora irei iniciar o processo de particionamento on-line da tabela FILHO. Para isso irei conectar com o usuário SYSTEM que possui privilégios DBA.

SQL> connect system/******
Conectado.

SQL> BEGIN
  2  DBMS_REDEFINITION.CAN_REDEF_TABLE
  3   (uname=>'SCOTT',
  4    tname=>'FILHO',
  5    options_flag=>DBMS_REDEFINITION.CONS_USE_PK);
  6  END;
  7  /

Procedimento PL/SQL concluído com sucesso.

O primeiro passo executado acima foi realizado para verificar se a tabela é passível de redefinição on-line. Se a tabela não fosse uma candidata para tal, a procedure iria retornar um erro indicando o motivo de a mesma não poder ser redefinida de forma on-line. Vale a pena salientar que eu utilizei a opção CONS_USE_PK pois a mesma possui um Primary Key definida. Se não fosse o caso, poderíamos utilizar a opção CONS_USE_ROWID.

O próximo passo será criar uma tabela temporária (interim) com a mesma estrutura da tabela alvo do particionamento, só que agora particionada. Neste caso irei criar uma tabela particionada por HASH.

SQL> create table SCOTT.interim
  2   (
  3    id number,
  4    cod_grupo number,
  5    data date
  6   )
  7    partition by hash (cod_grupo)
  8     (
  9      partition ph01_filho tablespace users,
 10      partition ph02_filho tablespace users,
 11      partition ph03_filho tablespace users
 12     )
 13  enable row movement;

Tabela criada.

Percebam a falta de constraints NOT NULL na definição da tabela INTERIM. Isso é importante para evitar qualquer problema na execução dos passos seguintes.

SQL> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE
  3   (uname=>'SCOTT',
  4    orig_table=>'FILHO',
  5    int_table=>'INTERIM',
  6    options_flag=>DBMS_REDEFINITION.CONS_USE_PK);
  7  END;
  8  /

Procedimento PL/SQL concluído com sucesso.

O procedimento acima inicia o processo de redefinição on-line da tabela FILHO. Irei verificar abaixo o que este procedimento realmente fez.

SQL> col query format a30
SQL> select mview_name,container_name,query 
  2    from dba_mviews 
  3   where owner='SCOTT';

MVIEW_NAME        CONTAINER_NAME     QUERY
----------------- ------------------ --------------------------------
INTERIM           INTERIM            SELECT "FILHO"."ID" "ID",
                                     "FILHO"."COD_GRUPO" "COD_GRUPO",
                                     "FILHO"."DATA" "DATA"
                                     FROM "SCOTT"."FILHO" "FILHO"

SQL> select master,log_table from dba_mview_logs where log_owner='SCOTT';

MASTER                         LOG_TABLE
------------------------------ ------------------------------
FILHO                          MLOG$_FILHO

Podemos perceber que não somente uma view materializada, mas também um log de view materializada foi automaticamente criada para dar suporte às operações DML que porventura sejam realizadas na tabela original durante a fase da redefinição on-line. Para dar continuidade ao próximo de redefinição on-line, abaixo executarei o procedimento para copiar os objetos dependentes para a tabela INTERIM como constraints e índices.

SQL> var num_errors number
SQL> BEGIN
  2  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
  3   (uname=>'SCOTT',
  4    orig_table=>'FILHO',
  5    int_table=>'INTERIM',
  6    num_errors=>:num_errors);
  7  END;
  8  /

Procedimento PL/SQL concluído com sucesso.

SQL> print num_errors

NUM_ERRORS
----------
         0

Após a execução da etapa acima podemos verificar os objetos atualmente criados no schema SCOTT.

SQL> select object_name,subobject_name,object_type
  2    from dba_objects
  3   where owner='SCOTT';

OBJECT_NAME                    SUBOBJECT_NAME                 OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PK_PAI                                                        INDEX
PAI                                                           TABLE
FILHO                                                         TABLE
PK_FILHO                                                      INDEX
IDX_FILHO_CODGRUPO                                            INDEX
INTERIM                        PH03_FILHO                     TABLE PARTITION
INTERIM                        PH02_FILHO                     TABLE PARTITION
INTERIM                        PH01_FILHO                     TABLE PARTITION
INTERIM                                                       TABLE
MLOG$_FILHO                                                   TABLE
RUPD$_FILHO                                                   TABLE
TMP$$_PK_FILHO0                                               INDEX
TMP$$_IDX_FILHO_CODGRUPO0                                     INDEX

13 linhas selecionadas.

Por fim, poderemos finalizar o processo de redefinição on-line.

SQL> BEGIN
  2  DBMS_REDEFINITION.FINISH_REDEF_TABLE
  3   (uname=>'SCOTT',
  4    orig_table=>'FILHO',
  5    int_table=>'INTERIM');
  6  END;
  7  /

Procedimento PL/SQL concluído com sucesso.


SQL> connect scott/tiger
Conectado.

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
PAI                            TABLE
INTERIM                        TABLE
FILHO                          TABLE

Neste momento não precisaremos mais da tabela temporária INTERIM. Portanto poderemos dropá-la de forma segura.

SQL> drop table interim;

Tabela eliminada.

No mais, poderemos ver abaixo que a tabela FILHO foi particionada de forma on-line apenas utilizando o pacote DBMS_REDEFINITION.

SQL> select table_name,partitioning_type,partition_count
  2    from user_part_tables;

TABLE_NAME                     PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
FILHO                          HASH                  3


SQL> select constraint_name,constraint_type,status
  2    from user_constraints
  3   where table_name='FILHO';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C004411                    C ENABLED
SYS_C004412                    C ENABLED
PK_FILHO                       P ENABLED
FK_FILHO_PAI                   R ENABLED


SQL> select index_name,status
  2   from user_indexes
  3  where table_name='FILHO';

INDEX_NAME                     STATUS
------------------------------ --------
PK_FILHO                       VALID
IDX_FILHO_CODGRUPO             VALID