Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.