潛水珽的異想世界

今天跟同事討論了一個轉檔的問題,同事問到要想將Oracle A DB轉到Oracle B DB,load 到DB可以用SQL LOADER(SQLLDR)來做,可是匯入前匯出資料的動作卻不知道有甚麼方法可以快速的達到目的,以下提出一些做法。


1.askTom提供了一支可以轉成cvs格式的function(據說是Tom自己寫的~厲害厲害~),做法如下:
step1.
建立一個oracle directory物件(這個物件指的是oracle db server上的目錄,不建議建在$ORACLE_HOME)

CREATE OR REPLACE DIRECTORY
CVS_DATA AS
'C:\TEMP';


step2.
賦予使用者對directory有讀寫的權利

GRANT READ, WRITE ON DIRECTORY SYS.CVS_DATA TO SYSTEM WITH GRANT OPTION;

step3.
將tom寫的function給建立起來

CREATE OR REPLACE function SYSTEM.dump_csv( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 
    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;
 
    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
 
    l_status := dbms_sql.execute(l_theCursor);
 
    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            utl_file.put( l_output, l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);
 
    utl_file.fclose( l_output );
    return l_cnt;
end dump_csv;
/

step4.
如果要測試執行結果,在SQLPLUS下使用下列指令測試:

exec dbms_output.put_line(dump_csv('select * from hr.jobs',',','CVS_DATA','test.dat'));

P1: SQL查詢
P2: 分隔符號
P3: Oracle directory name(需大寫)
P4: cvs file name

PS:
1.超過300萬筆rows session容易hang住,建議超過萬筆資料請不要使用這樣方式做。
2.如果SQL查詢中有遇到單引號(')的部份,請改用雙引號(")來取代或是將SQL查詢的參數改為綁定變數(bind value)。


2.在itpub上有文章利用SQLPLUS的Spool 的方式將資料組合成Cvs檔的格式.

step1.
加大SQLPLUS中的linesize與pagesize

set linesize 200
set term off verify off feedback off pagesize 999

step2.
設定標籤樣式是html.

set markup html on entmap ON spool on preformat off


step3.
設定spool file 路徑與檔名.

spool tables.xls

step4:
執行SQL查詢.

select * from v$archive_dest_status;


step5.
關閉 spool並將結果輸出至檔案.

spool off


step6.
關閉標籤樣式.

set markup html off


PS:
這個能轉出來筆數就比上一個更少了,取決於SQLPLUS的linesize與pagesize.


其他有關資料載入載出的做法,可以參考以下這個網頁中的說明:
http://www.orafaq.com/wiki/SQL*Loader_FAQ