How to Export a Table With Selected Columns

If you want to migrate only a part of a specific table, there are two possible goals that you may want to achieve:

• To migrate a table with some rows. (select vertically)
• To migrate a table with selected columns. (select horizontally)

For Oracle Data Pump, the first goal is easy to achieve by adding a parameter QUERY in the export command to filter desired rows. The parameter is to support WHERE clause which can add predicates to include or exclude data selectively.

The second goal could be a problem. Since data pump does not have any parameter to support filtering desired columns, there is no way to do it without some workarounds.

The key to workaround is to create an entity from the original table selectively, the entity could be a materialized view or a table with a different name. In this case, I choose to create a materialized view rather than a table.

Describe the table SH.SALES that we want to export.
SQL> desc sh.sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)
Create a materialized view with selected columns.
SQL> create materialized view sh.sales_view as select prod_id, cust_id, quantity_sold, amount_sold from sh.sales;

Materialized view created.
Describe the newly created materialized view.
SQL> desc sh.sales_view;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)
Export the materialized view.
[oracle@primary01 ~]$ expdp "/ as sysdba" tables=sh.sales_view dumpfile=exp_sales.dmp

Export: Release 11.2.0.1.0 - Production on Tue Jan 29 14:28:45 2013

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 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=sh.sales_view dumpfile=exp_sales.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 21 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
. . exported "SH"."SALES_VIEW"                           16.48 MB  918843 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/product/11.2.0/db_1/rdbms/log/exp_sales.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:29:46
There's 16.48MB or 918843 rows exported successfully.