logo
Entre em contato e saiba mais sobre como a DataUnique pode transformar seu negócio.

Fale Conosco

contato@dataunique.com.br
(62) 3932-2500
banner-winthor-sidebar
 

Redefinição online de tabelas com DBMS_REDEFINITION

Redefinição online de tabelas com DBMS_REDEFINITION

banner-winthor-header

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

Hugo Castro

hugo.castro@dataunique.com.br

Nenhum Comentário

Escreva um Comentário

Comentário
Nome
Email
Website