Oracle Data Pump – Using Environment Variables in Queries

datapumplinuxoracle

I have been trying to use data pump (expdp and impdp) in order to retrieve some data from a database to import it into another.

I am using the expdp using the table and the query parameters in order to select specific data from one table.

Can I use variables from Linux in the expdp and impdp queries?

For example, the directory is not a standard directory. I accept this as a variable from Linux and create a directory in Oracle using a bind variable. The thing is that later on when I want to invoke data pump I cannot have a standard directory but rather a variable. How do I use the variable from Linux in the expdp and impdp query?

Best Answer

Of course, you can. Here is an example:

$ cat expdp.sh
#!/bin/bash

DIR_NAME=dummy
DIR_PATH=/oracle/base/export
QUERY='employees:"WHERE employee_id = 100"'
mkdir -p $DIR_PATH
sqlplus / as sysdba<<EOF
create directory $DIR_NAME as '$DIR_PATH';
exit
EOF
expdp \"/ as sysdba\" directory=$DIR_NAME tables=hr.employees query=$QUERY

Output:

$ ./expdp.sh

SQL*Plus: Release 12.1.0.1.0 Production on Sun Mar 30 20:06:26 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
Directory created.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export: Release 12.1.0.1.0 - Production on Sun Mar 30 20:06:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=dummy tables=hr.employees query=employees:"WHERE employee_id = 100"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "HR"."EMPLOYEES"                            9.570 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oracle/base/export/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Mar 30 20:06:47 2014 elapsed 0 00:00:19
Related Question