ORACLE库到库导数SHELL

#!/bin/sh

##############################################

# Author :Chengjl

# Function:Extract data from one database to another

#

##############################################

#Source database connect information

sdb_user=aml

sdb_passwd=aml

sdb_tns=AML_30.3.3.30

#Destination database connect information

ddb_user=amlrisk

ddb_passwd=amlrisk

ddb_tns=bbdb

#Parameter

if [ $# -eq 2 ]; then

type=0

elif [ $# -eq 3 ];then

if [ \"$3\" == '0' ]||[ \"$3\" == '1' ]||[ \"$3\" == '2' ];then

type=$3

else

echo \"Parameter is wrong!\"

exit

fi

else

echo \"Parameter is wrong!\"

exit

fi

workday=$1

table_name=$(echo $2|tr '[a-z]' '[A-Z]')

file_name=/home/oracle/chengjl/${table_name}_${workday}.txt

table_struc_src=/home/oracle/chengjl/SRC_${table_name}.txt

table_struc_dst=/home/oracle/chengjl/DST_${table_name}.txt

table_struc_ctl=/home/oracle/chengjl/CTL_${table_name}.ctl

tab_columns=' '

table_name_mid=$(echo 'xxx'${workday}'xxx')

############################################################

#Unload data

##########################################################

##

#Get structure of table

function Unload_data()

{

sqlplus -s $sdb_user/$sdb_passwd@$sdb_tns </dev/null

SET feedback off

SET newpage none

SET pagesize 50000

SET linesize 20000

SET verify off

SET pagesize 0

SET term off

SET trims ON

SET heading off

SET trimspool ON

SET trimout ON

SET timing off

SET verify off

SET colsep |

spool $table_struc_src

SELECT CASE WHEN column_id =MAX(column_id)OVER()

THEN

'replace(replace('||column_name||',chr(10),chr(32)),chr(13),chr(32))'

ELSE

'replace(replace('||column_name||',chr(10),chr(32)),chr(13),chr(32))'||'||chr(8)||'

END AS all_col

FROM User_Tab_Columns

WHERE Table_Name = TRIM('$table_name')

ORDER BY column_id ;

spool off

eof

while read line

do

tab_columns=${tab_columns}${line}

done<$table_struc_src

#Extract data from database to file

#Get structure of table

sqlplus -s $sdb_user/$sdb_passwd@$sdb_tns </dev/null

SET feedback off

SET newpage none

SET pagesize 50000

SET linesize 20000

SET verify off

SET pagesize 0

SET term off

SET trims ON

SET heading off

SET trimspool ON

SET trimout ON

SET timing off

SET verify off

SET colsep |

spool $file_name

SELECT $tab_columns FROM $table_name ;

spool off

eof

echo $table_name

echo 'Unload '$(wc -l $file_name|cut -d ' ' -f1)'rows'

}

############################################################

#Load data

############################################################

function Load_data()

{

sqlplus -s $ddb_user/$ddb_passwd@$ddb_tns </dev/null

create table $table_name_mid as select * from $table_name where 1=0;

eof

#Get structure of table

sqlplus -s $ddb_user/$ddb_passwd@$ddb_tns </dev/null

SET feedback off

SET newpage none

SET pagesize 50000

SET linesize 20000

SET verify off

SET pagesize 0

SET term off

SET trims ON

SET heading off

SET trimspool ON

SET trimout ON

SET timing off

SET verify off

SET colsep |

spool $table_struc_dst

SELECT CASE WHEN column_id =MAX(column_id)OVER()

THEN column_name

ELSE column_name||','

END AS all_col

FROM User_Tab_Columns

WHERE Table_Name = TRIM('$table_name')

ORDER BY column_id ;

spool off

eof

#Create control file

echo \"LOAD DATA\" > $table_struc_ctl

echo \"INFILE '\"${file_name}\"'\" >>$table_struc_ctl

echo \"APPEND\" >>$table_struc_ctl

echo \"INTO TABLE \"${table_name_mid} >>$table_struc_ctl

echo \"FIELDS TERMINATED BY ''\" >>$table_struc_ctl

echo \"TRAILING NULLCOLS\" >>$table_struc_ctl

echo '(' >>$table_struc_ctl

while read line

do

echo $line >>$table_struc_ctl

done < $table_struc_dst

echo ')' >>$table_struc_ctl

#Load data

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

sqlldr $ddb_user/$ddb_passwd@$ddb_tns control=$table_struc_ctl

sqlplus -s $ddb_user/$ddb_passwd@$ddb_tns </dev/null

truncate table $table_name;

insert into $table_name select * from $table_name_mid ;

drop table $table_name_mid;

eof

}

############################################################

#Main structure

############################################################

if [ \"$type\" == '0' ];then

Unload_data

Load_data

elif [ \"$type\" == '1' ];then

Unload_data

elif [ \"$type\" == '2' ];then

Load_data

fi