在select语句中,我们的确可以使用sample语句去实现对应的一些功能。借助Datapump,我们也可以实现采样方式的数据表备份处理。
1、环境说明
我们选择Oracle 11gR2进行测试实验,具体版本为11.2.0.4。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
创建数据表SM_TEST。
SQL> create table scott.sm_test as select * from dba_objects;
Table created
SQL> select count(*) from scott.sm_test;
COUNT(*)
----------
133391
2、测试实验
对Expdp的Sample参数,官方的解释是很少的。
SAMPLE
Percentage of data to be exported.
Datapump是可以工作在三个模式下,全库模式、Schema模式和表模式。针对三种模式,我们都可以使用Sample参数来进行设置。下面通过最简单的比例值指定来设置采样比例。
[oracle@localhost ~]$ expdp \"/ as sysdba\" dumpfile=TEST_1.dmp tables=scott.sm_test sample=50
Export: Release 11.2.0.4.0 - Production on Mon Nov 23 13:50:02 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=TEST_1.dmp tables=scott.sm_test sample=50
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."SM_TEST" 6.402 MB 66587 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/admin/sicsdb/dpdump/TEST_1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 23 13:50:11 2015 elapsed 0 00:00:07
指定导出的数据表名称的表模式中,我们通过sample的百分比参数,来确定特定表的内容。从导出数据6万左右的情况,正好是13万总数据的50%,是合理的结果。
如果运行在schema工作模式下,直接指定比例份额,结果如何呢?
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
[oracle@localhost ~]$ expdp \"/ as sysdba\" dumpfile=TEST_2.dmp schemas=scott sample=50
Export: Release 11.2.0.4.0 - Production on Mon Nov 23 13:51:31 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" dumpfile=TEST_2.dmp schemas=scott sample=50
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8.093 MB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing