Introdução
Gerenciar estrutura de objetos em um banco de dados pode ser uma tarefa delicada, principalmente quando se trata de um banco de dados de produção. Recentemente precisei replicar alguns objetos criados em um banco de desenvolvimento para um banco de produção manualmente, utilizando PL/SQL.
No meu cenário, precisei gerar um script mais genérico para criar tabelas, índices, sequences e triggers na sequencia correta para evitar que algum objeto dependente ficasse inválido. Nesse post será apresentado uma versão mais enxuta do script utilizado, atendendo somente o escopo de tabelas.
Objetivo
Percorrer todas as tabelas de um determinado schema e gerar um novo script PL/SQL de saída contendo as instruções para criar as tabelas em outro banco de dados.
O script gerado deve ser capaz de verificar se a tabela a ser criada já existe no schema atual para evitar erros como “ORA-00955 – name is already used by an existing object“.
Lógica
Pode parecer confuso, mas a ideia aqui é criar um script PL/SQL que ao ser executado no banco de dados de origem (onde as tabelas já existem), gera um segundo script PL/SQL que deve ser executado no banco de dados de destino (onde as tabelas devem ser criadas).
Abaixo a estrutura do script principal que deve ser executado no banco de origem e a seguir explico com mais detalhes sua lógica.
Para começar, declaro uma variável chamada vSQL do tipo VARCHAR2, essa variável será utilizada para auxiliar a formação do script de saída criado dinamicamente.
Em seguida, declaro um cursor chamado TABELAS o qual é carregado com a lista de tabelas que serão geradas a instrução CREATE TABLE no script de saída. Observe que utilizo a view USER_TABLES, pois desejo exportar as tabelas do usuário que estou logado no momento. Também adiciono um filtro: Somente as tabelas com prefixo SYNC. O filtro é opcional e vai depender da sua necessidade.
DECLARE vSQL VARCHAR2(4000); CURSOR TABELAS IS SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'SYNC%';
Para obter a instrução completa para criar as tabelas, utilizo a package DBMS_METADATA que é fornecida pela Oracle para obter a instrução DDL de qualquer objeto no banco de dados.
Antes de obter o DDL das tabelas, altero dois parâmetros importantes: SEGMENT_ATTRIBUTES e SQLTERMINATOR. Respectivamente, eles indicam se a instrução DDL exportada incluirá os atributos de armazenamento do objeto (o nome da tablespace, por exemplo) e se a instrução DDL incluirá um “;” no final do comando.
dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false); dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',false);
Observe que eu passo o valor false para ambos os parâmetros. No caso de SEGMENT_ATTRIBUTES é porque eu desejo criar as tabelas no tablespace default do usuário logado no banco de dados de destino. Em relação a SQLTERMINATOR é porque o script será executado em um comando EXECUTE IMMEDIATE, e aqui o terminador “;” não é aceito pelo Oracle, gerando o erro “ORA-00911-Invalid Character“.
A seguir, começo a gerar o script de saída. A maneira mais prática no meu cenário foi imprimir a saída com a package DBMS_OUTPUT e depois copiar o script completo na guia OUTPUT da janela do PL/SQL Developer.
O primeiro passo é imprimir o início do bloco anônimo com DECLARE, uma variável vCONT e o BEGIN.
DBMS_OUTPUT.put_line( 'DECLARE vCONT INT; BEGIN');
Depois um loop percorre todos os registros do cursor TABELAS e cria uma string contendo uma consulta SQL que realiza uma contagem na view USER_TABLES e armazena o resultado da contagem na variável vCONT. O nome da tabela é concatenada e usada como filtro da cláusula WHERE da consulta.
Após realizar a contagem, uma condição IF verifica se o valor em vCONT é igual a zero, ou seja, se a tabela em questão não existe.
Caso o resultado do teste seja verdadeiro, então o script faz um EXECUTE IMMEDIATE e como parâmetro passa uma string contendo a instrução CREATE TABLE.
Observe que o conteúdo do script é formado dinamicamente por uma string literal concatenada com o nome da tabela e com a instrução DDL referente a mesma, obtida com a procedure GET_DDL da package DBMS_METADATA.
Os parâmetros dessa procedure são: Tipo de objeto, nome do objeto, nome do usuário proprietário do objeto.
DBMS_METADATA(OBJECT_TYPE, OBJECT_NAME, OWNER)
Como estamos trabalhando com tabelas, para o parâmetro OBJECT_TYPE é informado o valor ‘TABLE‘.
Já para o parâmetro OBJECT_NAME, passo o nome da tabela atual no cursor: T.TABLE_NAME.
Para finalizar, informo o nome do usuário proprietário das tabelas. Como as tabelas pertencem ao meu próprio usuário, passo a função USER que retorna o nome do usuário logado no Oracle (poderia ser qualquer outro valor, como ‘MAICON’).
FOR T IN TABELAS LOOP
vSQL := '
SELECT COUNT(*) INTO vCONT
FROM USER_TABLES
WHERE TABLE_NAME = ''' || T.TABLE_NAME || ''';
IF vCONT = 0 THEN
EXECUTE IMMEDIATE q''[' || DBMS_METADATA.GET_DDL('TABLE', T.TABLE_NAME, USER) ||']'';
END IF;' ;
DBMS_OUTPUT.put_line(vSQL);
END LOOP;
Para finalizar essa etapa, a procedure PUT_LINE da package DBMS_OUTPUT é utilizada para imprimir a variável vSQL (que contém a string já concatenada).
Esse trecho é repetido uma vez para cada nome de tabela contida no cursor. Ou seja, caso 100 tabelas sejam retornadas, o script de saída terá 100 SELECT COUNT(*) … com 100 IF vCONT = 0 … e 100 EXECUTE IMMEDIATE.
Veja um exemplo do script gerado para uma tabela chamada SYNC_PCDISTTRIB:
SELECT COUNT(*) INTO vCONT FROM USER_TABLES WHERE TABLE_NAME = 'SYNC_PCDISTTRIB'; IF vCONT = 0 THEN EXECUTE IMMEDIATE q'[ CREATE TABLE SYNC_PCDISTRIB ( ID NUMBER(8,0) NOT NULL ENABLE, CODDISTRIB VARCHAR2(4), DATAHORAULTATUALIZACAO DATE, ULTIMAOPERACAO VARCHAR2(1), CONSTRAINT PCDISTRIB_PK PRIMARY KEY (ID) ENABLE ) ]'; END IF;
Depois de percorrer a última linha do cursor, esse script também imprime a string END; para fechar o bloco de PL/SQL anônimo do script de saída.
Abaixo um print de uma parte do script completo gerado na saída do PL/SQL Developer. Observe que alterei o valor do Buffer Size para 1000000, caso contrário, se o script for muito grande o programa pode interromper a saída por conta do limite padrão.
Conclusão
Depois de executar esse script no banco de dados de origem. Podemos copiar todo o conteúdo na guia OUTPUT do PL/SQL Developer e executar como um bloco PL/SQL anônimo no banco de dados de destino. As tabelas serão criadas em sequência de tal forma que não precisamos nos preocupar se a tabela existe ou não.