1.创建directory：
如果是非sys用户的话就必须单独授权，即使给了dba的权限也不包含创建目录的权限，授权方法如下：

SQL> grant create any directory to unitele;

授权成功。

SQL> create or replace directory dump_dir as '/home/oracle';

目录已创建。

2.expdp还是挺顺利的，shell如下，没报什么错误：
#!/usr/bin/sh
#Oracle Environment
export ORACLE_BASE=/arrayoracle/app
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=bill
export ORACLE_TERM=xterm
export LANG=zh_CN.gb18030
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
export PATH=$PATH:$ORACLE_HOME/bin:.
export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export SHLIB_PATH=/usr/lib:$ORACLE_HOME/lib:$ORACLE_HOME/newwork/lib:$ORACLE_HOME/rdbms/lib
export ORA_NLS10=$ORACLE_HOME/nls/data

cd /arraybkg/work/trans

for TABLE_NAME in $(cat tables_s1.param)
do
expdp backup/neusoft_tsc@bill DIRECTORY=TRANS_DIR DUMPFILE=$TABLE_NAME.dmp PARALLEL=5 TABLES=unitele.$TABLE_NAME LOGFILE=$TABLE_NAME.log
mv ./data/$TABLE_NAME.dmp ./ftping/$TABLE_NAME.dmp
cd ./ftping
ftp -inv > ./../log/ftp$TABLE_NAME.log <open 136.127.40.136
user oraacctt acctt_ora
cd /arrayacctt02/expdp/ftping
bin
put $TABLE_NAME.dmp
close
bye
EOF

rm -rf ./../ftping/$TABLE_NAME.dmp cd /arraybkg/work/trans

done

3.impdp碰见的第一个问题：
Connected to: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20080219120327" and "KUPC$S_1_20080
219120327" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1580
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown
object","streams pool","fixed allocation callback")

是由于 streams_pool_size 设置导致的，默认是0 需要改大点

4.第二个错误

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORA-39002: 操作无效
ORA-39070: 无法纯?罩疚募?
ORA-39088: 文件名不能包含路径说明

由于log里写了个"./"导致

修改完之后的shell如下：

#!/usr/bin/sh

#Oracle Environment
export ORACLE_BASE=/arrayacctt01/oracle/app
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=billtest
export ORACLE_TERM=xterm
export LANG=zh_CN.gb18030
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
export PATH=$PATH:$ORACLE_HOME/bin:.
export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export SHLIB_PATH=/usr/lib:$ORACLE_HOME/lib:$ORACLE_HOME/newwork/lib:$ORACLE_HOME/rdbms/lib
export ORA_NLS10=$ORACLE_HOME/nls/data

cd /arrayacctt02/expdp
for TABLE_NAME in $(cat tables_s1.param)
do
mv ./ftping/$TABLE_NAME.dmp ./data

impdp unitele/xxp1 DIRECTORY=TRANS_DIR DUMPFILE=$TABLE_NAME.dmp LOGFILE=$TABLE_NAME.log TABLES=$TABLE_NAME PARALLEL=5

mv ./data/$TABLE_NAME.dmp ./bak
done

5.是个效率的问题，无论大表小表，哪怕空表执行导出命令后都挂在这个情况下大约7-8分钟，但是真正导出数据时间并不慢

expdp unitele/lemontea dumpfile=UNIPSTN_BLUR_HEAD_T.dmp tables=UNIPSTN_BLUR_HEAD_T logfile=UNIPSTN_BLUR_HEAD_T parallel=5 directory=dump_dir

Export: Release 10.2.0.4.0 - Production on 星期三, 30 9月, 2009 8:36:44

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
启动 "UNITELE"."SYS_EXPORT_TABLE_01": unitele/******** dumpfile=UNIPSTN_BLUR_HEAD_T.dmp tables=UNIPSTN_BLUR_HEAD_T logfile=UNIPSTN_BLUR_HEAD_T parallel=5 directory=dump_dir
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

尝试了N多办法，没有解决掉这个问题，expdp就是要通读一遍数据库
由此可见对于性能问题，并不能一概而论啊，exp有exp的优势，对于小数据量或者单表导出导入还是老办法exp +imp比较有优势，如果一次大量导出的话考虑用数据泵

expdp和impdp使用过程的问题和体会