#!/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博客

批量按用户expdp

最新文章