Oracle 12c 新特性 --- 数据泵在导入时更改表压缩
程序员文章站
2024-03-14 13:41:52
...
概念
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/[email protected]
Connected.
SQL> select compression from user_tables where table_name='LEO2';
COMPRESS
--------
DISABLED
2）导出test用户数据
[[email protected] ~]$ expdp test/[email protected] 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 导入
[[email protected] ~]$ impdp system/[email protected] 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/[email protected]
Connected.
SQL> select compression from user_tables where table_name='LEO2';
COMPRESS
--------
ENABLED参考链接：
http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT002