oracle把表改为压缩表,Oracle 12c 新特性 --- 数据泵在导入时更改表压缩

概念

There is a new impdp command-line option for Data Pump Import (as well as a new option for the PL/SQL DBMS_DATAPUMP package) that allows a user to change the compression options for a table.

This is useful when migrating to an Exadata machine where more compression options for tables are supported which provides better database performance.

对于数据泵导入，有一个新的impdp命令行选项(以及PL/SQL DBMS_DATAPUMP包的新选项)，它允许用户更改表的压缩选项。

这在迁移到Exadata机器时很有用，在那里，支持表的更多压缩选项可以提供更好的数据库性能。

The TABLE_COMPRESSION_CLAUSE clause of the TRANSFORM parameter allows the table compression characteristics of the tables in an import to be altered on the fly.

转换参数的TABLE_COMPRESSION_CLAUSE子句允许在导入中更改表的表压缩特性。

TRANSFORM=TABLE_COMPRESSION_CLAUSE:[NONE | compression_clause]

The allowable values for the TABLE_COMPRESSION_CLAUSE include the following.

•NONE : The table compression clause is omitted, so the table takes on the compression characteristics of the tablespace.

•NOCOMPRESS : Disables table compression.

•COMPRESS : Enables basic table compression.

•ROW STORE COMPRESS BASIC : Same as COMPRESS.

•ROW STORE COMPRESS BASIC : Same as COMPRESS.

•ROW STORE COMPRESS ADVANCED : Enables advanced compression, also known as OLTP compression.

•COLUMN STORE COMPRESS FOR QUERY : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances.

•COLUMN STORE COMPRESS FOR ARCHIVE : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances.

实验

1)源用户查看表LEO2 没有被压缩

SQL> conn test/test@pdbcndba

Connected.

SQL> select compression from user_tables where table_name='LEO2';

COMPRESS

--------

DISABLED

2)导出test用户数据

[oracle@host1 ~]$ expdp test/test@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log SCHEMAS=test LOGTIME=ALL

Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:10:37 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

04-AUG-17 14:10:38.787: Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log SCHEMAS=test LOGTIME=ALL

04-AUG-17 14:10:39.223: Estimate in progress using BLOCKS method...

04-AUG-17 14:10:40.400: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

04-AUG-17 14:10:40.438: Total estimation using BLOCKS method: 13 MB

04-AUG-17 14:10:40.675: Processing object type SCHEMA_EXPORT/USER

04-AUG-17 14:10:40.732: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

04-AUG-17 14:10:40.751: Processing object type SCHEMA_EXPORT/ROLE_GRANT

04-AUG-17 14:10:40.774: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

04-AUG-17 14:10:40.803: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

04-AUG-17 14:10:41.047: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

04-AUG-17 14:10:45.001: Processing object type SCHEMA_EXPORT/TABLE/TABLE

04-AUG-17 14:10:55.798: Processing object type SCHEMA_EXPORT/TABLE/COMMENT

04-AUG-17 14:10:56.667: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

04-AUG-17 14:10:57.684: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

04-AUG-17 14:10:57.721: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

04-AUG-17 14:10:58.325: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

04-AUG-17 14:10:58.399: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

04-AUG-17 14:11:03.093: . . exported "TEST"."LEO2" 10.36 MB 90936 rows

04-AUG-17 14:11:03.462: Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

04-AUG-17 14:11:03.465: ******************************************************************************

04-AUG-17 14:11:03.468: Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:

04-AUG-17 14:11:03.476: /backup/expdat.dmp

04-AUG-17 14:11:03.504: Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:11:03 2017 elapsed 0 00:00:26

3)impdp remap 新用户test2 并指定压缩方式table_compression_clause:compress 导入

[oracle@host1 ~]$ impdp system/oracle@pdbcndba directory=dpump_dir1 dumpfile=expdat.dmp logfile=impdp.log remap_schema=test:test2 transform=table_compression_clause:compress

Import: Release 12.1.0.2.0 - Production on Fri Aug 4 15:58:02 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@pdbcndba directory=dpump_dir1 dumpfile=expdat.dmp logfile=impdp.log remap_schema=test:test2 transform=table_compression_clause:compress

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TEST2"."LEO2" 10.36 MB 90936 rows

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Aug 4 15:58:16 2017 elapsed 0 00:00:13

4)新用户查看表LEO2 以压缩的方式导入

SQL> conn test2/test@pdbcndba

Connected.

SQL> select compression from user_tables where table_name='LEO2';

COMPRESS

--------

ENABLED