Query parameter in Datapump(EXPDP/IMPDP)

create a directory in server and database as follow:

[oracle@pavanorcl101 admin]$ mkdir -p /u01/app/export

SQL>create directory exportdirectory as '/u01/app/export';


we are exporting records in  EMP table in KRISHNA Schema with city name 'BHIMAVARAM'

SQL> conn krishna
Enter password:
Connected.

SQL> select * from emp;

        ID NAME                 CITY
---------- -------------------- --------------------
         1 Sai                  Vijayawada
         2 Ram                 Vijayawada
         3 Pavan                Bhimavaram
         4 Krishna             Bhimavaram

creating a export par file with required parameters as below

[oracle@pavanorcl101 export]$ vi emp_table.par

directory=EXPORTDIRECTORY
dumpfile=emp_table.dmp
logfile=emp_table.log
tables=KRISHNA.EMP
query=EMP:"where CITY='Bhimavaram'"


Give read, write and execute permissions to the par file:

[oracle@pavanorcl101 export]$ chmod -R 777 emp_table.par


Run the export job in background using nohup as below:

[oracle@pavanorcl101 export]$ nohup expdp system/oracle parfile=emp_table.par &



creating a import par file with required parameters as below

[oracle@pavanorcl101 export]$ vi import_emp_table.par

directory=EXPORTDIRECTORY
dumpfile=emp_table.dmp
logfile=import_emp_table.log
remap_schema=krishna:pavan
tables=krishna.emp


Give read, write and execute permissions to the par file:

[oracle@pavanorcl101 export]$ chmod -R 777 import_emp_table.par


Run the export job in background using nohup as below:

[oracle@pavanorcl101 export]$ nohup impdp system/oracle parfile=import_emp_table.par &

SQL> conn pavan
Enter password:
Connected.

SQL> select * from  emp;

        ID NAME                 CITY
---------- -------------------- --------------------
         3 Pavan                Bhimavaram
         4 Krishna             Bhimavaram