Pages

Wednesday 7 December 2011

Query level export using expdp utility





Today I used a new feature in oracle which I never used till now in my dba career that is query level export of a table using expdp utility. There is a table gds_salesseries_cd is one of the columns in that table. I need the rows with column series_cd value less than 9.8(series_cd>9.8) to be exported. I used the query level export for the data. The process is as follows:

Parfile
--------
tables = rtd.gds_sales
dumpfile= expdp.gds.qry.dmp
logfile= expdp.gds.qry.log
directory=DPUMP
query= rtd.gds_sales:"WHERE SERIES_CD<9.8"

Note: In the first parameter tables = rtd.gds_sales, rtd is the owner of the table. Since we do the export using system user, we specify the schema name before the table.

[oracle@rac-a exp]$ expdp parfile=expdp.gds.qry.par

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 19 November, 2010 2:28:21

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

Username: system
Password: ******

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=/home/oracle/admin/ACDB2/exp/expdp.gds.qry.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "RTD"."GDS_PRODUCT"                         29.16 MB   46963 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/admin/ACDB2/exp/expdp.gds.qry.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 02:28:41



> we need to specify the table name in the above format which is in the parfile, if not the below error arises.


query="where series_cd<9.8"
ORA-31658: specifying a schema name requires a table name

No comments:

Post a Comment