Movendo Tabela para Tablespace Diferente

Publicado: fevereiro 26, 2013 em Tablespace
Tags:

Uma das primeiras perguntas que um DBA Júnior deve saber responder é: Quais são os métodos para transferir uma tabela de uma tablespace para outra e entender o que acontece ao utilizar cada método para saber qual deve utilizar em determinada situação.

Vou tentar detalhar os seguintes métodos:
1. CREATE TABLE … AS SELECT
2. ALTER TABLE … MOVE TABLESPACE …
3. EXP/IMP ou EXPDP/IMPDP
      
– Criação da tabela com PK e índice para os testes:

     
SQL> CREATE TABLE TEST
      (TEST_ID NUMBER(9) NOT NULL,
       TEST_DESC VARCHAR(10),
      CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID) USING INDEX TABLESPACE INDEXTBS);
      /
      
      SQL> CREATE INDEX TEST_IND_1 ON TEST(TEST_DESC)  TABLESPACE INDEXTBS
      /
      
Realizar alguns INSERTs:
Ex.:
      
      SQL> INSERT INTO TEST VALUES(1,’Hello’);
      SQL> COMMIT;
      
– Criação de scripts de consulta:
      
table.sql
      
      SQL> SELECT TABLESPACE_NAME,
                      TABLE_NAME,
                      STATUS
        FROM DBA_TABLES
      WHERE TABLE_NAME = ‘&1’;
      
index.sql
      
      SQL> SELECT TABLESPACE_NAME,
                                   INDEX_NAME,
                                   STATUS
                     FROM DBA_INDEXES
                  WHERE TABLE_NAME = ‘TEST’;
      

1. CREATE TABLE … AS SELECT 

      

Esse método consiste na criação de uma cópia idêntica da tabela em uma tablespace de destino.

      
– Vamos verificar, primeiramente, em que tablespace a tabela e seus índices estão armazenados:
      
@table.sql TEST
      
      TABLESPACE_NAME        TABLE_NAME            STATUS
      ——————————————————————-
      USERS                                      TEST                                VALID
@index.sql TEST
      
      TABLESPACE_NAME        INDEX_NAME            STATUS
      ————-——————————————————-
      INDEXTBS                             PK_TEST                        VALID
      INDEXTBS                             TEST_IND_1                VALID
– Então criaremos a cópia da tabela na tablespace de origem utilizando o CTSA:
      
      CREATE TABLE TEST2 TABLESPACE MARCUSSOARES AS SELECT * FROM TEST;
      
– Verificando os atributos da tabela criada:
      
@table.sql TEST
            
      TABLESPACE_NAME             TABLE_NAME                STATUS
      ———–————————————————————–
      MARCUSSOARES                     TEST2                                 VALID
      
Obs.: O comando CTAS somente cria as constraints NOT NULL relacionadas às colunas da tabela origem na tabela de destino, ou seja, devem-se criar as constraints e indexes referenciando a nova tabela.
      
– Para gerar os DDLs referentes a constraint e o índice que criei utilizei os seguintes comandos:
      
      SQL> set pages 1000
      SQL> set long 9999
      Adiciona ‘;’ ao final dos comandos retornados no resultado.
      SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,’SQLTERMINATOR’,true);
      Retira os atributos de segmento dos comandos retornados no resultado.
      SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,’SEGMENT_ATTRIBUTES’,false);
      SQL> SELECT dbms_metadata.get_ddl(‘CONSTRAINT’,CONSTRAINT_NAME,OWNER)
         FROM dba_constraints
      WHERE TABLE_NAME = ‘TEST’;
      
Resultado: ALTER TABLE “MARCUSSOARES”.”TEST2″ ADD CONSTRAINT “PK_TEST2” PRIMARY KEY (“TEST_ID”) USING INDEX TABLESPACE INDEXTBS ENABLE;
      
     SQL>  SELECT dbms_metadata.get_ddl(‘INDEX’,INDEX_NAME,OWNER)
         FROM DBA_INDEXES
      WHERE TABLE_NAME = ‘TEST’
           AND index_name not in (SELECT INDEX_NAME
                                                                  FROM dba_constraints
                                                               WHERE table_name = ‘TEST’
                                                                     AND INDEX_NAME is not null);
Resultado:  CREATE INDEX “MARCUSSOARES”.”TEST_IND_2″ ON “MARCUSSOARES”.”TEST2″ (“TEST_DESC”) TABLESPACE INDEXTBS;
      
Obs.: Adicionei aos comandos retornados a opção para que sejam criados os índices na tablespace destinada ao armazenamento de índice.
     
– Depois de criar os índices, farei a consulta para verificar as informações dos mesmos:
      
@index.sql
      
      TABLESPACE_NAME          INDEX_NAME                  STATUS
      ————————————————————————-
      INDEXTBS                               PK_TEST2                           VALID
      INDEXTBS                               TEST_IND_2                      VALID
      
