Neste artigo irei demonstrar como podemos redefinir a estrutura de uma tabela já existente e converte-lá em uma tabela particionada utilizando o modelo range(intervalo) através da package DBMS_REDEFINITION existente desde a versão 9i e que permite uma redefinição ONLINE de tabelas.
Para este exemplo, vamos utilizar uma tabela existente que tenho aqui e que não é particionada, por nome de PAGTO_ALUNOS_SEM_PARTICAO:
SQL> col OWNER format a20 col TABLE_NAME format a30 select OWNER, TABLE_NAME from dba_tables where table_name ='PAGTO_ALUNOS_SEM_PARTICAO'; OWNER TABLE_NAME -------------------- ------------------------------ TESTE PAGTO_ALUNOS_SEM_PARTICAO SQL>
Vamos incluir um campo chave nesta tabela, pois, sem ele não conseguimos fazer a redefinição online:
SQL> alter table TESTE.PAGTO_ALUNOS_SEM_PARTICAO add constraint pk_PAGTO_ALUNOS_SEM_PARTICAO primary key (id_pagto); Table altered. SQL>
Confira que nossa tabela não é particionada:
SQL> select table_name, partition_name, num_rows from dba_tab_partitions where table_name='PAGTO_ALUNOS_SEM_PARTICAO'; no rows selected SQL>
Certo, nosso primeiro passo é verificar se a tabela pode ser redefinida de forma online.
Caso não seja uma candidata, a procedure irá retornar um erro indicando o motivo.
Em nosso caso abaixo, foi tudo tranquilo:
SQL> BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(uname => 'TESTE', tname => 'PAGTO_ALUNOS_SEM_PARTICAO'); END; / PL/SQL procedure successfully completed. SQL>
Neste exemplo não estou especificando a opção OPTIONS_FLAG, desta forma a procedure CAN_REDEF_TABLE utiliza a opção default DBMS_REDEFINITION.CONS_USE_PK que realiza a redefinição usando a chave-primária da tabela (condição existente na nossa tabela).
Caso a tabela não possua PK, podemos especificar OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_ROWID.
Mas apenas por curiosidade, se a nossa tabela não tivesse uma chave primária e você utiliza-se a opção default, nós receberiamos o erro abaixo:
SQL> BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(uname => 'TESTE', tname => 'PAGTO_ALUNOS_SEM_PARTICAO'); END; / BEGIN * ERROR at line 1: ORA-12089: cannot online redefine table "TESTE"."PAGTO_ALUNOS_SEM_PARTICAO" with no primary key ORA-06512: at "SYS.DBMS_REDEFINITION", line 173 ORA-06512: at "SYS.DBMS_REDEFINITION", line 3664 ORA-06512: at line 2
Agora iremos criar nossa tabela temporária, conhecida por INTERIM, utilizando a mesma estrutura da tabela anterior (PAGTO_ALUNOS_SEM_PARTICAO) porém agora particionada e sem constraints/índices.
SQL> create table teste.pagto_alunos_interim
2 ( id_pagto number not null,
3 id_aluno number not null,
4 valor_mensalidade number(10,2),
5 data_boleto date,
6 data_vencimento date
7 ) partition by range (data_boleto)
8 ( partition p_pagto_2015 values less than (to_date('31/12/2015','dd/mm/rrrr')) tablespace pagto_2015,
9 partition p_pagto_2016 values less than (to_date('31/12/2016','dd/mm/rrrr')) tablespace pagto_2016,
10 partition p_pagto_2017 values less than (to_date('31/12/2017','dd/mm/rrrr')) tablespace pagto_2017
11 );
Table created.
SQL>
Conceda quota para o usuário poder criar objetos no tablespaces abaixo caso não tenha:
SQL> alter user teste quota unlimited on pagto_2015; User altered. SQL> SQL> alter user teste quota unlimited on pagto_2016; User altered. SQL> alter user teste quota unlimited on pagto_2017; User altered. SQL>
O próximo passo é iniciar o processo de redefinição online da tabela, vejamos:
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'TESTE',
orig_table => 'PAGTO_ALUNOS_SEM_PARTICAO',
int_table => 'PAGTO_ALUNOS_INTERIM');
END;
/
PL/SQL procedure successfully completed.
SQL>
Você pode realizar uma contagem nas duas tabelas para comparar a quantidade de registros se quiser.
Ainda apenas por curiosidade, caso você tenha o erro abaixo quando iniciar o processo de redefinição, voce pode abortar o processo que foi iniciado com o: DBMS_REDEFINITION.abort_redef_table, como demonstrado abaixo.
Após ter abortado é só iniciar o processo de redefinição novamente (DBMS_REDEFINITION.start_redef_table).
ERROR at line 1: ORA-23539: table "TESTE"."PAGTO_ALUNOS_SEM_PARTICAO" currently being redefined ORA-06512: at "SYS.DBMS_REDEFINITION", line 75 ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459 ORA-06512: at line 2 SQL> SQL> BEGIN DBMS_REDEFINITION.abort_redef_table( uname => 'TESTE', orig_table => 'PAGTO_ALUNOS_SEM_PARTICAO', int_table => 'PAGTO_ALUNOS_INTERIM'); END; / PL/SQL procedure successfully completed.
Dando sequência iremos realizar a cópia dos objetos dependentes para a tabela INTERIM, como grants, triggers, constraints, índices e privilégios se tiverem:
SQL> var num_errors number SQL> BEGIN 2 3 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'TESTE', 4 orig_table=>'PAGTO_ALUNOS_SEM_PARTICAO', 5 int_table=>'PAGTO_ALUNOS_INTERIM', 6 copy_indexes=>dbms_redefinition.cons_orig_params , 7 copy_triggers=>TRUE, 8 copy_constraints=>TRUE, 9 copy_privileges=>TRUE, 10 ignore_errors=>TRUE, 11 num_errors=>:num_errors, 12 copy_statistics=>false); 13 14 END; 15 / PL/SQL procedure successfully completed.
Visualize os erros com a consulta abaixo e se achar necessário continue o procedimento:
SQL> col object_owner for a10
col base_table_name for a20
col ddl_txt for a30
select object_type, object_owner, base_table_name, ddl_txt
from dba_redefinition_errors;
OBJECT_TYPE OBJECT_OWN BASE_TABLE_NAME DDL_TXT
------------ ---------- -------------------- ------------------------------
CONSTRAINT TESTE PAGTO_ALUNOS_SEM_PAR ALTER TABLE "TESTE"."PAGTO_ALU
TICAO NOS_INTERIM" MODIFY ("ID_PAGTO
" CONSTRAINT "TMP$$_
CONSTRAINT TESTE PAGTO_ALUNOS_SEM_PAR ALTER TABLE "TESTE"."PAGTO_ALU
TICAO NOS_INTERIM" MODIFY ("ID_ALUNO
" CONSTRAINT "TMP$$_
SQL>
Certo, vamos disparar um sincronismo para manter a tabela INTERIM sincronizada com a tabela original(PAGTO_ALUNOS_SEM_PARTICAO).
No nosso cenário não temos nenhum benefício, pois ninguém esta alterando ou inserindo registros na tabela PAGTO_ALUNOS_SEM_PARTICAO, contudo em um ambiente OLTP cuja tabela esta sofrendo constantes operações DML o sincronismo é útil para minimizar a quantidade de sincronização necessária a ser feita pelo procedimento FINISH_REDEF_TABLE, acelerando as operações subsequentes:
SQL> BEGIN
DBMS_REDEFINITION.sync_interim_table(
uname => 'TESTE',
orig_table => 'PAGTO_ALUNOS_SEM_PARTICAO',
int_table => 'PAGTO_ALUNOS_INTERIM');
END;
/
PL/SQL procedure successfully completed.
Por fim, disparamos uma coleta de estatística sobre a PAGTO_ALUNOS_INTERIM e finalizamos o processo de redefinição.
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TESTE',
3 TABNAME => 'PAGTO_ALUNOS_INTERIM',
4 estimate_percent => 100,
5 method_opt => 'FOR ALL COLUMNS SIZE 1',
6 degree => 16,
7 granularity => 'ALL',
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
DBMS_REDEFINITION.finish_redef_table(
uname => 'TESTE',
orig_table => 'PAGTO_ALUNOS_SEM_PARTICAO',
int_table => 'PAGTO_ALUNOS_INTERIM');
END;
/
PL/SQL procedure successfully completed.
Agora já podemos remover a tabela INTERIM(PAGTO_ALUNOS_INTERIM):
SQL> drop table teste.pagto_alunos_interim; Table dropped. SQL> col table_name format a30 col partition_name format a30 col num_rows format 999999999.99 select table_name, partition_name, num_rows from dba_tab_partitions where table_name='PAGTO_ALUNOS_SEM_PARTICAO'; TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ------------- PAGTO_ALUNOS_SEM_PARTICAO P_PAGTO_2015 498679.00 PAGTO_ALUNOS_SEM_PARTICAO P_PAGTO_2016 501420.00 PAGTO_ALUNOS_SEM_PARTICAO P_PAGTO_2017 499901.00 SQL>
Agora nossa tabela já populada sem partição está particionada rs.
Abraço,
Ronaldo.
Nenhum comentário:
Postar um comentário