Pages

Tuesday, October 27, 2015

Export table data with filtered clause - Query based export (oracle expdp)



This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue.

For Example: 

If you want to query all records of employees for a particular department you will use:

SELECT *
FROM HR.EMPLOYEES
WHERE dept = 10;


EXPDP UTILITY CMD :

$ expdp HR/XXXXX@ORCL directory=DPUMP dumpfile=TEST_EMP.dmp logfile=TEST_EMP.log include="HR.EMPLOYEES:\" in (where dept =10)\""



NORMAL EXPORT UTILITY CMD :

$ exp HR/XXXXX@ORCL file=/u01/app/oracle/dpump/TEST_EMP.dmp log=/u01/app/oracle/dpump/TEST_EMP.log tables=HR.EMPLOYEES query='"where dept =10"'



No comments :

Post a Comment