outubro 2017 Alex Zaballa - DBA Oracle - ex-Oracle ACE Director, OCM, OCA, OCP, OCE, OCS

This article is part of series that used to be called OTN Appreciation Day. Now, it is Oracle Developer Community Appreciation Day.

The ODC Appreciation Day was proposed by Tim Hall and you can find more information here:

https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/

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 OLTP compression.
COLUMN STORE COMPRESS FOR QUERY : Hybrid Columnar Compression (HCC) disponível no Exadata, ZFS storage appliances e Pillar Axiom Storage Systems.
COLUMN STORE COMPRESS FOR ARCHIVE : Hybrid Columnar Compression (HCC) disponível no Exadata, ZFS storage appliances e Pillar Axiom Storage Systems.

*Opções que contenham espaços em branco devem ser colocadas 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 - DBA Oracle - ex-Oracle ACE Director, OCM, OCA, OCP, OCE, OCS

Oracle Blog

terça-feira, 10 de outubro de 2017

ODC Appreciation Day: Dois novos recursos do Oracle Data Pump 12c

This article is part of series that used to be called OTN Appreciation Day. Now, it is Oracle Developer Community Appreciation Day.

The ODC Appreciation Day was proposed by Tim Hall and you can find more information here:

https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/

Change Table Compression at Import

Change Table LOB Storage at Import

Translate

DISCLAIMER

Oracle ACE Program

Certificações

OraWorld

Sou Articulista

Sou Articulista

Arquivo do blog

Total de visualizações de página

Sobre