Redefinição online de tabelas com DBMS_REDEFINITION
Visão Geral
Em sistemas de gerenciamento de banco de dados, há a necessidade de modificar as estruturas físicas ou lógicas de uma tabela, para melhorar consultas ou DML, para adaptação da aplicação ou gerenciamento de armazenamento.
Geralmente as aplicações exigem que durante o processo de modificação a tabela esteja disponível. Para isso temos a Redefinição online de tabela que é uma funcionalidade para versão do banco de dados Oracle Enterprise Edition, foi lançada no Oracle 9i e melhorada no Oracle 10g com procedimento COPY_TABLE_DEPENDENTS, este que permite copiar objetos dependentes da tabela, por exemplo, índices, gatilhos, entre outros.
Essa funcionalidade, de modo geral, possibilita:
- Modificar parâmetros de armazenamento.
- Mover a tabela para outra tablespace.
- Adicionar, modificar, alternar a localização ou deletar colunas da tabela.
- Adicionar ou deletar uma partição.
- Alterar estrutura da partição.
- Adicionar suporte a consultas paralelas.
- Recriar uma tabela para reduzir fragmentação.
- Entre outras, consultar link na referência.
Observação: A redefinição não é 100% online como sugerido, durante o processo existe um pequeno intervalo de tempo, no qual a tabela é locada, porém, além de curto é independente do tamanho da tabela e de alterações realizadas, o que pode interferir nesse tempo é a quantidade de DML simultâneos.
Pré-requisitos para o Lab
- Iremos testar na versão mais atual, Banco de dados 18c Enterprise Edition.
- Espaço livre, referente ao tamanho da tabela redefinida, pouco provável que isso seja problema.
- Devidos privilégios aplicados.
- Tabela alvo.
- create user data identified by data;
Privilégios requeridos.
- GRANT UNLIMITED TABLESPACE TO DATA;
- GRANT CREATE SESSION TO DATA;
- GRANT EXECUTE ON DBMS_REDEFINITION TO DATA;
- GRANT REDEFINE ANY TABLE TO DATA;
- GRANT ADMINISTER DATABASE TRIGGER TO DATA;
- GRANT ALTER ANY INDEX TO DATA;
- GRANT ALTER ANY MATERIALIZED VIEW TO DATA;
- GRANT ALTER ANY SEQUENCE TO DATA;
- GRANT ALTER ANY TRIGGER TO DATA;
- GRANT CREATE ANY INDEX TO DATA;
- GRANT CREATE ANY MATERIALIZED VIEW TO DATA;
- GRANT CREATE ANY SEQUENCE TO DATA;
- GRANT CREATE ANY TABLE TO DATA;
- GRANT CREATE ANY TRIGGER TO DATA;
- GRANT CREATE ANY VIEW TO DATA;
- GRANT CREATE MATERIALIZED VIEW TO DATA;
- GRANT CREATE SESSION TO DATA;
- GRANT CREATE VIEW TO DATA;
- GRANT DROP ANY INDEX TO DATA;
- GRANT DROP ANY MATERIALIZED VIEW TO DATA;
- GRANT DROP ANY SEQUENCE TO DATA;
- GRANT DROP ANY TRIGGER TO DATA;
- GRANT DROP ANY VIEW TO DATA;
- GRANT EXECUTE ANY PROCEDURE TO DATA;
- GRANT INSERT ANY TABLE TO DATA;
- GRANT MERGE ANY VIEW TO DATA;
- GRANT SELECT ANY DICTIONARY TO DATA;
- GRANT SELECT ANY TABLE TO DATA;
- GRANT UNDER ANY VIEW TO DATA;
- GRANT UPDATE ANY TABLE TO DATA;
Procedimentos
Objetivos do exemplo:
- Remover coluna “name”.
- Adicionar coluna “obs2”.
- A coluna do tipo LOB é alterada para SECUREFILES.
- Particionar a tabela pela coluna “ID”.
- Move tabela para outra tablespace.
- Habilitar compressão de linha.
- A tabela é alterada de “index-organized” para “heap-organized”.
- O tipo de dado da coluna “DATA” será alterada de DATE para TIMESTAMP.
Essas quantidade de alterações, tanto físicas quanto lógicas, nos mostram o poder do DBMS_REDEFINITION.
Vamos lá!
sqlplus data/data
-- Criar tabela alvo
CREATE TABLE TESTE(
id NUMBER PRIMARY KEY,
name VARCHAR2(10),
obs CLOB,
data DATE)
ORGANIZATION INDEX;
-- Tablespace diferente para alocar nova tabela
CREATE TABLESPACE ts_teste
DATAFILE '/u01/oradata/ORCL/ts_teste01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- Objeto indice relacionado a tabela teste
CREATE INDEX idx_teste ON teste(name);
-- Sequence
CREATE SEQUENCE seq_teste;
-- Trigger relacionada
CREATE OR REPLACE TRIGGER trg_teset
BEFORE INSERT ON TESTE
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT seq_teste.NEXTVAL
INTO :new.id
FROM dual;
END;
/
-- Listar objetos dependentes
SQL> COLUMN object_name FORMAT A25
SQL> SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'DATA';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------- ----------------------- -------
TESTE TABLE VALID
SYS_IOT_TOP_74166 INDEX VALID
SYS_IL0000074166C00003$$ INDEX VALID
SYS_LOB0000074166C00003$$ LOB VALID
IDX_TESTE INDEX VALID
SEQ_TESTE SEQUENCE VALID
TRG_TESET TRIGGER VALID
-- Simulando a fragmentação na tabela
DECLARE
V_CLOB CLOB;
BEGIN
FOR I IN 0..999 LOOP
V_CLOB := NULL;
FOR J IN 1..1000 LOOP
V_CLOB := V_CLOB||TO_CHAR(I,'0000');
END LOOP;
INSERT INTO data.teste VALUES(I,TO_CHAR(I),V_CLOB,TRUNC(SYSDATE));
COMMIT;
END LOOP;
COMMIT;
END;
/
-- delete para fragmentação
DELETE FROM TESTE WHERE (ID/3) <> TRUNC(ID/3);
-- Para confirmar a fragmentação você pode utilizar DBMS_SPACE.SPACE_USAGE.
COLUMN object_name FORMAT A40
SELECT object_name, object_type, status FROM user_objects;
-- Passos para redefinição.
-- Verifica se a tabela poderá ser redefinida
EXEC DBMS_REDEFINITION.can_redef_table('DATA', 'TESTE');
-- Tabela temporária para o processo
CREATE TABLE TEMP(
id NUMBER,
data TIMESTAMP,
obs CLOB,
obs2 VARCHAR2(3))
LOB(obs) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING)
PARTITION BY RANGE (id) (
PARTITION par1 VALUES LESS THAN (333),
PARTITION par2 VALUES LESS THAN (666),
PARTITION par3 VALUES LESS THAN (MAXVALUE))
TABLESPACE ts_teste
ROW STORE COMPRESS ADVANCED;
-- Ínicio na processo de redefinição
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'DATA',
orig_table => 'TESTE',
int_table => 'TEMP',
col_mapping => 'id id, TO_TIMESTAMP(data) data, obs obs');
END;
/
-- Abort caso ocorra algum erro será necessário abortar antes de redefinir.
EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'DATA',orig_table=>'TESTE',int_table=>'TEMP');
-- Nesse momento é criada uma view materializada
SQL> SELECT object_name, object_type, status FROM user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
---------------------------------------- ----------------------- -------
SYS_IL0000074206C00003$$ INDEX PARTITION VALID
SYS_IL0000074206C00003$$ INDEX PARTITION VALID
SYS_IL0000074206C00003$$ INDEX PARTITION VALID
SYS_IL0000074206C00003$$ INDEX VALID
SYS_LOB0000074206C00003$$ LOB PARTITION VALID
SYS_LOB0000074206C00003$$ LOB PARTITION VALID
SYS_LOB0000074206C00003$$ LOB PARTITION VALID
SYS_LOB0000074206C00003$$ LOB VALID
RUPD$_TESTE TABLE VALID
I_MLOG$_TESTE INDEX VALID
MLOG$_TESTE TABLE VALID
-- Simulação de transações durante processo de redefinição.
INSERT INTO TESTE (ID,NAME,OBS,DATA) VALUES(22,'DATAUNIQUE','DATAUNIQUE OBS','');
select * from MLOG$_TESTE;
-- Copiar objetos dependentes
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'DATA',
orig_table => 'TESTE',
int_table => 'TEMP',
copy_indexes => 1,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => TRUE,
num_errors => num_errors);
END;
/
-- Sincronimo das alterações.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'DATA',
orig_table => 'TESTE',
int_table => 'TEMP');
END;
/
-- Existe alterações a serem aplicadas?
SQL> select * from MLOG$_TESTE;
-- Finalização da redefinição
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'DATA',
orig_table => 'TESTE',
int_table => 'TEMP');
END;
/
-- Recompilar trigger (Caso não tivessemos utilizada "COPY_TABLE_DEPENDENTS" a trigger dropada)
SQL> ALTER TRIGGER TRG_TESET COMPILE;
Referências
Tabela (Feature / Option / Pack): https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC-GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4
Link DBMS_REDEFINITION: https://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS042
Exemplos de redefinição: https://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN11677
https://oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1
SecureFiles LOBS: https://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB4444