Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction:
Most of time, you need to import just some rows from your logical backup for test purpose and from production to test environment.
Many options can be used;

1- CTAS using database link.
2- Materialized view using a database link.
3- Using the query option of data pump utility.

In this post, we will see how to use the query option when importing data via Data pump utility;

Example:

[oracle@wissem ~]$ rlsqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 1 12:22:44 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password: 

SQL> create table wissem.test_imp (ID number);

Table created.

SQL> begin
  2  for i in 1..100 loop
  3  insert into wissem.test_imp values (i);
  4  end loop;
  5  commit;
  6  end;
  7  / 

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@wissem ~]$ expdp wissem tables=test_imp directory=DATA_PUMP_DIR dumpfile=test_imp.dmp logfile=test_imp.log

Export: Release 11.2.0.1.0 - Production on Wed Jun 1 12:29:53 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "WISSEM"."SYS_EXPORT_TABLE_01":  wissem/******** tables=test_imp directory=DATA_PUMP_DIR dumpfile=test_imp.dmp logfile=test_imp.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 "WISSEM"."TEST_IMP"                         5.695 KB     100 rows
Master table "WISSEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for WISSEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/app/oracle/admin/ORAWISS/dpdump/test_imp.dmp
Job "WISSEM"."SYS_EXPORT_TABLE_01" successfully completed at 12:30:20

[oracle@wissem ~]$ rlsqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 1 12:31:12 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table wissem.test_imp;

Table dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@wissem ~]$ impdp wissem query=TEST_IMP:\"where ROWNUM\<10\"   directory=DATA_PUMP_DIR dumpfile=test_imp.dmp logfile=test_imp.log

Import: Release 11.2.0.1.0 - Production on Wed Jun 1 12:37:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "WISSEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "WISSEM"."SYS_IMPORT_FULL_01":  wissem/******** query=TEST_IMP:"where ROWNUM<10" directory=DATA_PUMP_DIR dumpfile=test_imp.dmp logfile=test_imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "WISSEM"."TEST_IMP"                         5.695 KB       9 out of 100 rows
Job "WISSEM"."SYS_IMPORT_FULL_01" successfully completed at 12:38:06

[oracle@wissem ~]$ 

[oracle@wissem ~]$ rlsqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 1 12:40:06 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from wissem.test_imp;

  COUNT(*)
----------
	 9

SQL>

Hope it helps,
Wissem