How to export only some rows of a table using oracle data pump option

Oracle data pump (expdp/impdp) is a very known utility for exporting data from tables. But the utility has a variety of options available and it can also be used not only to export the entire table data but also to export restricted rows from a table, that are defined using a where clause for example.
Let’s say that we want to export the rows of table HRP001 that meets the following conditions:

OTYPE = Q
RSIGN = B
RELAT = 032
SCLAS = P

we create a parameter file tab_where.par that has the content:

tables=HRP1001 
#table name to be exported from
directory=tmp_dmpdirs
#oracle directory where the export file will be located
DUMPFILE=exp_HRP1001_part.dmp
#file name that contains the data exported
logfile=exp_HRP1001_part.log
#log file of the 
query=HRP1001:"where OTYPE='Q' and RSIGN='B' and RELAT='032' and SCLAS = 'P'"
#the query that defines the data required from the table
COMPRESSION=all
#compression option for the dump file

Then we execute the expdp command that calls the parameter file that we just defined:

expdp user/passwd parfile=tab_where.par

Export: Release 12.1.0.2.0 – Production on Tue Nov 14 15:43:51 2017

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

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: user/******** parfile=tab_where.par
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.285 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported “HRP1001” 14.47 MB 584555 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oracle/exp_tbls/exp_HRP1001_part.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Nov 14 15:44:07 2017 elapsed 0 00:00:16

To import the data from this table we use the impdp utility:

impdp user/passwd parfile=tab_where.par

Leave a Reply

Your email address will not be published. Required fields are marked *