<<Back to Oracle DATAPUMP Main Page
Using EXPDP SAMPLE Parameter
PurposeAllows you to specify a percentage of the data rows to be sampled and unloaded from the database.
Syntax and DescriptionSAMPLE=[[schema_name.]table_name:]sample_percent
This parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported.
If you specify a schema, then you must also specify a table. However, you can specify a table without specifying a schema; the current user will be assumed. If no table is specified, then the sample_percent value applies to the entire export job.
Examples:
In the following example, 50% of the TEST.EMP table will be exported together with all other objects of TEST SCHEMA
$ cat exp.par
DIRECTORY=EXP
DUMPFILE=EXP.DMP
LOGFILE=EXP.LOG
SAMPLE=TEST.EMP:50
REUSE_DUMPFILES=Y
$ expdp test@PDB01 parfile=exp.par
Export: Release 12.2.0.1.0 - Production on Tue Nov 13 09:31:51 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@PDB01 parfile=exp.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP1" 5.476 KB 1 rows
. . exported "TEST"."TEST" 5.476 KB 1 rows
. . exported "TEST"."EMP" 5.492 KB 2 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/u01/dbatst1/stage/EXP.DMP
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 13 09:33:06 2018 elapsed 0 00:01:13
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@PDB01 parfile=exp.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP1" 5.476 KB 1 rows
. . exported "TEST"."TEST" 5.476 KB 1 rows
. . exported "TEST"."EMP" 5.492 KB 2 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/u01/dbatst1/stage/EXP.DMP
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 13 09:33:06 2018 elapsed 0 00:01:13
In this Example Only 50% of TABLE TEST.EMP will be exported
$ cat exp.par
DIRECTORY=EXP
DUMPFILE=EXP.DMP
LOGFILE=EXP.LOG
TABLES=TEST.EMP
SAMPLE=50
REUSE_DUMPFILES=Y
$ expdp test@PDB01 parfile=exp.par
Export: Release 12.2.0.1.0 - Production on Tue Nov 13 09:36:49 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "TEST"."SYS_EXPORT_TABLE_01": test/********@PDB01 parfile=exp.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."EMP" 5.492 KB 2 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/u01/dbatst1/stage/EXP.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 13 09:37:17 2018 elapsed 0 00:00:24
Comments
Post a Comment