Redefinição online de tabelas com DBMS_REDEFINITION

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

COMENTÁRIOS

Política de Privacidade e Termos de Uso de Dados - Dataunique Tecnologia da Informação LTDA

A Dataunique Tecnologia da Informação LTDA, empresa devidamente registrada sob o CNPJ 15.179.495/0001-35, compromete-se a proteger a privacidade e segurança dos dados pessoais de seus usuários. Esta política descreve como coletamos, usamos, compartilhamos e protegemos as informações pessoais fornecidas por você.

1. Informações Coletadas

1.1. A Dataunique coleta informações fornecidas voluntariamente por você, como nome, endereço, e-mail, número de telefone, entre outras, durante o cadastro ou utilização de nossos serviços.

1.2. Dados de acesso e utilização de nossos serviços, como endereço IP, tipo de navegador, páginas visitadas e tempo de permanência, podem ser automaticamente registrados para melhorar a qualidade dos serviços oferecidos.

2. Uso de Informações

2.1. As informações coletadas são utilizadas para fornecer, manter, proteger e melhorar nossos serviços, bem como para desenvolver novos serviços.

2.2. Podemos utilizar seus dados para personalizar conteúdos, oferecer suporte ao cliente, enviar atualizações, newsletters e informações sobre novos produtos ou serviços.

3. Compartilhamento de Informações

3.1. A Dataunique não compartilha informações pessoais com terceiros, exceto quando necessário para cumprir obrigações legais, proteger nossos direitos ou em situações autorizadas por você.

4. Segurança de Dados

4.1. Utilizamos medidas de segurança adequadas para proteger suas informações contra acessos não autorizados, alterações, divulgação ou destruição não autorizada.

5. Cookies e Tecnologias Semelhantes

5.1. Utilizamos cookies e tecnologias semelhantes para melhorar a experiência do usuário, analisar o tráfego e personalizar conteúdos.

6. Seus Direitos

6.1. Você tem o direito de acessar, corrigir ou excluir suas informações pessoais. Para exercer esses direitos ou esclarecer dúvidas, entre em contato com nosso Encarregado de Proteção de Dados (DPO) através do e-mail [email protected].

7. Alterações na Política de Privacidade

7.1. Reservamo-nos o direito de alterar esta política a qualquer momento, e as alterações serão comunicadas por meio de nossos canais de comunicação.

Ao utilizar nossos serviços, você concorda com os termos desta Política de Privacidade. Recomendamos a leitura regular desta política para se manter informado sobre como tratamos seus dados pessoais.

Dados de Contato:

  • Endereço: Rua T30, 2515, Quadra 99 Lote 11/14, Sala 1404 e 1405, Edif Walk Bueno Business Edif e Lifestyle, SET BUENO, Goiânia – GO, 74215-060.
  • Telefone: (62) 99906-0584
  • Fax/Mensageiro Online: (62) 3223-2257
  • E-mail: [email protected]

Data de vigência: [Data de atualização da política]

Atenciosamente,

Dataunique Tecnologia da Informação LTDA