shell 指令碼常用 oracle 環境變數set 設定

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