批量按使用者expdp

#!/bin/sh

#run as: sh exp_tabs.sh

#dmpfile name change to schema_tab_name.dmp

ORACLE_HOME=/ora11g/app/product/11.2.0/db_1

export ORACLE_HOME

ORACLE_SID=orcl

export ORACLE_SID

EXPDP=$ORACLE_HOME/bin/expdp

curr_date=`date +%Y%m%d%H%M%S`

$ORACLE_HOME/bin/sqlplus -S  "/as sysdba" <<EOF

SET SERVEROUTPUT ON FORMAT WRAPPED;

SET ECHO OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 100;

create or replace directory mc_dmp_dir as '/tmp';

spool tab.list

select owner||'.'||table_name from dba_tables where owner in ('SCOTT','HR');

spool off

exit

EOF

cat tab.list |while read line

    do

        $EXPDP \'/ as sysdba\' directory=mc_dmp_dir DUMPFILE=expdp_${line}_$curr_date.dmp LOGFILE=expdp_${line}_$curr_date.log tables=${line} EXCLUDE=statistics

    done

exit

EOF

find /tmp -name "expdp*" -mtime +3 -exec rm {} \;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23732248/viewspace-2285226/，如需轉載，請註明出處，否則將追究法律責任。