Data pump sample parameter

Data pump sample parameter can be used for creating a subset of data by table or schema. For example, if sample=10, only 10% of data is exported by table or by schema. It is great if you want to create a testing or development database from a production database and scale it down for various reasons. However, there is one big catch for doing so. The referential constraint is not enforced by sampling. So when you do import (dpimp) of the sampled data dump, you will get ORA-02298 error if you have foreign key constraints. Here is an example of log file for the data pump import:

;;;

Import: Release 10.2.0.2.0 - 64bit Production on Friday, 17 July, 2009 16:29:45

Copyright (c) 2003, 2005, Oracle. All rights reserved.

;;;

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=WTC_EXP_DIR DUMPFILE=dbpump_sample10_exp.dmp remap_schema=dbpump:dbpump2 logfile=dbpump_sample10_imp.log

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"DBPUMP2" already exists

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/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "DBPUMP2"."CHILD" 2.132 MB 101427 rows

. . imported "DBPUMP2"."PARENT" 20.92 KB 979 rows

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-02298: cannot validate (DBPUMP2.SYS_C0052496) - parent keys not found

Failing sql is:

ALTER TABLE "DBPUMP2"."CHILD" ADD FOREIGN KEY ("P_ID") REFERENCES "DBPUMP2"."PARENT" ("P_ID") ENABLE

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 16:29:52

Looks like I still need my PLSQL package for doing data dump of subset of data from production database.