Move de tabelas (online/offline) no Oracle (9i-12cR2)

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.

move

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.

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.Oracle Database 12c Release 2

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

  • Obrigado pela contribuição, Eli. Talvez eu devesse ter citado que o default é usar a PK como base para a redefinição, o que é o cenário mais comum, mas há a possibilidade de usar como base o ROWID da tabela a ser redefinida. Vou atualizar o artigo. Mais uma vez obrigado.

  • Eli Dias

    No dbms_redefinition foi citado que é necessário criar uma constraint para realizar a definição, porém, existe um parametro para redefinir tabelas que não possuem chaves.
    Ex.:
    exec DBMS_REDEFINITION.can_REDEF_TABLE(‘OWNER’,’TABLE’,2);