<<Back to Oracle DATAPUMP Main Page
Using Query Parameter with Datapump expdp or impdp
PurposeAllows you to specify a query clause that is used to filter the data that gets exported or imported
Syntax and Description
QUERY = [schema.][table_name:] query_clause
If the NETWORK_LINK parameter is specified along with the QUERY parameter, then any objects specified in the query_clause that are on the remote (source) node must be explicitly qualified with the NETWORK_LINK value. Otherwise, Data Pump assumes that the object is on the local (target) node. If it is not, then an error is returned and the import of the table from the remote (source) system fails.
For Example
QUERY=(hr.employees:"WHERE last_name IN(SELECT last_name
FROM hr.employees@dblink1)")
The QUERY parameter cannot be used with the following parameters:
– CONTENT=METADATA_ONLY
– ESTIMATE_ONLY
– TRANSPORT_TABLESPACES
Expdp QUERY
$ cat exp.par
DIRECTORY=EXP
DUMPFILE=EXP.DMP
LOGFILE=EXP.LOG
TABLES=TEST.EMP
QUERY=EMP:"WHERE id > 3"
REUSE_DUMPFILES=Y
$ expdp test@PDB01 parfile=exp.par
Export: Release 12.2.0.1.0 - Production on Mon Nov 12 14:52:23 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "TEST"."SYS_EXPORT_TABLE_01": test/********@PDB01 parfile=exp.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."EMP" 5.492 KB 2 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/u01/dbatst1/stage/EXP.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 12 14:53:32 2018 elapsed 0 00:00:56
Impdp QUERY
$ cat imp.par
DIRECTORY=EXP
DUMPFILE=EXP.DMP
REMAP_TABLE=TEST.EMP:EMP1
LOGFILE=IMP.LOG
QUERY=EMP:"WHERE id > 4"
$ impdp test@PDB01 parfile=imp.par
Import: Release 12.2.0.1.0 - Production on Mon Nov 12 15:02:40 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/********@PDB01 parfile=imp.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."EMP1" 5.492 KB 1 out of 2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 12 15:11:04 2018 elapsed 0 00:00:05
us import export data A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post.
ReplyDelete