Skip to content

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_ONLY
  • ESTIMATE_ONLY
  • TRANSPORT_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                    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.

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>
About these ads
4 Comments Post a comment
  1. Well done.
    Very useful tutorial, good explained.
    Thank you Azar, wish you good luck and teach us with your tutorials. :)

    November 10, 2010
    • You’re Welcome Brother :)

      Mohamed Azar.

      November 10, 2010
  2. raygg #

    Microsoft: Windows Server 2003
    Oracle: Web App Server
    -Security, hacker detection & forensics
    -Virus/Spyware discussion
    Programmers
    AJAX
    C
    Microsoft: Access Other topics
    Oracle: Enterprise Developer Suite
    Oracle: Oracle release – 9i
    Oracle: Oracle release 10g
    Oracle: Oracle release 11g
    Oracle: Reports
    Perl
    Sybase: PowerDesigner
    Sylvain Faust, Intl (SFI): SQL-Programmer
    Server Rack
    -General printer discussion
    HP (Compaq) servers
    HP Printers
    Data Transmission
    Remote access issues
    Wireless Data and Devices
    Wiring Closet
    Cabling
    Desktop Hardware and Hand-held devices
    PC hardware – Video issues
    Desktop Software
    Linux (client/desktop)
    Microsoft: Windows 2000 Professional
    Microsoft: Windows 7
    Microsoft: Windows Vista
    Microsoft: Windows XP Pro
    Application Management
    Application Deployment
    Project and Data Management
    Data mining general discussion
    Data warehousing general discussion
    Feedback
    “…This site is awesome!…Things I have been trying to figure out for weeks, I got the answer in hours!…”
    More…
    Geography
    Where in the world do Tek-Tips members come from?
    Click Here To Find Out!

    Home > Forums > Programmers > DBMS Packages > Oracle: Oracle release 10g Forum
    why did this DW datapump load fail?
    thread1177-1629541
    Forum Search FAQs Links Jobs Whitepapers Forum MVPs
    Read
    New Posts Reply To
    This Thread Start A
    New Thread e-mail
    E-mail It print
    Print Next
    Thread
    New Postraygg (TechnicalUser)
    30 Nov 10 10:39
    I am new to loading data warehouses databases.

    I hung after 6 hours loading (after 2.1 billion rows) a large fact table (2.4 billion rows) and got the error message below. At the time of the hang the tablespace dbf file was 67,108,848KB and there is 164GB of free space on the drive which is being managed with RAID5.

    statement in resumable session ‘RAYGG.WEATHER01_NUM10_FACT_ALL.1′ was suspended due to
    ORA-01653: unable to extend table METARDW.WEATHER2 by 4096 in tablespace FACT_TS

    This is running on windows 32bit 2003 server with 3gb RAM. This is a brand new use of this server and hard drive – it was just wiped and reimaged before I put this databse on the machine so defragmentation is not an issue.

    There were two tables specified in the parfile. After this table hung the impdp loaded the second smaller table as shown below in the impdp log.

    I am stumped – there is plenty of room left on the drive, the next table loaded ok, there is no space limit in the table definition, there is no space limitation on tablespace.

    ____________________________
    Here is the table desc
    ____________________________
    DROP TABLE WEATHER2 CASCADE CONSTRAINTS;
    CREATE TABLE WEATHER2
    ( DIR CHAR(3 BYTE),
    SPD INTEGER,
    (a bunch of other rows)
    )
    TABLESPACE FACT_TS
    PCTFREE 10 PCTUSED 75 INITRANS 1 MAXTRANS 255 LOGGING
    STORAGE(PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1);
    ____________________________________________
    here is the tablespace create statments
    ____________________________________________
    CREATE TABLESPACE FACT_TS DATAFILE ‘E:\app\metardw\oradata\metardw\DATAFILE\FACT_TS.DBF’
    SIZE 512M REUSE AUTOEXTEND ON NEXT 512M
    EXTENT MANAGEMENT LOCAL
    BLOCKSIZE 16K
    AUTOALLOCATE
    DEFAULT COMPRESS FOR OLTP;

    _____________________________
    This is the log from impdp
    _____________________________
    C:\>impdp raygg/raygg parfile=C:\Oracle\DBAScripts\imp_WEATHER01_num10_FACT_ALL.
    PAR

    Import: Release 11.2.0.1.0 – Production on Mon Nov 29 22:15:52 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 – Produc
    tion
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table “RAYGG”.”WEATHER01_NUM10_FACT_ALL” successfully loaded/unloaded
    Starting “RAYGG”.”WEATHER01_NUM10_FACT_ALL”: raygg/******** parfile=C:\Oracle\D
    BAScripts\imp_WEATHER01_num10_FACT_ALL.PAR
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    ORA-39171: Job is experiencing a resumable wait.
    ORA-01653: unable to extend table METARDW.WEATHER2 by 4096 in tablespace FACT_TS

    ORA-39171: Job is experiencing a resumable wait.
    ORA-01653: unable to extend table METARDW.WEATHER2 by 4096 in tablespace FACT_TS

    <<>>

    ORA-39171: Job is experiencing a resumable wait.
    ORA-01653: unable to extend table METARDW.WEATHER2 by 4096 in tablespace FACT_TS

    ORA-31693: Table data object “METARDW”.”WEATHER2″ failed to load/unload and is b
    eing skipped due to error:
    ORA-02354: error in exporting/importing data
    ORA-30032: the suspended (resumable) statement has timed out
    ORA-01653: unable to extend table METARDW.WEATHER2 by 4096 in tablespace FACT_TS

    . . imported “METARDW”.”WEATHER3″ 128.2 KB 2189 rows
    Job “RAYGG”.”WEATHER01_NUM10_FACT_ALL” completed with 24 error(s) at 07:53:37

    I was testing the loading with a smaller volume of the dmp file using query statements that limited the number of rows loaded by table using the TABLES and QUERY options as

    tables=dwuser.hc_dim
    QUERY=hc_dim:”WHERE rownum 19999 and 0 rows were loaded.

    So even after I allocate another datafile for the tablespace (not sure what else to do) I do not know how to restart the load – I only can assume I must restart from the beginning. The only other alternative I can think of would be to create a unique primary key and rerun the load anticipating the load would refuse loading all duplicate keys but then would resume loading after the load finds the end of the previously loaded rows. But I don’t know if this would take almost as long as the original load. The big dif is that it skips all those writes of the tablespace.

    Any help would be appreciated.

    November 30, 2010
  3. Sorry for Late replay….My suggestion, you should check your datafiles size before you import large data whether free space avilable or not and also check your datafiles maximum size, and Add another datafiles for existing tablespace, and also Other option you can create big file tablespace (It contains only one datafile , you cannot add another datafile for it). Because, you can import large data within a single datafile….

    And also you asking How do i import exisinting rows avilable in table, you can use “TABLE_EXISTS_ACTION” parameter for import (datapump) command…….

    Mohamed Azar.

    December 1, 2010

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 135 other followers

%d bloggers like this: