最近PL/SQLでプログラミングしてないと思ったので、リハビリを兼ねつつ、汎用的にテーブルのデータをCSV出力するプロシージャを作ってみる。

現在の環境
OS:Windows 7(Ultimate)
DB:Oracle11gXE


■１．権限付与

今回はadminというスキーマ内のテーブルをCSV出力するプロシージャを作ってみる。

出力先はXEが標準で持ってるDATA_PUMP_DIRディレクトリに出力しようと思うので、adminスキーマにDATA_PUMP_DIRのread, write権限を付与してみる。また出力に使うutl_fileの実行権限も一緒に付与。

SQL*PLUSでSYSでログインして下記を実行。

SQL>grant read, write on directory DATA_PUMP_DIR to admin;
SQL>grant execute on utl_file to admin;


■２．プロシージャの作成

次にadminスキーマにログインして、CSV出力するプロシージャ(csv_out)をコンパイル。

create or replace procedure csv_out (tbl_name in varchar2)
is
    select_str varchar2(2000);
    procedure create_select(tbl in varchar2, record_str out varchar2)
    is
        type rec_type is record(column_name varchar2(30), data_type varchar2(30));
        type tbl_rec_type is table of rec_type;
        tbl_rec tbl_rec_type;
    begin
        record_str := 'select ';
        execute immediate 'select column_name, data_type from user_tab_columns where table_name = :1 order by column_id' bulk collect into tbl_rec using tbl;
        for i in 1 .. tbl_rec.count loop
            record_str := record_str
                || case when tbl_rec(i).data_type in ('VARCHAR2', 'CHAR', 'CLOB') then ' ''"'' || ' else '' end
                || tbl_rec(i).column_name
                || case when tbl_rec(i).data_type in ('VARCHAR2', 'CHAR', 'CLOB') then ' || ''"'' ' else '' end
                || case when i=tbl_rec.last then '' else ' || '','' ||' end;
        end loop;
        record_str := record_str || ' as csv_data from ' || tbl;
    end;

    procedure create_csv(tbl in varchar2, str in varchar2)
    is
        fout utl_file.file_type;
        type csv_data_type is table of varchar2(5000);
        csv_data_tbl csv_data_type;
    begin
        fout := utl_file.fopen('DATA_PUMP_DIR', tbl || '.csv', 'w');
        execute immediate str bulk collect into csv_data_tbl;
        for i in 1 .. csv_data_tbl.count loop
            utl_file.put_line(fout, csv_data_tbl(i));
        end loop;
        utl_file.fclose(fout);
    end;
begin
  -- CSV出力Select文作成
  create_select(upper(tbl_name), select_str);
  -- CSV出力
  create_csv(upper(tbl_name), select_str);
end;
/

昔よくやっていた、CSV出力するためのSELECT文を作ってdbms_outputで出力するというのをPL/SQLに置き換えただけ…。
一応VARCHAR2,CHAR,CLOBだけ2重符号で囲う。


■３．実行

adminスキーマの中のテーブル(******)を適当に指定して、実行してみる。

SQL>execute csv_out('******');

出力先(C:\oraclexe\app\oracle\admin\XE\dpdum)に、******.csvというファイル名で出力されるので確認。



もっと簡単に出力できる方法があったりするんかな？調べてないけど…。というか、たいていCSV出力はsql_developerでやっちゃったりしてる(-_-;)。

簡易アプリ作成でデータベース(Oracle11gXE)を使ったりしているが、アプリ移植の為に、今あるDB環境の複製が欲しくなった。調べてみると、XEに付随してるリカバリーマネージャ(RMAN)を使うことで簡単にできるらしい。

ちなみに今の環境
OS:Windows7Ultimate(64bit)
DB:Oracle11gXE

流れとしては、XEと別に独自のインスタンスを作り、そのインスタンス用の専用フォルダを作っておき、XEで使用されるDBFやログファイル等をRMANで転送するだけ。
１．～４．あたりは以前の記事(Oracle11gXE)とやってることが被るので、少々説明を省き気味(汗)。詳細はOracle11gXEの方を覗いて頂けると幸いです。


■１．複製先インスタンスのwindowsサービスの作成

今回は複製先インスタンス名を「XE2」とする。Windowsではインスタンスのwindowsサービスを作成しないといけないのでoradim.exeを使って作成。コマンドプロンプト上で

>oradim.exe -NEW -SID XE2

を実行。


■２．フォルダの作成

複製先インスタンスが使用するフォルダを作成。フォルダ構成はXEと同じにしたいので以下の場所にフォルダを作成。(ちなみにOracle11gXEがC:\直下にインストールされてることを前提とした上での絶対path)

