expdp

■ ディレクトリオブジェクト登録
conn system/oracle
set linesize 400
col owner for a10
col directory_name for a30
col directory_path for a50
col grantee for a20
col table_name for a30
col privilege for a20

select owner, directory_name,directory_path from all_directories where directory_name = 'DATA_PUMP_DIR';

select owner, grantee,table_name, privilege from dba_tab_privs where table_name = 'DATA_PUMP_DIR';

create or replace directory dpump_dir as '/mnt/extHDD/dpdump ';

select owner,directory_name,directory_path from all_directories where directory_name = 'DPUMP_DIR';

conn / as sysdba

grant read on directory dpump_dir to system;

grant write on directory dpump_dir to system;

select owner, grantee, table_name, privilege from dba_tab_privs where grantee = 'SYSTEM' and TABLE_NAME = 'DPUMP_DIR';

■ expdpコマンド

usage(){

       echo USAGE: sh cmd.sh expdp \[table\]

}

SCHEMA_LISTS="schema01|Y|system|oracle|sid|directory \

              schema02|N|system|oracle|sid|directory \

             "

main(){

    case "$1" in

    "expdp" )

        expdp_cmd $2

        ;;

     *)

        usage

        ;;

    esac

}

expdp_cmd(){

    for row in $SCHEMA_LISTS; do

        ORA_SCHEMA=`echo $row      | cut -d "|" -f 1`

        ORA_FULL=`echo $row        | cut -d "|" -f 2`

        ORA_USER=`echo $row        | cut -d "|" -f 3`

        ORA_PASS=`echo $row        | cut -d "|" -f 4`

        ORA_SID=`echo $row         | cut -d "|" -f 5`

        ORA_DIRECTORY=`echo $row   | cut -d "|" -f 6`

        if [ "$1" == "" ]  || [ "$1" == "${ORA_SCHEMA}" ]; then

            echo "[`date '+%Y-%m-%d %H:%M:%S'`] Exporting SCHEMAS=${ORA_SCHEMA} START"

            echo "expdp ${ORA_USER}/${ORA_PASS}@${ORA_SID} DIRECTORY=${ORA_DIRECTORY} SCHEMAS=${ORA_SCHEMA} DUMPFILE=exp_${ORA_SCHEMA}.dmp LOGFILE=exp_${ORA_SCHEMA}.log"

            #expdp ${ORA_USER}/${ORA_PASS}@${ORA_SID} DIRECTORY=${ORA_DIRECTORY} SCHEMAS=${ORA_SCHEMA} DUMPFILE=exp_${ORA_SCHEMA}.dmp LOGFILE=exp_${ORA_SCHEMA}.log

            if [ $? -ne 0 ]; then

               echo "[`date '+%Y-%m-%d %H:%M:%S'`] Exporting SCHEMAS=${ORA_SCHEMA} ERROR !! "

            else

               echo "[`date '+%Y-%m-%d %H:%M:%S'`] Exporting SCHEMAS=${ORA_SCHEMA} NORMAL END"

            fi

        fi

    done

}

main $@

■meta only

expdp ${ORA_USER}/${ORA_PASS}@${ORA_SID} FULL=Y DIRECTORY=${DIRECT} DUMPFILE=exp_metaonly.dmp LOGFILE=exp_metaonly.log CONTENT=METADATA_ONLY