How do I filter data when doing an import or export

When you need a subset of data for testing, it is very useful to be able to specify the filtering in the export or import. That's a lot easier than copying a full dataset and waiting while it deletes most if it.

This is very easy using the Datapump tools in 10g. Both expdb and impdb have the facility to accept a filter criteria. As a quick example:

Step 1 - Create the table. It has 16,000 rows (roughly)


>sqlplus gary/gary


SQL*Plus: Release 10.2.0.1.0 - Production 
SQL> create table bunch_of_data
  2  as select owner, table_name, column_name, data_type
  3  from all_tab_columns;


SQL> select count(*) from bunch_of_data;


  COUNT(*)
----------
     16346

Step 2. Export a subset of the data

>expdp userid=gary/gary dumpfile=bunch.dmp directory=ext_tables 
   tables=BUNCH_OF_DATA query='WHERE DATA_TYPE=''DATE'''

Export: Release 10.2.0.1.0 - Production on Friday, 04 March, 2011 17:45:17


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


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "GARY"."SYS_EXPORT_TABLE_01":  
 userid=gary/******** dumpfile=bunch.dmp 
 directory=ext_tables tables=BUNCH_OF_DATA 
 query='WHERE DATA_TYPE=''DATE'''


Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "GARY"."BUNCH_OF_DATA"                      30.10 KB     513 rows
Master table "GARY"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GARY.SYS_EXPORT_TABLE_01 is:
  D:\APPS\AR122007\BUNCH.DMP
Job "GARY"."SYS_EXPORT_TABLE_01" successfully completed at 17:46:00

Step 3. Import a subset of that export (into another schema in this test)

>impdp userid=gary_dba/gary dumpfile=bunch.dmp
        directory=ext_tables 
        tables=BUNCH_OF_DATA 
        query='WHERE OWNER = ''SYSTEM'''
        remap_schema=gary:gary_dba

Import: Release 10.2.0.1.0 - Production on Friday, 04 March, 2011 17:46:02


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


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "GARY_DBA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "GARY_DBA"."SYS_IMPORT_TABLE_01":  
  userid=gary_dba/******** dumpfile=bunch.dmp directory=ext_tables 
  tables=BUNCH_OF_DATA 
  query='WHERE OWNER = ''SYSTEM''' remap_schema=gary:gary_dba
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "GARY_DBA"."BUNCH_OF_DATA"            30.10 KB       2 out of 513 rows
Job "GARY_DBA"."SYS_IMPORT_TABLE_01" successfully completed at 17:46:11

Partial export and partial import all complete.