Copiando tabelas sem utilizar export/import

Objetivo:

Uma tarefa muito comum no dia a dia do DBA é copiar tabelas de uma base de produção para uma base de desenvolvimento ou Homologação. Essa tarefa na maioria das vezes é realizada utilizando o export/import,uma alternativa a esse procedimento seria utilizar o comando COPY que é um recurso disponível no SQL*Plus.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 9.2.0.4.0 e Oracle Database 10.2.0.5.0

Restrições:

1. O comando COPY suporta somente os seguintes datatypes: CHAR, DATE, LONG, NUMBER e VARCHAR2;

2. A versão 9.2.0.1.0 do SQL*Plus possui um bug: 4350952

   Possíveis contornos para esse bug:  

    a) Utilize um cliente SQL*Plus das versões 10g, 11g  ou 9.0.1.4.0; 
    b) Utilize a função TO_NUMBER  para o campo NUMBER sem especificação 
       das casas decimais. 
       Ex:    
       copy from Scott/Tiger@INST1 create NewTable    
            using Select to_number(Non_Precision_Column) ALIAS, 
                         COLUMN2, 
                         COLUMN3
                    from Scott.Tablename;        
    c) Utilize um cliente SQL*Plus no ambiente Unix;

Overview:

Nessa demonstração temos duas bases (instância: lab1 9.2.0.4.0 servidor: odbsrv92) e outra base como mesmo nome de instância (instância: lab1 10.2.0.5 servidor: odbsrv02). Vamos conectar na base 10g e copiar a tabela TEST_TAB do esquema APPL01 para o esquema TESTE na base 9i. Vamos utilizar a versão 9.2.0.1 do SLQ*Plus e devido ao bug 4350952 temos que utilizar a função TO_NUMBER no comando SELECT.

Tópicos:

1. Verificar existência da tabela na base 9i;
2. Copiando a tabela TEST_TAB;
3. Verificando a tabela copiada;

1. Verificar existência da tabela na base 9i

SQL> select INSTANCE_NAME, HOST_NAME, VERSION from v$instance;

INSTANCE_NAME   |HOST_NAME              |VERSION
----------------|-----------------------|-----------------
lab1            |odbsrv92.localdomain   |9.2.0.4.0

1 linha selecionada.

SQL> select table_name from dba_tables where owner = 'TESTE';

não há linhas selecionadas

SQL>

.

2. Copiando a tabela TEST_TAB;

SQL> select INSTANCE_NAME, HOST_NAME, VERSION from v$instance;

INSTANCE_NAME    HOST_NAME                  VERSION
---------------- -------------------------- ------------
lab1             odbsrv02.localdomain       10.2.0.5.0

Decorrido: 00:00:01.04
SQL> desc appl01.TEST_TAB;
 Nome            Nulo?    Tipo
 --------------- -------- ----------------
 REGID                    NUMBER
 NOME                     VARCHAR2(30)
 ENDERECO                 VARCHAR2(30)
 DATA                     DATE
 COMENTARIO               VARCHAR2(100)

SQL> set arraysize 1000
SQL> set copyc 100
SQL> copy to teste/oracle@lab92 CREATE test_copy 
          using select to_number(REGID) REGID, 
                       NOME, 
                       ENDERECO, 
                       DATA, 
                       COMENTARIO 
                  from appl01.TEST_TAB;

Tamanho do vetor busc./ligaç.é 1000. (tamanho do vetor é 1000)
Validará após cada 100 ligações de vetor. (validaçãocópia é 100)
Extensão de tam.máximo é 10000000. (extensão é 10000000)
Tabela TEST_COPY criada.

   70004 linhas selecionadas da conexão DEFAULT HOST.
   70004 linhas inseridas em TEST_COPY.
   70004 linhas validadas em TEST_COPY no teste@lab92.

SQL> 

.

3. Verificando a tabela copiada;

SQL*Plus: Release 9.2.0.1.0 - Production on Seg Jul 12 10:24:25 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn system/oracle@lab92;
Conectado.
SQL> select INSTANCE_NAME, HOST_NAME, VERSION from v$instance;

INSTANCE_NAME    HOST_NAME              VERSION
---------------- ---------------------- ---------------
lab1             odbsrv92.localdomain   9.2.0.4.0

SQL> 
SQL> select table_name from dba_tables where owner = 'TESTE';

TABLE_NAME
------------------------------
TEST_COPY

SQL> select count(*) from teste.TEST_COPY;

  COUNT(*)
----------
     70004

SQL>