Utilizando o pacote dbms_redefinition para particionamento de tabelas no Oracle 10g

O objetivo desse tutorial é realizar particionamento de tabelas on-line. Podemos usar o pacote para outras ações, como reorganização de tabelas.

Para esse procedimento vamos dividir as tarefas, conforme abaixo:

1. Verificar se a tabela é candidata ao particionamento
2. Criação das novas tablespaces
3. Criação da tabela interina
4. Start do Redefinition
5. Sync do Redefinition
6. Finish do Redefinition

1) Verificar se a tabela é candidata ao particionamento

Iremos utilizar a procedure CAN_REDEF_TABLE do pacote DBMS_REDEFINITION
Essa procedure determina se é possível fazer o redimensionamento online. Esse é o primeiro passo para o processo de particionamento (redefinition). Se a tabela não puder ser particionada retornará um erro

exec dbms_redefinition.can_redef_table('SCOTT', 'BILLING', DBMS_REDEFINITION.CONS_USE_PK);

2) Criação das novas tablespaces

Realizar esse passo de acordo com as suas novas partições

3) Criação da tabela interina

CREATE TABLE SCOTT.BILLING

PARTITION BY HASH (CODE1, CODE2, CODE3)

PARTITIONS 20

STORE IN (DATA_PAR_HASH1,DATA_PAR_HASH2,DATA_PAR_HASH3,DATA_PAR_HASH4,DATA_PAR_HASH5,DATA_PAR_HASH6,DATA_PAR_HASH7,DATA_PAR_HASH8,DATA_PAR_HASH9,DATA_PAR_HASH10,DATA_PAR_HASH11,DATA_PAR_HASH12,DATA_PAR_HASH13,DATA_PAR_HASH14,DATA_PAR_HASH15,DATA_PAR_HASH16,DATA_PAR_HASH17,DATA_PAR_HASH18,DATA_PAR_HASH19,DATA_PAR_HASH20)

AS SELECT * FROM SCOTT.BILLING where 1=2;

4) Start do Redefinition

Iremos utilizar a procedure START_REDEF_TABLE do pacote DBMS_REDEFINITION
Essa procedure inicia o processo de particionamento on-line.

begin

dbms_redefinition.START_REDEF_TABLE

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, COL_MAPPING => NULL

, OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK

, ORDERBY_COLS => NULL

, PART_NAME => NULL

);

end;

/

5) Cópia dos dependentes

Iremos utilizar a procedure COPY_TABLE_DEPENDENTS do pacote DBMS_REDEFINITION
Essa procedure copia as dependências da tabelas de origem para a tabela interina

DECLARE

num_errors PLS_INTEGER;

BEGIN

dbms_redefinition.COPY_TABLE_DEPENDENTS

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, COPY_INDEXES => DBMS_REDEFINITION.CONS_ORIG_PARAMS

, COPY_TRIGGERS => TRUE

, COPY_CONSTRAINTS => TRUE

, COPY_PRIVILEGES => TRUE

, IGNORE_ERRORS => TRUE

, NUM_ERRORS => num_errors

, COPY_STATISTICS => TRUE

);

END;

/

Verificar os erros:

SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS where base_table_name = 'BILLING'

select OBJECT_TYPE, OBJECT_NAME, BASE_TABLE_NAME, INTERIM_OBJECT_NAME from dba_redefinition_objects where BASE_TABLE_NAME = 'BILLING' order by 1,2,3;

6) Sync do Redefinition

Essa procedura mantém a tabela provisória sincronizado com a tabela original. Este passo é útil para minimizar a quantidade de sincronização antes de concluir a redefinição online.

BEGIN

dbms_redefinition.SYNC_INTERIM_TABLE

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, PART_NAME => NULL

);

END;

/

7) Finish do Redefinition

Esse procedure complete o processo de particionamento. Após concluir esta etapa, a tabela original é redefinido com os atributos e os dados da tabela provisória. A tabela original está bloqueado brevemente durante este procedimento

BEGIN

dbms_redefinition.FINISH_REDEF_TABLE

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, PART_NAME => NULL

);

END;

/

9) Abortar o processo (se necessário)

BEGIN

DBMS_REDEFINITION.ABORT_REDEF_TABLE

(

uname => 'BILLOWN'

, orig_table => 'BILLING'

, int_table => 'BILLING_PAR'

, part_name => NULL

);

END;

/