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
Related