ORACLE本文主要介绍使用
sqlplus工具将数据库字段导出成TXT文本格式文件。
一、文本导出原理
Oracle 文本导出的原理就是把SELECT输出结果直接输出到文本文件中。使用spool文件名方式制定输出结果
二、spool常用的设置对于SPOOL数据的SQL,最好要自己定义格式,以方便程序直接导入,SQL语句如:
select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;
set echo off; //显示start启动的脚本中的每个sql命令,缺省为on
set feedback off; /回显本次sql命令处理的记录条数,缺省为on
set heading off; //输出域标题,缺省为on
set pagesize 0; //输出每页行数,缺省为24,为了避免分页,可设定为0。
set termout off; //显示脚本中的命令的执行结果,缺省为on
set trimspool on; //去除重定向(spool)输出每行的拖尾空格,缺省为off
SET TERM OFF; //去除结果输出。特别提示:在执行语句时各个版本的ORACLE设置略有不同,oracle8版本使用的是
set termout off //命令关闭回显。Oracle92版本使用的是SET TERM OFF,标准的设置为:
SET TIME OFF
SET ECHO OFF
SET HEAD OFF
SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT OFF
SET TRIMSPOOL ON
SET LINESIZE 2500
SET FEEDBACK OFF
SET ECHO OFF
SET TERM OFF
三、脚本编写方法
1、单执行文件方式SQL和执行脚本写到一个文件中,该方法主要适用于UNIX系统中,WINDOWS支持不是很好,例如:Tru64系统:
#!/bin/sh
send_day=`sqlplus -s $DB_USER/$DB_PWD@$DB_SERV< SET TIME OFF
SET ECHO OFF
SET HEAD OFF
SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT OFF
SET TRIMSPOOL ON
SET LINESIZE 2500
SET FEEDBACK OFF
SET TERM OFF
spool /zxt10/ccpc/ts_yyt/ts_crm_area.txt
select a.serv_id||'|'||b.node||'|'||a.serv_state||'|'||a.bureau_no||'|'||a.state
from serv@crm a,tvlsm_bureau@crm b
where a.city_no=38 and a.bureau_no=b.bureau_no;
spool off
EOF
AIX系统:
#!/bin/sh
send_day=`sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<
2、调用外部SQL方式该方法使用sqlplus调用外部SQL的方式,直接执行,或将多条写入同一个bat批处理文件中,改方法也适用于UNIX系统:
sqlplus -s $DB_USER/$DB_PWD@$DB_SERV @unload_oracle.sql
其中unload_oracle.sql
文件内容为:
SET TIME OFF
SET ECHO OFF
SET HEAD OFF
SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT OFF
SET TRIMSPOOL ON
SET LINESIZE 2500
SET FEEDBACK OFF
SET TERM OFF
spool /zxt10/ccpc/ts_yyt/ts_crm_area.txt
select a.serv_id||'|'||b.node||'|'||a.serv_state||'|'||a.bureau_no||'|'||a.state from serv@crm a,tvlsm_bureau@crm b where a.city_no=38 and a.bureau_no=b.bureau_no;
spool off
exit;
四、大表导出时的问题遇到超过千万级别的大数据表导出,经常会遇到ORA-01555的错误,ORA-01555: 快照过旧: 回退段号 1 在名称为 "_SYSSMU1$" 过小导致终止。改问题解决方式有两条路:
1、修改数据库 undo retension参数,延长快照更新时间。
2、将大表分成多个表导出最常用的方式是对KEY值取模:
spool file_name_1.txt
select user_data||'|' from table where mod(key_id,2)=1;
spool off
spool file_name_2.txt
select user_data||'|' from table where mod(key_id,2)=0;
spool off
sqlplus工具将数据库字段导出成TXT文本格式文件。
一、文本导出原理
Oracle 文本导出的原理就是把SELECT输出结果直接输出到文本文件中。使用spool文件名方式制定输出结果
二、spool常用的设置对于SPOOL数据的SQL,最好要自己定义格式,以方便程序直接导入,SQL语句如:
select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;
set echo off; //显示start启动的脚本中的每个sql命令,缺省为on
set feedback off; /回显本次sql命令处理的记录条数,缺省为on
set heading off; //输出域标题,缺省为on
set pagesize 0; //输出每页行数,缺省为24,为了避免分页,可设定为0。
set termout off; //显示脚本中的命令的执行结果,缺省为on
set trimspool on; //去除重定向(spool)输出每行的拖尾空格,缺省为off
SET TERM OFF; //去除结果输出。特别提示:在执行语句时各个版本的ORACLE设置略有不同,oracle8版本使用的是
set termout off //命令关闭回显。Oracle92版本使用的是SET TERM OFF,标准的设置为:
SET TIME OFF
SET ECHO OFF
SET HEAD OFF
SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT OFF
SET TRIMSPOOL ON
SET LINESIZE 2500
SET FEEDBACK OFF
SET ECHO OFF
SET TERM OFF
三、脚本编写方法
1、单执行文件方式SQL和执行脚本写到一个文件中,该方法主要适用于UNIX系统中,WINDOWS支持不是很好,例如:Tru64系统:
#!/bin/sh
send_day=`sqlplus -s $DB_USER/$DB_PWD@$DB_SERV< SET TIME OFF
SET ECHO OFF
SET HEAD OFF
SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT OFF
SET TRIMSPOOL ON
SET LINESIZE 2500
SET FEEDBACK OFF
SET TERM OFF
spool /zxt10/ccpc/ts_yyt/ts_crm_area.txt
select a.serv_id||'|'||b.node||'|'||a.serv_state||'|'||a.bureau_no||'|'||a.state
from serv@crm a,tvlsm_bureau@crm b
where a.city_no=38 and a.bureau_no=b.bureau_no;
spool off
EOF
AIX系统:
#!/bin/sh
send_day=`sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<
2、调用外部SQL方式该方法使用sqlplus调用外部SQL的方式,直接执行,或将多条写入同一个bat批处理文件中,改方法也适用于UNIX系统:
sqlplus -s $DB_USER/$DB_PWD@$DB_SERV @unload_oracle.sql
其中unload_oracle.sql
文件内容为:
SET TIME OFF
SET ECHO OFF
SET HEAD OFF
SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT OFF
SET TRIMSPOOL ON
SET LINESIZE 2500
SET FEEDBACK OFF
SET TERM OFF
spool /zxt10/ccpc/ts_yyt/ts_crm_area.txt
select a.serv_id||'|'||b.node||'|'||a.serv_state||'|'||a.bureau_no||'|'||a.state from serv@crm a,tvlsm_bureau@crm b where a.city_no=38 and a.bureau_no=b.bureau_no;
spool off
exit;
四、大表导出时的问题遇到超过千万级别的大数据表导出,经常会遇到ORA-01555的错误,ORA-01555: 快照过旧: 回退段号 1 在名称为 "_SYSSMU1$" 过小导致终止。改问题解决方式有两条路:
1、修改数据库 undo retension参数,延长快照更新时间。
2、将大表分成多个表导出最常用的方式是对KEY值取模:
spool file_name_1.txt
select user_data||'|' from table where mod(key_id,2)=1;
spool off
spool file_name_2.txt
select user_data||'|' from table where mod(key_id,2)=0;
spool off
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29446986/viewspace-1681815/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29446986/viewspace-1681815/