Dois novos recursos do Oracle Data Pump 12c – parte 2
Por Alex Zaballa ,
Postado em Setembro 2014
Revisado por Marcelo Pivovar - Sulution Architect
Indice:
- Dois novos recursos do Oracle Data Pump 12c - Parte 1
- Dois novos recursos do Oracle Data Pump 12c - Parte 2
Change Table Compression at Import
Em versões anteriores ao 12c, o Data Pump sempre realizava as importações utilizando as mesmas configurações de compressão que estavam presentes no processo de exportação. No 12c, ele foi aprimorado para permitir que você especifique um método de compressão no momento da importação, independentemente do método de compressão usado quando os dados foram exportados. Com isso, você pode comprimir ou descomprimir a tabela durante a importação. Isto é possível devido à introdução de um novo parâmetro da cláusula TRANSFORM, chamado TABLE_COMPRESSION_CLAUSE. Alguns valores permitidos:
- NONE : Se for omitido, a tabela assume as características da tablespace.
- NOCOMPRESS : Desativa a compressão da tabela.
- COMPRESS : Habilita a compressão básica.
- ROW STORE COMPRESS BASIC : O mesmo que o COMPRESS.
- ROW STORE COMPRESS ADVANCED : Habilita a compressão avançada, conhecida como ulTP compression.
- CulUMN STORE COMPRESS FOR QUERY : Hybrid Culumnar Compression (HCC) disponível no Exadata, ZFS storage appliances e Pillar Axiom Storage Systems.
- CulUMN STORE COMPRESS FOR ARCHIVE : Hybrid Culumnar Compression (HCC) disponível no Exadata, ZFS storage appliances e Pillar Axiom Storage Systems.
*Opções que contenham espaços em branco devem ser culocadas entre aspas simples ou duplas.
Iremos confirmar se a tabela de origem está com a compressão desativada:
[oracle@oracle01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 8 11:26:52 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select owner,table_name,compression from dba_Tables where owner='HR' and table_name = 'EMPLOYEES';
OWNER TABLE_NAME COMPRESS
-------------------- -------------------- --------
HR EMPLOYEES DISABLED
Criando o diretório no banco de dados para realizar o export:
SQL> create directory dir_teste as '/tmp/zaballa';
Directory created.
Exportando a tabela que será usada para o import com transform=table_compression_clause:compress:
[oracle@oracle01 ~]$ expdp alex_zaballa dumpfile=hr_employees.dmp directory=dir_teste tables=hr.employees
Export: Release 12.1.0.2.0 - Production on Fri Aug 8 11:33:05 2014
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "ALEX_ZABALLA"."SYS_EXPORT_TABLE_01":
alex_zaballa/******** dumpfile=hr_employees.dmp directory=dir_teste tables=hr.employees
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
Master table "ALEX_ZABALLA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ALEX_ZABALLA.SYS_EXPORT_TABLE_01 is:
/tmp/zaballa/hr_employees.dmp
Job "ALEX_ZABALLA"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 8 11:33:40 2014 elapsed 0 00:00:32
Importando a tabela e habilitando a compressão:
[oracle@oracle01 ~]$ impdp alex_zaballa directory=dir_teste dumpfile=hr_employees.dmp remap_schema=hr:alex_zaballa
transform=table_compression_clause:compress
Import: Release 12.1.0.2.0 - Production on Fri Aug 8 12:07:48 2014
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ALEX_ZABALLA"."SYS_IMPORT_FULL_01": alex_zaballa/******** directory=dir_teste dumpfile=hr_employees.dmp
remap_schema=hr:alex_zaballa transform=table_compression_clause:compress
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ALEX_ZABALLA"."EMPLOYEES" 17.08 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully completed at Fri Aug 8 12:08:23 2014 elapsed 0 00:00:32
Podemos verificar que agora a tabela está com a compressão ativada:
SQL> select owner,table_name,compression from dba_Tables where owner='ALEX_ZABALLA' and table_name = 'EMPLOYEES';
OWNER TABLE_NAME COMPRESS
-------------------- -------------------- --------
ALEX_ZABALLA EMPLOYEES ENABLED
Change Table LOB Storage at Import
No 12c, podemos especificar o método de armazenamento de LOBs que será usado no Data Pump durante o processo de importação, independente de como foi realizado o export. Isto fornece um método simples que permite aos usuários migrar facilmente de BasicFile LOBs para SecureFile LOBs. Isto é possível devido à introdução de um novo parâmetro da cláusula TRANSFORM, chamado LOB_STORAGE. Alguns valores permitidos:
- SECUREFILE : Os LOBS serão armazenados como SecureFiles.
- BASICFILE : Os LOBS serão armazenados como BasicFiles.
- DEFAULT : O armazenamento do LOB é determinado pelo banco de dados.
- NO_CHANGE : O processo de importação irá utilizar as configurações criadas durante o processo de exportação.
Iremos criar uma tabela com um LOB do tipo BASICFILE:
SQL> CREATE TABLE HR.TABELA_TESTE
(coluna1 number(10),
coluna2 clob)
LOB (coluna2) STORE as BASICFILE TABLESPACE EXAMPLE;
Table created.
Iremos confirmar se o LOB da tabela de origem está como BASICFILE:
[oracle@oracle01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 8 12:38:25 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select owner,table_name,tablespace_name,securefile from dba_lobs where
owner='HR' and table_name='TABELA_TESTE';
OWNER TABLE_NAME TABLESPACE_NAME SEC
-------------------- ------------------ ------------------- ---
HR TABELA_TESTE EXAMPLE NO
Exportando a tabela que será usada para o import com transform=lob_storage:securefile:
[oracle@oracle01 ~]$ expdp alex_zaballa dumpfile=hr_tabela_teste.dmp directory=dir_teste tables=hr.tabela_teste
Export: Release 12.1.0.2.0 - Production on Fri Aug 8 12:33:03 2014
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "ALEX_ZABALLA"."SYS_EXPORT_TABLE_01": alex_zaballa/******** dumpfile=hr_tabela_teste.dmp
directory=dir_teste tables=hr.tabela_teste
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "HR"."TABELA_TESTE" 0 KB 0 rows
Master table "ALEX_ZABALLA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ALEX_ZABALLA.SYS_EXPORT_TABLE_01 is:
/tmp/zaballa/hr_tabela_teste.dmp
Job "ALEX_ZABALLA"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 8 12:33:51 2014 elapsed 0 00:00:43
Importando a tabela e habilitando o armazenamento do LOB em SECUREFILE:
[oracle@oracle01 ~]$ impdp alex_zaballa directory=dir_teste dumpfile=hr_tabela_teste.dmp
remap_schema=hr:alex_zaballa transform=lob_storage:securefile
Import: Release 12.1.0.2.0 - Production on Fri Aug 8 12:41:09 2014
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ALEX_ZABALLA"."SYS_IMPORT_FULL_01": alex_zaballa/******** directory=dir_teste
dumpfile=hr_tabela_teste.dmp remap_schema=hr:alex_zaballa transform=lob_storage:securefile
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ALEX_ZABALLA"."TABELA_TESTE" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully completed at Fri Aug 8 12:41:36 2014 elapsed 0 00:00:22
Podemos verificar que agora o LOB da tabela está armazenado como SECUREFILE:
SQL> select owner,table_name,tablespace_name,securefile from dba_lobs where
owner='ALEX_ZABALLA' and table_name='TABELA_TESTE';
OWNER TABLE_NAME TABLESPACE_NAME SEC
-------------------- ------------------- -------------------- ---
ALEX_ZABALLA TABELA_TESTE EXAMPLE YES
Alex Zaballa, formado em Análise de Sistemas, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Servidores de Aplicação e Sistemas Operacionais; trabalha com Oracle há 14 anos, é ORACLE ACE, certificado OCM Database 11G e conta com mais de 100 outras certificações em produtos da Oracle. Desde 2007 é funcionário da empresa Júpiter em Angula, alocado em um projeto no Ministério das Finanças. Alex também é fundador do Grupo de Usuários Oracle de Angula (GUOA) e membro do time OraWorld.
Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.