Oracle 10g 11g Data Pump EXPDP SAMPLE Parameter Option

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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google