导出
导出指定用户下的所有表
export ORACLE_SID=bdyz
export ORACLE_BASE=/u01/oracle11g_R2
export ORACLE_HOME=$ORACLE_BASE/11g
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG="simplified chinese"_china.zhs16gbk;
export PATH=$PATH:$ORACLE_HOME/bin
rq=$(date +%Y%m%d)
user_name='lltf'
user_passwd='lltf_2017'
bak_dir=/u01/oradata/${user_name}/$rq
if [ ! -s $bak_dir ];then
mkdir -p $bak_dir
fi
sqlplus / as sysdba <<EOF
drop directory dump_dir;
create directory dump_dir as '${bak_dir}';
grant read,write on directory dump_dir to ${user_name};
quit
EOF
expdp ${user_name}/${user_passwd} directory=dump_dir dumpfile=${user_name}_%U.dmp schemas=${user_name} logfile=${user_name}_${rq}_exp.log compression=ALL filesize=1g parallel=8
导出指定用户下的指定表
#!/bin/sh
export ORACLE_SID=ldb1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/11.2.0/db_1
export PATH=${PATH}:$ORACLE_HOME/bin
bak_dir='/home/oracle/lxm/meituan/data_of_tables_need_compare_module'
user_name='ldb'
user_passwd='ldb'
sqlplus / as sysdba <<EOF
drop directory dump_dir;
create directory dump_dir as '${bak_dir}';
grant read,write on directory dump_dir to ${user_name};
quit
EOF
expdp ${user_name}/${user_passwd} TABLES=${user_name}.t_sof_subscriber,${user_name}.t_subscriber_import_task,${user_name}.t_as,${user_name}.t_subscriber,${user_name}.T_SMB_MSISDN directory=dump_dir dumpfile=${user_name}_%U.dmp compression=ALL filesize=1g parallel=8
导入
#!/bin/sh
. ~/.bash_profile
bak_dir='/home/oracle/lxm/meituan/data_of_tables_need_compare_module'
exp_user_name='ldb'
exp_tablespace='LDB_DATA'
exp_index_tablespace='LDB_INDEX'
current_tablespace='USERS'
user_name='test'
user_passwd='test'
sqlplus / as sysdba <<EOF
drop directory exp_dir;
create directory exp_dir as '${bak_dir}';
grant read,write on directory exp_dir to ${user_name};
quit
EOF
impdp ${user_name}/${user_passwd} PARALLEL=8 cluster=no dumpfile=${exp_user_name}_%U.dmp directory=exp_dir REMAP_TABLESPACE=${exp_tablespace}:${current_tablespace},${exp_index_tablespace}:${current_tablespace} REMAP_SCHEMA=${exp_user_name}:${user_name} TABLE_EXISTS_ACTION=truncate