10G、11G expdp的compression参数的区别

最新推荐文章于 2024-03-18 12:51:06 发布

cqug68627

最新推荐文章于 2024-03-18 12:51:06 发布

阅读量422

点赞数

文章标签：数据库

10g的expdp的一个参数compression是不提供对表数据的压缩的

只是对metadata的压缩也就是定义的压缩说白了就是没鸟用的

今天刚刚装了个11g 就测试了下expdp的compression

首先expdp help=y看看 11g 和10g有什么不同

[@more@]

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION Reduce size of dumpfile contents where valid keyword.
values are: ALL, (METADATA_ONLY), DATA_ONLY and NONE.
CONTENT Specifies data to unload where the valid keyword
values are: (ALL), DATA_ONLY, and METADATA_ONLY.
DATA_OPTIONS Data layer flags where the only valid value is:
XML_CLOBS-write XML datatype in CLOB format
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION Encrypt part or all of the dump file where valid keyword
values are: ALL, DATA_ONLY, METADATA_ONLY,
ENCRYPTED_COLUMNS_ONLY, or NONE.
ENCRYPTION_ALGORITHM Specify how encryption should be done where valid
keyword values are: (AES128), AES192, and AES256.
ENCRYPTION_MODE Method of generating encryption key where valid keyword
values are: DUAL, PASSWORD, and (TRANSPARENT).
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
ESTIMATE Calculate job estimates where the valid keyword
values are: (BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
REMAP_DATA Specify a data conversion function,
e.g. REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REUSE_DUMPFILES Overwrite destination dump file if it exists (N).
SAMPLE Percentage of data to be exported;
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORTABLE Specify whether transportable method can be used where
valid keyword values are: ALWAYS, (NEVER).
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
REUSE_DUMPFILES Overwrite destination dump file if it exists (N).
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

这里只讨论compression 其他的后续再研究呵呵

我们可以发现 11g的compression比10g 多了2个参数值增加了all 和data_only

data_only是什么意思呢？其实就是提供对表数据的压缩下面看测试

[oracle@red-db1 bin]$ expdp system/huanhuan dumpfile=1210_02.dmp tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx02.log

Export: Release 11.1.0.6.0 - Production on Saturday, 12 December, 2009 0:29:19

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=1210_02.dmp tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx02.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ROGER"."B1" 10.44 KB 2 rows
. . exported "ROGER"."B2" 10.52 KB 3 rows
. . exported "ROGER"."B3" 10.59 KB 4 rows
. . exported "ROGER"."B4" 10.66 KB 5 rows
. . exported "ROGER"."B5" 10.74 KB 6 rows
. . exported "ROGER"."B6" 10.81 KB 7 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oracle/product/admin/alex/dpdump/1210_02.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:29:42

[oracle@red-db1 bin]$ expdp system/huanhuan dumpfile=1210_03.dmp compression=none tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx03.log

Export: Release 11.1.0.6.0 - Production on Saturday, 12 December, 2009 0:30:39

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=1210_03.dmp compression=none tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx03.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ROGER"."B1" 10.44 KB 2 rows
. . exported "ROGER"."B2" 10.52 KB 3 rows
. . exported "ROGER"."B3" 10.59 KB 4 rows
. . exported "ROGER"."B4" 10.66 KB 5 rows
. . exported "ROGER"."B5" 10.74 KB 6 rows
. . exported "ROGER"."B6" 10.81 KB 7 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oracle/product/admin/alex/dpdump/1210_03.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:31:02

[oracle@red-db1 bin]$ expdp system/huanhuan dumpfile=1210_04.dmp compression=metadata_only tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx04.log

Export: Release 11.1.0.6.0 - Production on Saturday, 12 December, 2009 0:31:40

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=1210_04.dmp compression=metadata_only tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx04.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ROGER"."B1" 10.44 KB 2 rows
. . exported "ROGER"."B2" 10.52 KB 3 rows
. . exported "ROGER"."B3" 10.59 KB 4 rows
. . exported "ROGER"."B4" 10.66 KB 5 rows
. . exported "ROGER"."B5" 10.74 KB 6 rows
. . exported "ROGER"."B6" 10.81 KB 7 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oracle/product/admin/alex/dpdump/1210_04.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:32:03

[oracle@red-db1 bin]$ expdp system/huanhuan dumpfile=1210_05.dmp compression=data_only tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx05.log

Export: Release 11.1.0.6.0 - Production on Saturday, 12 December, 2009 0:32:45

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=1210_05.dmp compression=data_only tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx05.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ROGER"."B1" 5.765 KB 2 rows
. . exported "ROGER"."B2" 5.773 KB 3 rows
. . exported "ROGER"."B3" 5.820 KB 4 rows
. . exported "ROGER"."B4" 5.765 KB 5 rows
. . exported "ROGER"."B5" 5.875 KB 6 rows
. . exported "ROGER"."B6" 5.898 KB 7 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oracle/product/admin/alex/dpdump/1210_05.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:33:09

[oracle@red-db1 bin]$ expdp system/huanhuan dumpfile=1210_06.dmp compression=all tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx06.log

Export: Release 11.1.0.6.0 - Production on Saturday, 12 December, 2009 0:33:44

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=1210_06.dmp compression=all tables=roger.b1,roger.b2,roger.b3,roger.b4,roger.b5,roger.b6 logfile=lizx06.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ROGER"."B1" 5.765 KB 2 rows
. . exported "ROGER"."B2" 5.773 KB 3 rows
. . exported "ROGER"."B3" 5.820 KB 4 rows
. . exported "ROGER"."B4" 5.765 KB 5 rows
. . exported "ROGER"."B5" 5.875 KB 6 rows
. . exported "ROGER"."B6" 5.898 KB 7 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oracle/product/admin/alex/dpdump/1210_06.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:34:08

[oracle@red-db1 bin]$

-rw-r----- 1 oracle oinstall 237568 12-12 00:29 1210_02.dmp
-rw-r----- 1 oracle oinstall 237568 12-12 00:31 1210_03.dmp
-rw-r----- 1 oracle oinstall 237568 12-12 00:32 1210_04.dmp
-rw-r----- 1 oracle oinstall 81920 12-12 00:33 1210_05.dmp
-rw-r----- 1 oracle oinstall 81920 12-12 00:34 1210_06.dmp

我们可以看到默认是不进行压缩的对于表数据而言 all和data_only基本一样。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/670493/viewspace-1029593/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/670493/viewspace-1029593/