oracle的匯出spool，exp,pl/sql匯出查詢結果

   

第一：SPOOL(SPOOL是SQLPLUS的命令，不是SQL文法裡面的東西。)

對於SPOOL資料的SQL，最好要自己定義格式，以方便程式直接匯入,SQL語句如：
select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;

spool常用的設定
set colsep' ';　　　 //域輸出分隔字元
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 trimout on;　　　//去除標準輸出每行的拖尾空格，預設為off
set trimspool on;　　//去除重新導向（spool）輸出每行的拖尾空格，預設為off

匯出文本資料的建議格式：
SQL*PLUS環境設定SET NEWPAGE NONE
                SET HEADING OFF
                SET SPACE 0
                SET PAGESIZE 0
                SET TRIMOUT ON
                SET TRIMSPOOL ON
                SET LINESIZE 2500

註：LINESIZE要稍微設定大些，免得資料被截斷，它應和相應的TRIMSPOOL結合使用防止匯出的文本有太多的尾部空格。但是如果 LINESIZE設定太大，會大大降低匯出的速度，另外在WINDOWS下匯出最好不要用PLSQL匯出，速度比較慢，直接用COMMEND下的 SQLPLUS命令已最小化的視窗執行。

對於欄位內包含很多斷行符號分行符號的應該給與過濾，形成比較規矩的文字檔。通常情況下，我們使用SPOOL方法，將資料庫中的表匯出為文字檔的時候會採用兩種方法，如下述：

方法一：採用以下格式指令碼　
        set colsep '|'               --設定|為資料行分隔符號
　　set trimspool on
　　set linesize 120
　　set pagesize 2000         
　　set newpage 1 --設定頁與頁之間的分隔 當set newpage 0 時，會在每頁的開頭有一個小的黑方框。當set newpage none 時，會在頁和頁之間沒有任何間隔。

當set newpage n 時，會在頁和頁之間隔著n個空行。就是每次輸出的時候欄位名字下面的資料行數的空格。
　　set heading off            --去迴轉部select語句
　　set term off
        set num 18                 
        set feedback off       --去掉後面統計多少行數     ，1460 rows selected
　　spool 路徑+檔案名稱
　　select * from tablename;
　　spool off

方法二：採用以下指令碼
        set trimspool on
　　set linesize 120
　　set pagesize 2000
　　set newpage 1
　　set heading off
　　set term off

　　spool 路徑+檔案名稱
　　select col1||','||col2||','||col3||','||col4||'..' from tablename;
　　spool off

比較以上方法，即方法一採用設定分隔字元然後由sqlplus自己使用設定的分隔字元對欄位進行分割，方法二將分隔字元拼接在SELECT語句中，即手工控制輸出格式。

在實踐中，發現通過方法一匯出來的資料具有很大的不確定性，這種方法匯出來的資料再由sqlldr匯入的時候出錯的可能性在95%以上，尤其對大批量的資料表，如100萬條記錄的表更是如此，而且匯出的資料檔案狂大。

而方法二匯出的資料檔案格式很規整，資料檔案的大小可能是方法一的1/4左右。經這種方法匯出來的資料檔案再由sqlldr匯入時，出錯的可能性很小，基本都可以匯入成功。

因此，實踐中我建議大家使用方法二手工去控制spool檔案的格式，這樣可以減小出錯的可能性，避免走很多彎路。

方法1：在linux中去掉。sed 's/"//g' aa.txt >>c.txt

方法2:在sqlplus中用spool命令。可以去掉前面和後面，但是第一行會是空行，也要用linux命令才能去掉。sed '1d' a.txt >>aa.txt 可以用tr命令，但是所有的tr命令都可以用sed完成。

掛載，

如果要匯出為excel，則要

1.先拷貝到文字檔

2.另存新檔 *.csv

3.再另存新檔 *.xls

 第二：exp

一、用途：
    1、在不同的系統平台間移植資料。
    2、資料庫的備份和恢複手段之一。
    3、用於使用者間的資料移動。
二、基本文法和執行個體：
    1、EXP:
       有三種主要的方式（完全、使用者、表）
       1、完全：
          EXP SYSTEM/MANAGER   BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
          如果要執行完全匯出，必須具有特殊的許可權
       2、使用者模式：
          EXP SONIC/SONIC     BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC
          這樣使用者SONIC的所有對象被輸出到檔案中。
       3、表模式：
          EXP SONIC/SONIC     BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)

          這樣使用者SONIC的表SONIC就被匯出
    2、IMP:
       具有三種模式（完全、使用者、表）
       1、完全：
          IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
       2、使用者模式：
          IMP SONIC/SONIC     BUFFER=64000 FILE=C:\SONIC.DMP FROMUSER=SONIC TOUSER=SONIC

          這樣使用者SONIC的所有對象被匯入到檔案中。必須指定FROMUSER、TOUSER參數，這樣才能匯入資料。
       3、表模式：
          EXP SONIC/SONIC     BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)

          這樣使用者SONIC的表SONIC就被匯入。

Oracle資料匯入匯出imp/exp就相當於oracle資料還原與備份。exp命令可以把資料從遠端資料 程式庫伺服器匯出到本地的dmp檔案，imp命令可以把dmp檔案從本地匯入到遠處的資料庫伺服器中。 利用這個功能可以構建兩個相同的資料庫，一個用來測試，一個用來正式使用

 1 將資料庫TEST完全匯出,使用者名稱system 密碼manager 匯出到D:\daochu.dmp中
   exp system/manager@TEST file=d:\daochu.dmp full=y

2 將資料庫中system使用者與sys使用者的表匯出
   exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

3 將資料庫中的表inner_notify、notify_staff_relat匯出
    exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4 將資料庫中的表table1中的欄位filed1以"00"打頭的資料匯出
   exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

上面是常用的匯出，對於壓縮，既用winzip把dmp檔案可以很好的壓縮。也可以在上面命令後面 加上 compress=y 來實現。

 問題1：匯入的時候有些表已經存在，

    對該表就不進行匯入。
   在後面加上 ignore=y 就可以了。
想覆蓋掉怎麼辦？刪了原來的表，我怎麼知道原來的表示哪些？

system/manager是使用者名稱和密碼
file=daochu.dmp 是要匯入的檔案
log=dible_db是組建記錄檔的名稱
full=y 是匯入整個檔案
ignore=y 是忽略建立錯誤

詳細用法可在“運行”中輸入cmd進入命令視窗鍵入
'IMP HELP=Y' 擷取協助資訊提示
問題2
Oracle 不允許直接改變表的擁有者, 利用Export/Import可以達到這一目的.
先建立import9.par,
然後，使用時命令如下：imp parfile=/filepath/import9.par
例 import9.par 內容如下：
        FROMUSER=TGPMS       
        TOUSER=TGPMS2     （註：把表的擁有者由FROMUSER改為TOUSER，FROMUSER和TOUSER的使用者可以不同）          

        ROWS=Y
        INDEXES=Y
        GRANTS=Y
        CONSTRAINTS=Y
        BUFFER=409600
        file==/backup/ctgpc_20030623.dmp
        log==/backup/import_20030623.log
測試：

先匯出一張表：

exp
dgsmscongxing/congxingdgpwd@zqsms
file= d:\aa.dmp tables=(aa)
成功匯出

imp scott/tiger@orcl file=d:\aa.dmp  tables=(aa) 成功匯入

imp scott/tiger@orcl file=d:\aa.dmp 
fromuser=dgsmscongxing touser=scott tables=(aa)成功匯入