问题描述

sqloader 数据导出怎么写。

在数据库中有79张表 将其全部导出为txt格式的文本 该如何实现
导入语句如下：#!/bin/sh
start_time=date +"%Y-%m-%d %H:%M:%S"
start=date +%s

#set -x
user=fina
password=fina
base_dir=/home/oracle/eastloader
ctl_dir=${base_dir}/ctl_dir
log_dir=${base_dir}/log

###########################################################
##get data_date,default the last day of last month##
if [ $# -ne 1 ];then
data_date=date +%Y%m%d
yy=echo ${data_date}|cut -c 1-4
mm=echo ${data_date}|cut -c 5-6
dd=echo ${data_date}|cut -c 7-8
if [ ${mm} -ne 1 ];then
mm=expr ${mm} - 1
aa=cal ${mm} ${yy}
dd=echo ${aa}|awk '{print $NF}'
if [ ${mm} -lt 10 ];then
data_date=${yy}0${mm}${dd}
else
data_date=${yy}${mm}${dd}
fi
else
yy=expr ${yy} - 1
mm=12
bb=31
data_date=${yy}${mm}${bb}
fi
else
data_date=${1}
fi

##create ctl files##

if [ -d ${base_dir}/ctl_dir/${data_date} ];then
rm -rf ${base_dir}/ctl_dir/${data_date}
fi
##create ctl files##

if [ -d ${base_dir}/ctl_dir/${data_date} ];then
rm -rf ${base_dir}/ctl_dir/${data_date}
fi

if [ ! -d ${base_dir}/ctl_dir/${data_date} ];then
mkdir -p ${base_dir}/ctl_dir/${data_date}
fi

#cp ${base_dir}/ctl_dir/${data_date}/* ${base_dir}/ctl_dir/stand/
cd ${base_dir}/ctl_dir/${data_date}
#chmod 755 *

cat ${base_dir}/config.ini | while read line
do
touch B1068H215030001-${line}-${data_date}.ctl
chmod 755 ${base_dir}/ctl_dir/${data_date}/B1068H215030001-${line}-${data_date}.ctl
echo "load data CHARACTERSET ZHS16GBK">>${base_dir}/ctl_dir/${data_date}/B1068H215030001-${line}-${data_date}.ctl
echo "infile '${base_dir}/data_dir/${data_date}/B1068H215030001-${line}-${data_date}.txt'">>${base_dir}/ctl_dir/${data_date}/B1068H215030001-${line}-${data_date}.ctl
cat ${base_dir}/ctl_dir/stand/${line}.ctl >>${base_dir}/ctl_dir/${data_date}/B1068H215030001-${line}-${data_date}.ctl
done

###########################################################
##main sqlloader##
if [ -d ${base_dir}/log/${data_date} ];then
rm -rf ${base_dir}/log/${data_date}
fi

if [ ! -d ${base_dir}/log/${data_date} ];then
mkdir -p ${base_dir}/log/${data_date}
fi

if [ -f ${base_dir}/${data_date}_load_main.log ];then
rm -rf ${base_dir}/${data_date}_load_main.log
fi

cd ${base_dir}

cat ${base_dir}/config.ini | while read line
do
echo "${base_dir}/data_dir/${data_date}/B1068H215030001-${line}-${data_date}.txt"
if [ -f ${base_dir}/data_dir/${data_date}/B1068H215030001-${line}-${data_date}.txt ];then
sqlldr userid=${user}/${password} direct=true control=${base_dir}/ctl_dir/${data_date}/B1068H215030001-${line}-${data_date}.ctl log=${base_dir}/log/${data_date}/B1068H215030001-${line}-${data_date}.log rows=100000 readsize=25800000 bindsize=25800000

echo "<<<<<<<<<<<<<<<<<<<<date +%Y%m%d-%H:%M:%S start to load ${line}">>${data_date}_load_main.log
cat ${base_dir}/log/${data_date}/B1068H215030001-${line}-${data_date}.log|grep "Total logical records skipped">>${data_date}_load_main.log
cat ${base_dir}/log/${data_date}/B1068H215030001-${line}-${data_date}.log|grep "Total logical records read">>${data_date}_load_main.log
cat ${base_dir}/log/${data_date}/B1068H215030001-${line}-${data_date}.log|grep "Total logical records rejected">>${data_date}_load_main.log
cat ${base_dir}/log/${data_date}/B1068H215030001-${line}-${data_date}.log|grep "Total logical records discarded">>${data_date}_load_main.log
else
echo ">>>>>>>>>>>>>>>THE DATA FILE IS NOT READY!! ${line}">>${data_date}_load_main.log
fi
done

end_time=date +"%Y-%m-%d %H:%M:%S"
end=date +%s
echo "中间表数据结转成功,时间总计:"expr $end - $start

导出语句我该如何去写

解决方案

http://wenku.baidu.com/link?url=rKXlv4UYww4v6UNQq8gYHtAUi-kxsRun2J0fZ5pjF59lkbGK_NdLLoqdxeAlDQfYnDO4iG-bh501Yb1GnqIPAhkPQzb2cLEV48T-xs7CLca

解决方案二：

http://blog.chinaunix.net/uid-23412974-id-2425222.html