Use data pump to export query results in Oracle (i) query function of data pump

On the itpub saw someone raise this question, can use the data pump to export a query result. In fact, the data pump really has this function.

Establish a simple test environment:

sql> CREATE TABLE T1

2 (ID number, NAME VARCHAR2 (30));

Table created.

Sql> INSERT into T1

2 SELECT rownum, Tname

3 from TAB;

Created rows.

sql> CREATE TABLE T2

2 (ID number, NAME VARCHAR2 (30));

Table created.

Sql> INSERT into T2

2 VALUES (1, ' A ');

1 row created.

Sql> INSERT into T2

2 VALUES (2, ' B ');

1 row created.

Sql> COMMIT;

Commit complete.

If you want to export T1 and T2 query results and meet two criteria, you can consider using the Data pump query feature.

The first condition is that all the fields of the query are from the same table;

The second condition is that other tables are associated with the current table field as unique, in other words, when the current and other tables are associated, they do not produce duplicate records.

Sql> SELECT t1.* from T1, T2

2 WHERE t1.id = t2.id;

ID NAME

---------- ------------------------------

1 cp_test_t

2 CELL

This column more highlights: http://www.bianceng.cn/database/Oracle/

For example, the above query results can be achieved through the data pump query parameters, first to query deformation, into in or exists form:

Sql> SELECT * from T1

2 WHERE ID in

3 (SELECT t2.id from T2 WHERE t2.id = t1.id);

ID NAME

---------- ------------------------------

1 cp_test_t

2 CELL

The following can be exported:

[oracle@yans1 ~]$ expdp test/test dumpfile=d_output:t1.dp tables=t1 query= ' T1: ' where ID in (SELECT ID from T2 WHERE t2.id = ID) "'

Export:release10.2.0.3.0-64bit Production on Sunday, 13 June, 2010 19:52:28

Copyright (c) 2003, +, Oracle. All rights reserved.

Connected to:oracle database10genterprise Edition release10.2.0.3.0-64bit Production

With the partitioning, OLAP and Data Mining options

Starting "TEST". " Sys_export_table_01 ": test/******** dumpfile=d_output:t1.dp tables=t1 query=t1:" Where ID in (SELECT ID from T2 WHERE t2.i D = ID) "

Estimate in progress using BLOCKS method ...

Processing Object Type Table_export/table/table_data

Total estimation using BLOCKS method:128 KB

Processing Object Type Table_export/table/table

. . Exported "TEST". " T1 "5.234 KB 2 rows

Master table "TEST". " Sys_export_table_01 "Successfully loaded/unloaded

******************************************************************************

Dump file set for TEST. SYS_EXPORT_TABLE_01 is:

/home/oracle/t1.dp

Job "TEST". " Sys_export_table_01 "successfully completed at 19:52:32

This approach is simpler, but the biggest drawback is that the restrictions are too restrictive, especially if all the fields are entirely from a single table, and most of the cases cannot meet this condition.