Tom为了体现“自动化精神”，提供了一整套从数据库中卸载数据到平文本中的方法。这个小文儿演示一下这些脚本的使用方法。
1.从AskTom网站上下载Tom的脚本
Tom关于卸载脚本的介绍信息
http://asktom.oracle.com/tkyte/flat/index.html
脚本直接下载链接
http://asktom.oracle.com/tkyte/flat/unloader.zip
2.解压下载得到的unloader.zip文件后，您会得到6个文件，分别是：
flat ---- Shell环境下以tab为间隔卸载数据
flat.cmd ---- Windows环境下以tab为间隔卸载数据
flat.sql ---- SQL*Plus环境下以tab为间隔卸载数据
sqlldr_exp ---- Shell环境下以“|”为间隔卸载数据
sqlldr_exp.cmd ---- Windows环境下以“|”为间隔卸载数据
sqlldr_exp.sql ---- SQL*Plus环境下以“|”为间隔卸载数据
3.演示一下Windows环境的脚本sqlldr_exp.cmd的使用方法
什么参数都不加的情况下可以得到一个简略的使用方法的介绍
C:/>sqlldr_exp.cmd
"usage sqlldr_exp un/pw [tables|views]"
"example sqlldr_exp scott/tiger emp dept"
"description Select over standard out all rows of table or view with "
" columns delimited by tabs."
使用sqlldr_exp.cmd命令后紧接着用户名、密码以及服务名，最后紧跟着待导出的表名就可以了。
C:/>sqlldr_exp.cmd sec/sec@144.194.192.183 emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|MANAGER|7839|02-APR-81|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7788|SCOTT|ANALYST|7566|09-DEC-82|3000||20
7839|KING|PRESIDENT||17-NOV-81|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
7876|ADAMS|CLERK|7788|12-JAN-83|1100||20
7900|JAMES|CLERK|7698|03-DEC-81|950||30
7902|FORD|ANALYST|7566|03-DEC-81|3000||20
7934|MILLER|CLERK|7782|23-JAN-82|1300||10
OK，到此可以看到这个脚本已经自动生成了SQLLDR的控制文件。可以使用这个控制文件直接加载数据到目标数据库中。
将上面的内容保存在emp.ctl控制文件中，使用下面的命令就可以完成数据的迁入的功能。
$ sqlldr sec/sec control=emp.ctl
4.再演示一下sqlldr_exp.sql脚本
这个脚本的功能是在SQL*Plus环境下以“|”为间隔卸载数据。演示如下：
ora10g@testdb183 /home/oracle/unloader$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 8 16:39:59 2009
Copyright (c) 1982, 2006,
Oracle. All Rights Reserved.
Connected to:
Oracle Database
10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> @sqlldr_exp.sql emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|1980-12-17 00:00:00|800||20
7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30
7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|1982-12-09 00:00:00|3000||20
7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|1983-01-12 00:00:00|1100||20
7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30
7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20
7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
与上面的sqlldr_exp.cmd脚本得到的内容是完全一样的。Tom只是通过不同的方式来实现了这个功能，可以按照个人喜好来使用。
5.最后，演示一下flat.sql和flat.cmd脚本的使用
SQL*Plus环境下以tab为间隔卸载数据
ora10g@testdb183 /home/oracle/unloader$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 8 16:45:02 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> @flat.sql emp
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
C:/>flat.cmd sec/sec@144.194.192.183 emp
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
其他的脚本大家慢慢尝试。
Goodluck 2 u.
【附】将六个脚本的内容罗列记录在此，方便查询和使用（这些脚本编写的都很精致，都值得细心的体会。）
1.flat ---- Shell环境下以tab为间隔卸载数据
$ cat flat
#!/bin/sh
if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]
example: flat scott/tiger emp dept
description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done
2.flat.cmd ---- Windows环境下以tab为间隔卸载数据
$ cat flat.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @flat.sql %2
goto :done
:Usage
echo "usage flat un/pw [tables|views]"
echo "example flat scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
3.flat.sql ---- SQL*Plus环境下以tab为间隔卸载数据
$ cat flat.sql
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
prompt from &1
prompt /
spool off
set termout on
@ytmpy.sql
exit
4.sqlldr_exp ---- Shell环境下以“|”为间隔卸载数据
$ cat sqlldr_exp
#!/bin/sh
if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]
example: flat scott/tiger emp dept
description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $X
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('$X')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
#rm /tmp/flat$$.sql
done
5.sqlldr_exp.cmd ---- Windows环境下以“|”为间隔卸载数据
$ cat sqlldr_exp.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @sqlldr_exp.sql %2
goto :done
:Usage
echo "usage sqlldr_exp un/pw [tables|views]"
echo "example sqlldr_exp scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
6.sqlldr_exp.sql ---- SQL*Plus环境下以“|”为间隔卸载数据
$ cat sqlldr_exp.sql
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE &1
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
prompt from &1
prompt /
spool off
set termout on
@ytmpy.sql
exit
-- The End --