Datapump query parameter
This post show you about Datapump query parameter used in impdp and expdp.
For Export :
Default: none
Purpose
Enables you to filter the data that is exported by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table.
Syntax and Description
QUERY = [schema.][table_name:] query_clause
The query_clause is typically a WHERE clause for fine-grained row selection, but could be any SQL clause. For example, an ORDER BY clause could be used to speed up a migration from a heap-organized table to an index-organized table. If a [schema.]table_name is not supplied, the query is applied to (and must be valid for) all tables in the export job. A table-specific query overrides a query applied to all tables.
When the query is to be applied to a specific table, a colon must separate the table name from the query clause. More than one table-specific query can be specified, but only one can be specified per table. Oracle highly recommends that you place QUERY specifications in a parameter file; otherwise, you might have to use operating system-specific escape characters on the command line before each quotation mark. To specify a schema other than your own in a table-specific query, you need the EXP_FULL_DATABASE role.
Restrictions
The QUERY parameter cannot be used in conjunction with the following parameters:
CONTENT=METADATA_ONLYESTIMATE_ONLYTRANSPORT_TABLESPACES
Example For Export :
SQL> create table empquery as select * from scott.emp; Table created. SQL> select * from empquery; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
Here I wrote some export commands in *.par file method”
See below open notepad and write commands here like below
tables=empquery query=empquery:"WHERE deptno > 20" nologfile=y directory=data_pump_dir dumpfile=queryme.dmp and save as *.par file.
C:\Users\mazar>expdp testnet/testnet parfile=C:\app\oracle\mazar\admin\azardb\dpdump\querytest.par Export: Release 11.2.0.1.0 - Production on Tue Nov 9 14:49:16 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "TESTNET"."SYS_EXPORT_TABLE_05": testnet/******** parfile=C:\app\oracle\mazar\admin\azardb\dpdump\querytest.par Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "TESTNET"."EMPQUERY" 8.273 KB 6 rows Master table "TESTNET"."SYS_EXPORT_TABLE_05" successfully loaded/unloaded ****************************************************************************** Dump file set for TESTNET.SYS_EXPORT_TABLE_05 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\QUERYME.DMP Job "TESTNET"."SYS_EXPORT_TABLE_05" successfully completed at 14:49:40
After exported , I just dropped table and import that dump file what inside have for test purpose.
SQL> drop table empquery; Table dropped. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\mazar>impdp testnet/testnet tables=empquery directory=data_pump_dir dumpfile=queryme.dmp logfile=queryme.log Import: Release 11.2.0.1.0 - Production on Tue Nov 9 14:52:26 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "TESTNET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "TESTNET"."SYS_IMPORT_TABLE_01": testnet/******** tables=empquery directory=data_pump_dir dumpfile=queryme.dmp logfile=queryme.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TESTNET"."EMPQUERY" 8.273 KB 6 rows Job "TESTNET"."SYS_IMPORT_TABLE_01" successfully completed at 14:52:32
and then connect testnet user
SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 6 rows selected.
You can see only DEPTNO=30 ROWS avilable.
For Import :
At Same Table again i was created , and select the execute command what i show in first steps
SQL> create table empquery as select * from scott.emp;
Table created.
SQL> select * from empquery;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\mazar>expdp testnet/testnet tables=empquery directory=data_pump_dir dumpfile=queryexport.dmp logfile=queryexport.log Export: Release 11.2.0.1.0 - Production on Tue Nov 9 14:55:59 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "TESTNET"."SYS_EXPORT_TABLE_05": testnet/******** tables=empquery directory=data_pump_dir dumpfile=queryexport.dmp logfile=queryexport.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "TESTNET"."EMPQUERY" 8.585 KB 14 rows Master table "TESTNET"."SYS_EXPORT_TABLE_05" successfully loaded/unloaded ****************************************************************************** Dump file set for TESTNET.SYS_EXPORT_TABLE_05 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\QUERYEXPORT.DMP Job "TESTNET"."SYS_EXPORT_TABLE_05" successfully completed at 14:56:20
C:\Users\mazar>sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 9 14:57:59 2010 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: testnet/testnet Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop table empquery; Table dropped. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Here I create *.par file like below
tables=empquery query=empquery:"WHERE deptno <= 20" nologfile=y directory=data_pump_dir dumpfile=queryexport.dmp and save as *.par file.
C:\Users\mazar>impdp testnet/testnet tables=empquery parfile=D:\backup\sqlloader\queryimp.par Import: Release 11.2.0.1.0 - Production on Tue Nov 9 15:00:38 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "TESTNET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "TESTNET"."SYS_IMPORT_TABLE_01": testnet/******** tables=empquery parfile=D:\backup\sqlloader\queryimp.par Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TESTNET"."EMPQUERY" 8.585 KB 8 out of 14 rows Job "TESTNET"."SYS_IMPORT_TABLE_01" successfully completed at 15:00:58
C:\Users\mazar>sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 9 15:01:23 2010 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: testnet/testnet Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set lines 500 SQL> select * from empquery; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 8 rows selected. SQL>
DBA 