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