– Depois executa o DROP da tabela de destino e altera-se o nome da tabela de destino para TEST:
      
      DROP TABLE MARCUSSOARES.TEST;
      ALTER TABLE MARCUSSOARES.TEST2 RENAME TO TEST;
      
Assim, finalizamos a transferência da tabela para outro tablespace com o comando CTAS. Lembrando que se tiver Triggers, também devem ser recriadas manualmente na outra tablespace.
      
      
2. ALTER TABLE … MOVE TABLESPACE …
      
Depois de recriar cenário de teste, utilizaremos o comando “ALTER TABLE … MOVE TABLESPACE …” para mover a tabela para outra tablespace. A tabela TEST encontra-se na tablespace USERS, como mostra a consulta a abaixo:
      
@table.sql TEST
      
      TABLESPACE_NAME             OWNER            TABLE_NAME             STATUS
      —————————————————————————————-
      USERS                               MARCUSSOARES     TEST                                 VALID
      
@index.sql TEST
      
      TABLESPACE_NAME             INDEX_NAME       STATUS
      ——————————————————
      INDEXTBS                    PK_TEST          VALID
      INDEXTBS                    TEST_IND_1       VALID
      
– Então executamos o comando ALTER TABLE … MOVE TABLESPACE … para mover a tabela de TEST:
      
     SQL>  ALTER TABLE MARCUSSOARES.TEST MOVE TABLESPACE MARCUSSOARES;
      
– Agora consultamos as informações sobre a tabela e seus índices:
      
@tables.sql TEST
      
      TABLESPACE_NAME          OWNER            TABLE_NAME                STATUS
      —————————————————————————-
      MARCUSSOARES             MARCUSSOARES     TEST                      VALID
      
@index.sql TEST
      
      TABLESPACE_NAME             INDEX_NAME                STATUS
      —————————————————————-
      INDEXTBS                    PK_TEST                   UNUSABLE
      INDEXTBS                    TEST_IND_1                UNUSABLE
      
Verificamos então, que os índices ficaram com o status de UNUSABLE. Isto ocorre porque os ROWIDs da linhas da tabela serão modificados, ocasionando inconsistência com os ROWIDs que estão armazenados no índex.
      
– Para reconstruir os índexes utilizaremos os seguintes comandos:
      
      ALTER INDEX MARCUSSOARES.PK_TEST REBUILD;
      ALTER INDEX MARCUSSOARES.TEST_IND_1 REBUILD;
      
– Então verificamos seus status:
      
@index.sql TEST
      
      TABLESPACE_NAME             INDEX_NAME            STATUS
      ————————————————————
      INDEXTBS                    PK_TEST               VALID
      INDEXTBS                    TEST_IND_1            VALID
      
E assim, finalizamos a transferência da tabela TEST para outra tablespace.
      
      
3. EXP/IMP ou EXPDP/IMPDP
      
Agora iremos mover a tabela para outra tablespace através da ferramenta de importação e exportação de dados, Data Pump.
      
     [oracle@orcl ~] expdp system@dbprod tables=MARCUSSOARES.TEST dumpfile=Expdp_Test.dmp logfile=Expdp_Test.log directory=DUMP_DIR
      
Export: Release 11.2.0.1.0 – Production on Fri Jan 4 17:24:08 2013
      
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:
      
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″:  system/********@dbprod tables=MARCUSSOARES.TEST dumpfile=Expdp_Test.dmp logfile=Expdp_Test.log directory=DUMP_DIR
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported “MARCUSSOARES”.”TEST”                       5.531 KB       5 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/Expdp_Test.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at 17:24:59
      
E para alterar a tablespace iremos utilizar, no Expdp, o parâmetro REMAP_TABLESPACE. Para remapear as tablespaces anteriores para as tablespaces indicadas.
      
      [oracle@orcl ~] impdp system@DBPROD dumpfile=Expdp_Test.dmp directory=DUMP_DIR remap_tablespace=USERS:MARCUSSOARES table_exists_action=replace
      
Import: Release 11.2.0.1.0 – Production on Mon Jan 7 12:09:56 2013
      
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:
      
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″:  system/********@DBPROD dumpfile=Expdp_Test.dmp directory=DUMP_DIR remap_tablespace=USERS:MARCUSSOARES table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “MARCUSSOARES”.”TEST”                       5.531 KB       5 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at 12:10:09
      
Obs.: Esse método tem a vantagem de exportar/importar os índices/constraints/triggers que estão relacionadas à tabela que estamos movendo.
      
– Verificando os status dos objectos envolvidos no teste:
      
@table.sql TEST
      
      TABLESPACE_NAME        OWNER             TABLE_NAME         STATUS
      ———————————————————————
      MARCUSSOARES           MARCUSSOARES      TEST               VALID
      
@index.sql TEST
      
      TABLESPACE_NAME        INDEX_NAME        STATUS
      —————————————————————
      INDEXTBS               PK_TEST           VALID
      INDEXTBS               TEST_IND_1        VALID
      
comentários
  1. Marcelo Arizo disse:

    Qual destes 3 métodos seria o mais rápido para uma tabela com milhões de dados?

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s