Precisamos reorganizar uma tabela que é acessada durante 24 horas por dia e sete dias na semana, nessa situação uma alternativa seria utilizar a package Online Redefinition (DBMS_REDEFINITION).
Ambiente:
SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado
Overview:
Oracle Online Redefinition permite alterações na estrutura das tabelas enquanto os usuários estão lendo e gravando nessas tabelas. Assegurando assim a continuidade do serviço sem impacto nas atividades dos usuários finais, não há restrição quanto tempo necessário para reorganização da tabela. Quando a reorganização estiver concluída, a mudança para a nova tabela reorganizada é automática e completamente transparente para os usuários da aplicação.
Tópicos:
- Setup;
- Verificar se a tabela pode ser reorganizada online;
- Verificar tamanho da tabela;
- Criar a tabela provisória;
- Iniciar a reorganização;
- Simular uma modificação do usuário;
- Copiar objetos dependentes da tabela;
- Simular commit da transação do usuário;
- Sincronizar tabela provisória com a original;
- Disponibilizar a tabela provisória como original;
- Verificar se a tabela foi reorganizada;
1. Setup
SQL> create tablespace appl01 datafile '/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/appl01.dbf' size 100 M;
Tablespace criado.
SQL> create user appl01 identified by oracle default tablespace appl01 temporary tablespace temp;
Usuário criado.
SQL> grant connect to appl01;
Concessão bem-sucedida.
SQL> grant unlimited tablespace to appl01;
Concessão bem-sucedida.
SQL> grant select any dictionary to appl01;
Concessão bem-sucedida.
SQL> grant resource to appl01;
Concessão bem-sucedida.
SQL> conn appl01/oracle@lab1;
Conectado.
SQL>
SQL> drop table TEST_REORG CASCADE CONSTRAINTS PURGE;
Tabela eliminada.
SQL> create table test_reorg
2 ( regid number not null
3 ,nome varchar2(30) not null
4 ,endereco varchar2(30) not null
5 ,data date not null
6 ,comentario varchar2(100)
7 ,constraint test_reorg_pk primary key (regid)
8 );
Tabela criada.
SQL>
SQL>
SQL> create index test_reorg_idx on test_reorg(nome);
Índice criado.
SQL> select object_name, object_type from dba_objects where owner = 'APPL01';
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
DB_DICT TABLE
TEST_REORG TABLE
TEST_REORG_PK INDEX
TEST_REORG_IDX INDEX
DB_OBJETOS TABLE
SQL> declare
2 id number;
3 x varchar2(30);
4 y varchar2(30);
5 c varchar2(100);
6 begin
7 x := '123456789+123456789+1234567890';
8 y := 'Abcde fghi jklmn opqrs tuvxz99';
9 id := 1;
10 c := 'black Abcde fghi jklmn opqrs tuvxz99 123456789+123456789+1234567890 black Abcde fghi jklmn opqrs';
11
12 for i in 1..100000 loop
13
14 insert into test_reorg values (id, x, y, sysdate, c);
15 id := id + 1;
16
17 end loop;
18 end;
19 /
Procedimento PL/SQL concluído com sucesso.
SQL>
SQL> commit;
Validação completa.
SQL>
SQL>
SQL> delete from test_reorg where regid > 5000 and regid <> commit;
Validação completa.
SQL> delete from test_reorg where regid > 25000 and regid <> commit;
Validação completa.
SQL> delete from test_reorg where regid > 45000 and regid <> commit;
Validação completa.
SQL> delete from test_reorg where regid > 75000 and regid <> commit;
Validação completa.
SQL>
SQL>
2. Verificar se a tabela pode ser reorganizada online
Para reorganizar uma tabela, precisamos verificar se a mesma é candidata a uma redefinição online:
SQL> conn system/oracle@lab1;
Conectado.
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('APPL01',' TEST_REORG',dbms_redefinition.cons_use_pk);
3 END;
4 /
Procedimento PL/SQL concluído com sucesso.
SQL>
SQL>
3. Verificar tamanho da tabela
Vamos verificar o tamanho da tabela antes da reorganização para comparar com o tamanho após a reorganização:
SQL> select segment_name, owner, bytes/1024 "Tam. KB" from dba_segments where segment_name = 'TEST_REORG';
SEGMENT_NAME OWNER Tam. KB
-------------------- ------------ ----------
TEST_REORG APPL01 20480
SQL>
4. Criar a tabela provisória
SQL> conn appl01/oracle@lab1;
Conectado.
SQL> create table test_reorg_prov
2 ( regid number
3 ,nome varchar2(30)
4 ,endereco varchar2(30)
5 ,data date
6 ,comentario varchar2(100)
7 );
Tabela criada.
SQL>
5. Iniciar a reorganização
Agora vamos iniciar o processo de reorganização e definir os mapeamentos de coluna para as colunas antigas e novas:
SQL> conn system/oracle@lab1;
Conectado.
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('APPL01', 'TEST_REORG','TEST_REORG_PROV',
3 'regid regid, nome nome, endereco endereco, data data, comentario comentario' ,
4 dbms_redefinition.cons_use_pk);
5 END;
6 /
Procedimento PL/SQL concluído com sucesso.
SQL>
6. Simular uma modificação do usuário
Enquanto a reorganização está executando vamos iniciar outra sessão onde faremos alterações na tabela TEST_REORG:
SQL> conn appl01/oracle@lab1;
Conectado.
SQL> UPDATE TEST_REORG set endereco = 'valteraquino.blogspot.com' where regid=20000;
1 linha atualizada.
SQL>
Obs: Não execute o commit nesse momento.
7. Copiar objetos dependentes da tabela
Em seguida retornando para a primeira sessão, vamos criar automaticamente todos os triggers, índices e constraints na tabela provisória:
SQL> set serveroutput on
SQL> DECLARE
2 num_erros int;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
5 ('APPL01', 'TEST_REORG','TEST_REORG_PROV',1, TRUE, TRUE, TRUE, FALSE, num_erros);
6 DBMS_OUTPUT.put_line ('Numero de erros durante a copia do obj.dependentes: ' num_erros );
7 END;
8 /
Numero de erros durante a copia do obj.dependentes: 0
Procedimento PL/SQL concluído com sucesso.
SQL>
8. Simular commit da transação do usuário
Agora vamos retornar para a sessão onde executamos o UPDATE na tabela e vamos confirmar a mudança de endereço e fechar a sessão:
SQL> commit;
Validação completa.
SQL> exit
9. Sincronizar tabela provisória com a original
Agora podemos sincronizar a tabela provisória para que ela inclua a alteração no endereço que fizemos:
SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('APPL01', 'TEST_REORG', 'TEST_REORG_PROV');
3 END;
4 /
Procedimento PL/SQL concluído com sucesso.
SQL>
10. Disponibilizar a tabela provisória como original
Agora vamos encerrar a reorganização e as tabelas serão invertidas automaticamente:
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('APPL01', 'TEST_REORG', 'TEST_REORG_PROV');
3 END;
4 /
Procedimento PL/SQL concluído com sucesso.
SQL> DROP TABLE APPL01.TEST_REORG_PROV CASCADE CONSTRAINTS PURGE;
Tabela eliminada.
SQL>
11. Verificar se a tabela foi reorganizada
Vamos verificar o tamanho atual da tabela 'TEST_REORG' e comparar com o tamanho antes do processo de reorganização:
SQL> select segment_name, owner, bytes/1024 "Tam. KB" from dba_segments where segment_name = 'TEST_REORG';
SEGMENT_NAME OWNER Tam. KB
-------------------- ------------ ----------
TEST_REORG APPL01 14336
SQL>
SQL> select endereco from APPL01.TEST_REORG where regid in (19999,20000,20001);
ENDERECO
------------------------------
Abcde fghi jklmn opqrs tuvxz99
valteraquino.blogspot.com
Abcde fghi jklmn opqrs tuvxz99
SQL>
SQL> select object_name, object_type from dba_objects where owner = 'APPL01';
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
DB_DICT TABLE
DB_OBJETOS TABLE
TEST_REORG_PK INDEX
TEST_REORG_IDX INDEX
TEST_REORG TABLE
SQL>
Muito Bom o seu Blog.
ResponderExcluirAbs.
Hudson Santos