・C:\oraclexe\app\oracle\oradata\XE2
・C:\oraclexe\app\oracle\admin\XE2\adump
・C:\oraclexe\app\oracle\admin\XE2\dpdump
・C:\oraclexe\app\oracle\fast_recovery_area\XE2\ARCHIVELOG
・C:\oraclexe\app\oracle\fast_recovery_area\XE2\ONLINELOG


■３．initファイル(パラメータファイル)、PWD(パスワードファイル)の作成

複製先インスタンスのinitファイルを作成する(場所はC:\oraclexe\app\oracle\product\11.2.0\server\database)。XEインスタンスの静的initファイル(initXE.ora)が作られていない場合は、先にXEに接続してcreate pfile from spfileを実行してinitファイルを作成しておく。

initXE.oraをコピペして、ファイル名をinitXE2.oraにする。
initXE2.oraを以下のように追加修正

*.audit_file_dest='C:\oraclexe\app\oracle\admin\XE2\adump'
*.compatible='11.2.0.0.0'
*.control_files='C:\oraclexe\app\oracle\oradata\XE2\control.dbf'
*.db_name='XE2'
*.db_file_name_convert='C:\ORACLEXE\APP\ORACLE\ORADATA\XE\','C:\ORACLEXE\APP\ORACLE\ORADATA\XE2\'
*.log_file_name_convert='C:\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\','C:\oraclexe\app\oracle\fast_recovery_area\XE2\ONLINELOG\'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\fast_recovery_area'
*.diagnostic_dest='C:\oraclexe\app\oracle\.'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=XE2)(DISPATCHERS=1)'
*.job_queue_processes=4
*.max_shared_servers=8
*.memory_target=1024M
*.open_cursors=300
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.statistics_level='typical'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

オレンジ部分に記述されてるconvert句が重要。複製元のインスタンスのDBFとログファイルを指定して、複製先のフォルダを指定する。(複製元のinitXE.oraに記述する必要は無し)

また同じ場所にあるパスワードファイル(PWDXE.ora)もコピペしてファイル名を「PWDXE2.ora」にして置いておく（面倒なので複製元と同じ管理者パスワードにする為)。


■４．listener.ora、tnsnames.oraへの追加

複製先インスタンスで接続できるように、リスナーとネットサービス名を追加しておく。

・listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) 
      (SERVICE_NAME = XE)
    )
    (SID_DESC =
      (SID_NAME = XE2)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) 
      (SERVICE_NAME = XE2)
    )
   
水色の部分を追加。

・tnsnames.ora
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ****)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
XE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ****)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE2)
    )
  )
 
水色の部分を追加。****は自分の環境かlocalhostで入力。(XEのネットサービス名を作っていない場合はそちらも追加)

listener.oraは変更後にlsnrctl reloadしておく。


■５．双方のインスタンスの起動

コマンドプロンプトでsqlplusを起動して、複製元のインスタンス(XE)と複製先インスタンス(XE2)を起動しておく。

・複製元インスタンス(XE)
>set oracle_sid=xe
>sqlplus / as sysdba
SQL>startup

・複製先インスタンス(XE2)
>set oracle_sid=xe2
>sqlplus / as sysdba
SQL>startup nomount

複製先はnomount状態で起動。(エラーが無いことを確認)
複製元はmountかopen状態であれば良い。(上記はopenで起動)


■６．リカバリーマネージャで複製

５．まで終われば後はDBを複製するだけの作業。

まずはコマンドプロンプトで、リカバリーマネージャ(RMAN)を起動。

>rman target sys/********@xe auxiliary sys/********@xe2

targetは複製元のログイン情報、auxiliaryは複製先のログイン情報。
********の部分はXEをインストールしたときに設定したSYSDBAのパスワードを入力。

最後に複製コマンドを実行。

RMAN>duplicate target database to XE2;

「Duplicate Db」と表示されれば複製完了(SPFILEなども自動的に作られている)。
完了後、複製先インスタンスはOPEN状態になっているので、念の為、複製先インスタンスのDBFとログファイルの入出力先を確認すると・・・



正常にXE2フォルダを指している。複製なので当然管理用GUIツールなども



このように使用できる。(パラメータがXE2を指しているのを確認。XEインスタンスと被るのでdbms_xdb.sethttpportでポートを変えておくと良いかも)
後は複製先インスタンスの起動・停止バッチをXEからコピペして作成しておくと良さそう。

ちなみにXEだけでなく、以前の記事(Oracle11gXE)で書いた独自で作ったインスタンスについても複製できる(というかそっちの方がやりたかった)。一つ気になったのはstandbyインスタンスにしても作成できたのでOracle11gXEでDataGuard機能が有効みたい(？)。

補足：
expdpなどで使うdirectoryの「DATA_PUMP_DIR」のパスだけXEと同じになっているので、以下をSYSで実行して切り替える。

SQL>create or replace directory DATA_PUMP_DIR as 'C:\oraclexe\app\oracle\admin\XE2\dpdump\';