ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】使用Tom卸载脚本卸载表数据到平文本文件

【实验】使用Tom卸载脚本卸载表数据到平文本文件

原创 Linux操作系统 作者:secooler 时间:2009-09-08 16:54:41 0 删除 编辑
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 --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-614181/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7978762