写了两个sh,执行2.sh生成 3.sh,执行3.sh就可以了 1.sh: if [ "$1" = "" ] || [ "$2" = "" ]; then echo "usage: sh $0 [user/pwd@sid] [tbl_name]" exit fi echo $2.ctl sqlplus -S $1 >$2.ctl <<! set feedback off; set pagesize 0; set linesize 1000; set trimout on; set trimspool on;select column_name from( select 'LOAD DATA '||'INFILE '''||TABLE_NAME||'.unl'||''''||'into table '||table_name||' replace fields terminated by "|" trailing nullcols(' as column_name,0 as column_id from user_tables where table_name='$2' union select column_name||',',column_id from user_tab_columns where table_name='$2' and column_id <>(select max(column_id) from user_tab_columns where table_name='$2') union select column_name||')',column_id from user_tab_columns where table_name='$2' and column_id =(select max(column_id) from user_tab_columns where table_name='$2') ) order by column_id; exit; ! 2.sh echo "sh 2.sh 架构用户 密码 服务名" echo $1 echo $2 echo $3 sqlplus -S $1/$2@$3 >3.sh <<! set feedback off; set pagesize 0; set linesize 1000; set trimout on; set trimspool on;select 'sh ./1.sh '||'$1/$2@$3'||' '||object_name ||';' from all_objects where owner=upper('$1') and object_type=upper('table'); exit; !
1.sh:
if [ "$1" = "" ] || [ "$2" = "" ]; then
echo "usage: sh $0 [user/pwd@sid] [tbl_name]"
exit
fi
echo $2.ctl sqlplus -S $1 >$2.ctl <<!
set feedback off;
set pagesize 0;
set linesize 1000;
set trimout on;
set trimspool on;select column_name
from(
select 'LOAD DATA '||'INFILE '''||TABLE_NAME||'.unl'||''''||'into table '||table_name||' replace fields terminated by "|" trailing nullcols(' as column_name,0 as column_id
from user_tables
where table_name='$2'
union
select column_name||',',column_id
from user_tab_columns
where table_name='$2'
and column_id <>(select max(column_id) from user_tab_columns where table_name='$2')
union
select column_name||')',column_id
from user_tab_columns
where table_name='$2'
and column_id =(select max(column_id) from user_tab_columns where table_name='$2')
)
order by column_id;
exit;
!
2.sh
echo "sh 2.sh 架构用户 密码 服务名"
echo $1
echo $2
echo $3 sqlplus -S $1/$2@$3 >3.sh <<!
set feedback off;
set pagesize 0;
set linesize 1000;
set trimout on;
set trimspool on;select 'sh ./1.sh '||'$1/$2@$3'||' '||object_name ||';'
from all_objects
where owner=upper('$1')
and object_type=upper('table');
exit;
!