Olá galera, o objetivo aqui é compartilhar os procedimentos para realizar o move de tabelas no Oracle Database a partir da versão 9i até a versão 12cR2.
Aqui neste artigo estou utilizando a versão 12cR2 (12.2.0.1) para também poder demonstrar as funcionalidades desta versão. Então tirando a parte da arquitetura Multitenant, os procedimentos funcionam também para bancos de dados non CDB.
Vamos primeiro configurar nosso ambiente antes de realizar o move…
Configuração do ambiente
Estou conectado ao container Root:
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
Verifico os Pluggable Databases existentes:
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------- ---------- PDB$SEED READ ONLY PDB1 MOUNTED Elapsed: 00:00:00.02
Crio um novo PDB chamado SOEPDB:
SQL> create pluggable database soepdb admin user pdbadmin identified by oracle default tablespace soedata datafile size 100M autoextend on parallel 2 storage (maxsize 5g); Pluggable database created. Elapsed: 00:00:08.18
Altero minha sessão para o PDB recém criado:
SQL> alter session set container=soepdb; Session altered. Elapsed: 00:00:00.01
Abro o PDB em questão:
SQL> alter pluggable database soepdb open; Pluggable database altered. Elapsed: 00:00:09.74
Crio o usuário FRANKY já com a Role DBA:
SQL> grant dba to franky identified by oracle; Grant succeeded. Elapsed: 00:00:00.09
Crio duas tablespaces para realizar os testes:
SQL> create tablespace users; Tablespace created. Elapsed: 00:00:00.78 SQL> create tablespace users2; Tablespace created. Elapsed: 00:00:00.71
Conecto-me ao PDB:
SQL> conn franky/oracle@localhost:1521/soepdb.localdomain Connected. Session altered. Elapsed: 00:00:00.00
Move Online de tabelas usando a package DBMS_REDEFINITION
O objetivo aqui é realizar o move da tabela T_MOVE1 para a tablespace USERS2 sem gerar indisponibilidade para a aplicação utilizando a package DBMS_REDEFINITION. Este procedimento funciona a partir do Oracle Database 10gR1, apesar da package DBMS_REDEFINITION existir desde o Oracle Database 9i.
Crio a tabela em questão na tablespace USERS a partir da view ALL_OBJECTS:
SQL> create table t_move1 tablespace users as select * from all_objects; Table created. Elapsed: 00:00:05.55
Crio um índice para esta tabela também na tablespace USERS:
SQL> create index i_move1 on t_move1 (object_id) tablespace users; Index created. Elapsed: 00:00:00.16
Verifico os segmentos criados e também a tablespace onde estão armazenados:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MOVE1 INDEX USERS T_MOVE1 TABLE USERS Elapsed: 00:00:00.20
Verifico se a tabela T_MOVE1 do owner FRANKY pode ser redefinida:
SQL> exec dbms_redefinition.can_redef_table('FRANKY','T_MOVE1');
BEGIN dbms_redefinition.can_redef_table('FRANKY','T_MOVE1'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "FRANKY"."T_MOVE1" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 242
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5439
ORA-06512: at line 1
Elapsed: 00:00:00.04
Tomei um erro na verificação por esta tabela não ter uma primary key. Só podemos utilizar este método para tabelas que possuem PK ou utilizar a opção do dbms_redefinition com rowid. O Oracle precisa de um método para identificar unicamente cada linha e isto pode ser feito através de uma PK ou através do rowid.
Crio a constraint PK na tabela:
SQL> alter table t_move1 add constraint t_move1_pk primary key (object_id); Table altered. Elapsed: 00:00:00.14
Novamente verifico se a tabela pode ser redefinida:
SQL> exec dbms_redefinition.can_redef_table('FRANKY','T_MOVE1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
Crio a tabela T_MOVE2 na tablespace USERS2 baseada na T_MOVE1, mas sem seus dados:
SQL> create table t_move2 tablespace users2 as select * from t_move1 where 1=0; Table created. Elapsed: 00:00:00.22
Inicio o processo de redefinição indicando o owner, a tabela origem e a tabela destino (que deve estar vazia):
SQL> exec dbms_redefinition.start_redef_table('FRANKY','T_MOVE1','T_MOVE2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.05
Consulto os dados de ambas as tabelas para saber se há o mesmo número de registros:
SQL> select count(*) from t_move1; COUNT(*) ---------- 68108 Elapsed: 00:00:00.10 SQL> select count(*) from t_move2; COUNT(*) ---------- 68108 Elapsed: 00:00:00.05
Constatado que ambas estão iguais vou utilizar o bloco anônimo abaixo para copiar as dependências da tabela origem para a tabela destino:
SQL> SET SERVEROUTPUT ON SQL> DECLARE num_errors PLS_INTEGER; BEGIN dbms_redefinition.copy_table_dependents( 'FRANKY', 'T_MOVE1', 'T_MOVE2', copy_indexes => dbms_redefinition.cons_orig_params, num_errors => num_errors ); dbms_output.put_line(num_errors); END; / DECLARE * ERROR at line 1: ORA-01442: column to be modified to NOT NULL is already NOT NULL ORA-06512: at "SYS.DBMS_REDEFINITION", line 2074 ORA-06512: at "SYS.DBMS_REDEFINITION", line 2074 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1608 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1487 ORA-06512: at "SYS.DBMS_REDEFINITION", line 408 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1480 ORA-06512: at "SYS.DBMS_REDEFINITION", line 2056 ORA-06512: at "SYS.DBMS_REDEFINITION", line 3115 ORA-06512: at "SYS.DBMS_REDEFINITION", line 5578 ORA-06512: at line 4 Elapsed: 00:00:09.52
Um erro foi retornado informando que as colunas NOT NULL de origem já estão com NOT NULL no destino.
Podemos ignorar estes erros informando o parâmetro ignore_errors com o valor TRUE no bloco anônimo abaixo:
SQL> DECLARE num_errors PLS_INTEGER; BEGIN dbms_redefinition.copy_table_dependents( 'FRANKY', 'T_MOVE1', 'T_MOVE2', copy_indexes => dbms_redefinition.cons_orig_params, num_errors => num_errors, ignore_errors => true ); dbms_output.put_line(num_errors); END; / PL/SQL procedure successfully completed. Elapsed: 00:00:12.55
Outra opção é usar esse bloco anônimo (by oracle-base.com) informando no parâmetro copy_constraints o valor FALSE:
-- Copy table dependents
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'FRANKY',
orig_table => 'T_MOVE1',
int_table => 'T_MOVE2',
copy_indexes => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
copy_triggers => TRUE, -- Default
copy_constraints => FALSE, -- Non Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
É importante observar os demais objetos dependentes de uma tabela durante o procedimento, pois em algumas versões do Oracle Database objetos PL/SQL podem ficar inválidos, principalmente triggers, pois estão diretamente relacionados às tabelas.
Depois de copiar as dependências verifico os segmentos existentes. Percebam que temos a tabela T_MOVE1 e seu índice I_MOVE1 na tablespace USERS e a tabela T_MOVE2 na tablespace USERS2 e seu índice TMP$$_I_MOVE10 na tablespace USERS:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MOVE1 INDEX USERS TMP$$_I_MOVE10 INDEX USERS T_MOVE1 TABLE USERS T_MOVE2 TABLE USERS2
Vou inserir um registro novo na tabela T_MOVE1:
SQL> insert into t_move1 (OWNER,OBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,NAMESPACE) values ('FRANKY','ASDF',1,SYSDATE,SYSDATE,999);
1 row created.
Elapsed: 00:00:00.50
SQL> commit;
Commit complete.
Elapsed: 00:00:00.06
E agora comparo a tabela de origem com a de destino:
SQL> select count(*) from t_move1; COUNT(*) ---------- 68109 Elapsed: 00:00:00.01 SQL> select count(*) from t_move2; COUNT(*) ---------- 68108 Elapsed: 00:00:00.01
Observem que temos uma quantidade de registros diferente, então concluímos que nossa aplicação poderia continuar trabalhando na tabela origem durante este processo de move.
Para igualar as tabelas precisamos executar o sincronismo. Para isso usamos a procedure SYNC_INTERIM_TABLE passando novamente o owner, a tabela origem e a tabela destino:
SQL> exec dbms_redefinition.sync_interim_table('FRANKY','T_MOVE1','T_MOVE2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.30
Agora consulto a tabela T_MOVE2 para saber se ela foi sincronizada com a T_MOVE1:
SQL> select count(*) from t_move2; COUNT(*) ---------- 68109 Elapsed: 00:00:00.01
Novamente consulto os segmentos criados após este processo de sincronismo:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MLOG$_T_MOVE1 INDEX SOEDATA MLOG$_T_MOVE1 TABLE SOEDATA T_MOVE2 TABLE USERS2 I_MOVE1 INDEX USERS TMP$$_I_MOVE10 INDEX USERS T_MOVE1 TABLE USERS 6 rows selected. Elapsed: 00:00:00.05
Observe que foi criado mais dois segmentos: a tabela MLOG$_T_MOVE1 e o índice I_MLOG$_T_MOVE1. Na verdade aqui temos uma Materialized View Log e a tabela T_MOVE2 é como se fosse nossa Materialized View. Concluímos que esse processo é feito através de um método de Snapshot.
Vou inserir mais um registro para saber se o sincronismo acontece automaticamente:
SQL> insert into t_move1 (OWNER,OBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,NAMESPACE) values ('C##FRANKY','ASDF',9999998,SYSDATE,SYSDATE,999);
1 row created.
Elapsed: 00:00:00.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.03
Ao consultar as tabelas podemos perceber que o sincronismo não ocorreu:
SQL> select count(*) from t_move1; COUNT(*) ---------- 68110 Elapsed: 00:00:00.01 SQL> select count(*) from t_move2; COUNT(*) ---------- 68109 Elapsed: 00:00:00.01
É necessário fazê-lo manualmente:
SQL> exec dbms_redefinition.sync_interim_table('FRANKY','T_MOVE1','T_MOVE2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.29
Agora sim as tabelas estão sincronizadas:
SQL> select count(*) from t_move2; COUNT(*) ---------- 68110 Elapsed: 00:00:00.01
Consulto novamente os segmentos para constatar que mais nenhum foi criado:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MLOG$_T_MOVE1 INDEX SOEDATA I_MOVE1 INDEX USERS MLOG$_T_MOVE1 TABLE SOEDATA TMP$$_I_MOVE10 INDEX USERS T_MOVE1 TABLE USERS T_MOVE2 TABLE USERS2 6 rows selected. Elapsed: 00:00:00.09
Agora para finalizar o processo executo a procedure FINISH_REDEF_TABLE. Esta procedure fará o sincronismo final e removerá a Materialized View Log:
SQL> exec dbms_redefinition.finish_redef_table('FRANKY','T_MOVE1','T_MOVE2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.04
Consulto novamente os segmentos para constatar que a MLOG foi removida e que a tabela T_MOVE1 agora está na tablespace USERS2 e a T_MOVE2 está na tablespace users:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MOVE1 INDEX USERS TMP$$_I_MOVE10 INDEX USERS T_MOVE1 TABLE USERS2 T_MOVE2 TABLE USERS Elapsed: 00:00:00.11
O que houve na verdade foi um rename das tabelas envolvidas. O processo de redefinição faz isso de maneira transparente.
Agora removo a tabela T_MOVE2:
SQL> drop table T_MOVE2; Table dropped. Elapsed: 00:00:00.37
Consulto o índice da tabela T_MOVE1 para confirmar que o estado dele está válido após a redefinição:
SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1'; INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------- I_MOVE1 T_MOVE1 VALID Elapsed: 00:00:00.01
Novamente consulto os segmentos:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ BIN$UbJLSmoxG+ngUxMPqMBjFg==$0 INDEX USERS BIN$UbJLSmoyG+ngUxMPqMBjFg==$0 TABLE USERS I_MOVE1 INDEX USERS T_MOVE1 TABLE USERS2 Elapsed: 00:00:00.11
Como estou com a recyclebin habilitada os segmentos permanecem com o prefixo BIN$… então é necessário fazer a limpeza da recyclebin:
SQL> purge recyclebin; Recyclebin purged. Elapsed: 00:00:00.39
Ufa! O move da tabela T_MOVE1 para a tablespace USERS2 foi concluído com sucesso de maneira online.
Move de tabelas usando o MOVE (offline)
O objetivo aqui é realizar o move da tabela T_MOVE1 para a tablespace USERS utilizando a cláusula MOVE do comando ALTER TABLE. Este método gera indisponibilidade para aplicação e também invalida os índices da tabela. Este procedimento funciona desde o Oracle Database 9i.
Vou consultar os segmentos e verificar em que tablespace estão armazenados:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MOVE1 INDEX USERS T_MOVE1 TABLE USERS2 Elapsed: 00:00:00.05
Confirmo que o índice está válido:
SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1'; INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------- I_MOVE1 T_MOVE1 VALID Elapsed: 00:00:00.01
Movo a tabela T_MOVE1 para a tablespace USERS:
SQL> alter table t_move1 move tablespace users; Table altered. Elapsed: 00:00:00.24
Verifico que o índice passou a ter um estado inutilizável (unusable):
SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1'; INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------- I_MOVE1 T_MOVE1 UNUSABLE Elapsed: 00:00:00.00
Verifico os segmentos e constato que o objeto índice existe, mas não o seu segmento:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ T_MOVE1 TABLE USERS Elapsed: 00:00:00.01
Reconstruo o índice também na tablespace USERS:
SQL> alter index i_move1 rebuild tablespace users; Index altered. Elapsed: 00:00:00.26
Agora sim o índice passou a ficar válido:
SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1'; INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------- I_MOVE1 T_MOVE1 VALID Elapsed: 00:00:00.00
O segmento do índice foi criado após a sua reconstrução:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MOVE1 INDEX USERS T_MOVE1 TABLE USERS Elapsed: 00:00:00.01
Move de tabelas usando o MOVE ONLINE (online)
O objetivo aqui é mover a tabela T_MOVE1 para a tablespace USERS2 utilizando a cláusula MOVE ONLINE do comando ALTER TABLE. Este método não gera indisponibilidade para aplicação e não invalida os índices da tabela. Este procedimento funciona a partir do Oracle Database 12cR2.
Vou consultar os segmentos e verificar em que tablespace estão armazenados:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MOVE1 INDEX USERS T_MOVE1 TABLE USERS Elapsed: 00:00:00.01
Efetuo a movimentação da tabela T_MOVE1 para a tablespace USERS2 utilizando o MOVE ONLINE:
SQL> alter table t_move1 move online tablespace users2; Table altered. Elapsed: 00:00:00.94
Verifico o estado do índice da tabela e constato que o mesmo permanece válido:
SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1'; INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------- I_MOVE1 T_MOVE1 VALID Elapsed: 00:00:00.00
Aqui como bônus do processo reconstruo o índice de maneira online na tablespace USERS2:
SQL> alter index i_move1 rebuild online tablespace users2; Index altered. Elapsed: 00:00:00.20
Verifico novamente os segmentos:
SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ I_MOVE1 INDEX USERS2 T_MOVE1 TABLE USERS2
Tabela e índice movidos para a tablespace USERS2 sem gerar indisponibilidade para a aplicação utilizando o novo recurso MOVE ONLINE do Oracle Database 12cR2.
A movimentação de tabelas no Oracle tem ainda outros métodos não abordados neste artigo, mas você pode movê-las utilizando também o DataPump.
Espero que este artigo seja útil pra muita gente. Se você gostou inscreva-se no blog como forma de contribuição e ainda fique sabendo toda vez que sair um novo artigo.
Grande abraço e até mais,
Franky