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 
Well done.
Very useful tutorial, good explained.
Thank you Azar, wish you good luck and teach us with your tutorials. 🙂
You’re Welcome Brother 🙂
Mohamed Azar.
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.
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.