Many options to use with Oracle expdp: who remembers all of them?
I end to forget and not use many of the options offered by expdp, so I have decided to write a script to help me remind its option displaying simple menus.
One of the things I wanted to test is weather the local directory can be used for the parameter DIRECTORY
SQL> create directory DIR as './.';
It is allowed ... but the dumpfile and the logfile are created on $ORACLE_HOME/dbs/ , at least in my HP-UNIX environment.
- The table dba_directories is queried to choose the parameter DIRECTORY
- Since quite offen the local directory would be the best choice for the destination, the script creates an Oracle directory on the fly, using the output of `pwd`
- dba_users is queried to choose the schemas for the expdp parameter SCHEMAS
I will add many more options, but it is already very useful.
# A script to help perform datapump exports
echo "Type a mnemonic for this expdp (could be steve_prod) \c:"
mnemonic=$mnemonic"_"`date '+%d%m%Y'` ## the date is appended to the mnemonic
# The list of available directories is display if the input is "?".
# If the input is ".", the directory QUI is created, which stores the output of pwd
while [ $DIR = "?" ]
echo "Destination directory [?] "
if [ $DIR = "?" ]; then
sqlplus -s / as sysdba<<EOF_SQL
column DIRECTORY_PATH format a64
set line 200
select * from dba_directories ORDER BY 1;
if [ $DIR = "." ]; then
sqlplus -s / as sysdba<<EOF_D
DROP DIRECTORY QUI;
CREATE DIRECTORY QUI AS '$WHERE';
# A menu for the existing schemas
while [ $SCHEMA = "?" ]
echo "Schemas to export (separated by comma)[?] "
if [ $SCHEMA = "?" ]; then
sqlplus -s / as sysdba <<EOF_S
set pages 0 heading off
SELECT username FROM dba_users WHERE username NOT IN ('SYS', 'SYSTEM') ORDER BY 1;
echo "Grade of parallelism  "
if [ -z $parallel ]; then
echo "dumpfile=$DUMPFILE" > $PAR_FILE
echo "logfile=$LOGFILE" >> $PAR_FILE
echo "schemas=($SCHEMA)" >> $PAR_FILE
echo "directory=$DIR" >> $PAR_FILE
echo "parallel=$parallel" >> $PAR_FILE
echo "JOB_NAME=JOB_$mnemonic" >> $PAR_FILE
echo "You can now execute expdp parfile=$PAR_FILE"