Oracle 10g 11g Data Pump EXPDP SAMPLE Parameter Option

advertisements

_____________________________________________________________________________________________________________________

SAMPLE parameter is used for exporting the sample number of rows. The parameter value mentioned will be considered as the sample percentage. Suppose if you don’t want the entire data from a table or schema and just need few numbers of records you can use this sample parameter of expdp utility. See the example below.

The table object_list has 24718848 and I am going to export only 10% of data for the table. In the dump file it has only 2472969 rows.

SQL> select count(*) from object_list;

COUNT(*)

----------

24718848

expdp directory=EXPDIR dumpfile=object_list.dmp logfile=object_list.log tables=scott.object_list sample=10

Export: Release 11.2.0.2.0 - Production on Wed Jan 11 01:02:39 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=EXPDIR dumpfile=object_list.dmp logfile=object_list.log tables=scott.object_list sample=10

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 274.2 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."OBJECT_LIST" 235.2 MB 2472969 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

/tmp/object_list.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 01:03:36

Datapump Tuning Features