方法一:使用spool进行保存
SQL>spool emp.txt
SQL>select empno||','||ename||','||job||','||mgr||','||sal from emp;
EMPNO||','||ENAME||','||JOB||','||MGR||','||SAL
----------------------------------------------------------------------------------------------------
7369,SMITH,CLERK,7902,800
7499,ALLEN,SALESMAN,7698,1600
7521,WARD,SALESMAN,7698,1250
7566,JONES,MANAGER,7839,2975
7654,MARTIN,SALESMAN,7698,1250
7698,BLAKE,MANAGER,7839,2850
7782,CLARK,MANAGER,7839,2450
7788,SCOTT,ANALYST,7566,3000
7839,KING,PRESIDENT,,5000
7844,TURNER,SALESMAN,7698,1500
7876,ADAMS,CLERK,7788,1100
7900,JAMES,CLERK,7698,950
7902,FORD,ANALYST,7566,3000
7934,MILLER,CLERK,7782,1300
14 rows selected.
SQL>spool off;
自己写了简单脚本进行导出指定表到外部文本:
#!/bin/bash
##jxp
##导出指定表结构
tname=$1
sqlplus -S scott/tiger <
spool tname1.txt
desc $tname
spool off
exit
EOF
##截取表内字段名
sed -n '3,$p' tname1.txt| awk '{print$1}' |sed '$d' >tname2.txt
##使用abcd.sed把字段名进行列转行输出并使用'||'作为分隔符传入tname_1
tname_1=`sed -f abcd.sed tname2.txt`
#echo $tname_1
##传入表名,在sqlplus中进行选择数据
new_tname=$tname
sqlplus -S scott/tiger <
set pagesize 0;
set linesize 300;
set feedback off;
alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss'
spool $new_tname.txt
select $tname_1 from $new_tname;
spool off
exit;
EOF
##删除产生的临时文件
rm -rf tname*.txt
#
需要修改的可能是登陆数据库的用户名了。其中脚本中用到了abcd.sed文件,下面列出此文件内容和作用:
{
N
s/\n/\|\|\'||\'\|\|/g
}
{
N
s/\n/\|\|\'||\'\|\|/g
}
{
N
s/\n/\|\|\'||\'\|\|/g
}
{
N
s/\n/\|\|\'||\'\|\|/g
}
{
N
s/\n/\|\|\'||\'\|\|/g
}
{
N
s/\n/\|\|\'||\'\|\|/g
}
此处指列出abcd.sed(abcd是随便起的名字)的简单几行,如果导出的表的字段较多的话,需要补充abcd.sed的内容,直接复制粘贴以上内容就Ok。
abcd.sed的主要作用就是把需要导出表的字段进行合并使用“||”连接为一行。分隔符“||”可以自己指定其他的符号替代。
脚本的使用:
把exp_table.sh和abcd.sed放到一个目录下直接运行sh脚本,后面跟用户下的表名
[oracle@jxpred test_dir]$ nohup ./exp_table.sh dept
[oracle@jxpred test_dir]$ cat dept.txt
10||ACCOUNTING||NEW YORK
20||RESEARCH||DALLAS
30||SALES||CHICAGO
40||OPERATIONS||BOSTON
方法二:使用oracle中的UTL_FILE
conn sys/oracle as sysdba
--在初始化参数文件中加入下面一行
utl_file_dir=d:\bk
alter system setutl_file_dir='/home/oracle/exp'scope=spfile;
这个需要重启数据库,比较严重哦。
--其中\n为换行
--%s为替代字符,将来会被后面的1到5个参数替代,默认值为NULL
--NEW_LINE 过程建立一个新的空行
-------------------案例:将DEPT表的数据导入到文本中-----------------------------
declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen('/home/oracle/exp','dept.txt','w');
UTL_FILE.PUTF (v_filehandle,'表DEPT的文本数据,导出时间为:%s\n', SYSDATE);
UTL_FILE.NEW_LINE (v_filehandle);
for i in(select * from scott.dept) loop
UTL_FILE.PUTF (v_filehandle, '%s ,%s, %s\n',i.deptno,i.dname,i.loc);
end loop;
UTL_FILE.FCLOSE (v_filehandle);
end;
/
此方法需要将表的所用的字段都写到
UTL_FILE.PUTF后 :
UTL_FILE.PUTF (out_file, '%s||%s||%s||%s||%s||',i.TYPEID,i.SYSTEMID,i.SOURCEID,i.SOURCENAME,i.DESTINATIONID);
UTL_FILE.PUTF (out_file, '%s||%s||%s||%s||%s||',i.DESTINATIONNAME,i.REMARK,i.OPRTR,i.OPRT_TIME,i.SORT_NO);
UTL_FILE.PUTF (out_file, '%s\n',i.PARENTID);
而且每个UTL_FILE.PUTF中最多只能有5个%s替代符。多的字段需要换行继续写UTL_FILE.PUTF内容。此处使用的是“||”作为 分隔符,可以替换其他分隔符。
以上是oracle较常用的两种导出表到文本的方法。