Oracleデータインポート・エクスポート

Oracleのインポート/エクスポート

単にタブ区切りでデータを取得したい場合などに。

まずは、データの取得(export)

#!/bin/sh

USER_PASS=user/pass@db

sqlplus $USER_PASS <<EOF
set echo off;
set heading off;
set wrap off;
set linesize 999;
set serveroutput on;
spool $HOME/tmp/exp.tsv;

BEGIN
    DBMS_OUTPUT.ENABLE_(NULL);
    FOR r IN (
        SELECT 
            col1
          , col2
          , col3
        FROM test_tbl
    ) LOOP
    DBMS_OUTPUT.PUT_LINE (
                     r.col1
        || CHR(9) || r.col2
        || CHR(9) || r.col3
    );
    END LOOP;
END;
/
exit;
EOF

#### EOF

sqlldr用のctlファイル
imp.ctl

load data
infile '$HOME/tmp/exp.tvs'
append into table test_tbl
fields terminated by X'09'
trailing nullcols
(
    col1
  , col2
  , col3
)

データインポート用の実行スクリプト

#!/bin/sh

USER_PASS=user/pass@db

sqlplus $USER_PASS <<EOF
DROP TABLE test_tbl;
CREATE TABLE test_tbl;
    col1 NUMBER
  , col2 VARCHAR2(10)
  , col3 VARCHAR2(10)
);
exit;
EOF

sqlldr $USER_PASS COLTROL=$HOME/tmp/imp.ctl

#### EOF

Oracle表領域の使用率表示

DB中の全表領域の利用率をSQLで表示。cron等のバッチで実行してメールでお知らせまでやってるので、全3ファイルから。

get_tablespace_name.sql	全tablespace名の取得
get_tablespace_df.sql	指定tablespaceの利用率取得
get_tablespace_df.sh	上記SQLを実行するスクリプト

get_tablespace_name.sql

set head off;
set echo off;

spool /home/oracle/tools/get_tablespace.tmp;

select TABLESPACE_NAME from dba_tablespaces;

exit;

get_tablespace_df.sql

-- set head off;
set linesize 99;
set pagesize 100;
set echo off;

spool /home/oracle/tools/show_tablespace_df.tmp;

set head off;
SELECT 'TABLESPACE Report' FROM dual;
-- SELECT TO_CHAR (sysdate, 'YYYY/MM/DD') FROM dual;

set head on;

SELECT sumdf.tablespace_name "Tablespace",
       TO_CHAR(sumdf.total_bytes, '999,999,999,990') "size(bytes)",
       TO_CHAR(sumdf.total_bytes - sumfs.free_bytes, '999,999,999,990') "Used",
       TO_CHAR(sumfs.free_bytes, '999,999,999,990') "Avail",
       TO_CHAR(100 - ((nvl(sumfs.free_bytes, 0) / sumdf.total_bytes) * 100), '990.99') || '%' "%"
FROM (SELECT df.tablespace_name, sum(df.bytes) total_bytes
      FROM dba_data_files df
      GROUP BY df.tablespace_name) sumdf
LEFT OUTER JOIN (SELECT fs.tablespace_name, sum(fs.bytes) free_bytes
                 FROM dba_free_space fs
                 GROUP BY fs.tablespace_name) sumfs
ON (sumdf.tablespace_name = sumfs.tablespace_name)
STR_TBLSPCS

exit;

get_tablespace_df.sh
最初のブロックで、全tablespace名を取得。次のブロックで、取得したtablespace名毎の利用率を取得。最後のブロックはおまけで、レポートをメールで通知。 nagios等利用できれば、利用率が80%超えたらアラート投げるとか。

#!/bin/sh

SQLPLUS=$HOME/database/product/10.1.0/db_1/bin/sqlplus
UP="sys/oracle  as sysdba"

#### --------------------------------------------------------------------------
#
#### --------------------------------------------------------------------------

$SQLPLUS $UP @get_tablespace_name.sql

grep -v "rows selected" get_tablespace.tmp > get_tablespace.log
rm -f get_tablespace.tmp


TBLSPCS=`cat get_tablespace.log`

STR_TBLSPCS=""

for TBLSPC in $TBLSPCS
do
    if [ "X" != "X$TBLSPC" ]; then
        if [ "X" != "X$STR_TBLSPCS" ]; then
            STR_TBLSPCS=`echo $STR_TBLSPCS,\'$TBLSPC\'`
        else
            STR_TBLSPCS=`echo \'$TBLSPC\'`
        fi
    fi

done
# echo "*** STR_TBLSPCS=[$STR_TBLSPCS]"

#### --------------------------------------------------------------------------
#
#### --------------------------------------------------------------------------
rm -f get_tablespace_df.sql.tmp
while read REC
do
    if [ "XSTR_TBLSPCS" = "X$REC" ]; then
        echo "WHERE sumdf.tablespace_name in ($STR_TBLSPCS);" >> get_tablespace_df.sql.tmp
    else
        echo "$REC" >> get_tablespace_df.sql.tmp
    fi

done < get_tablespace_df.sql

$SQLPLUS $UP @get_tablespace_df.sql.tmp
grep -v "rows selected" get_tablespace_df.tmp > get_tablespace_df.log
rm -f get_tablespace_df.sql.tmp get_tablespace_df.tmp

#### --------------------------------------------------------------------------
#
#### --------------------------------------------------------------------------
DATE=`date "+%Y/%m/%d"`
HOST=`hostname -s`
HEAD="Oracle tablespace report ($HOST - $DATE)"
BODY=`cat get_tablespace_df.log`
TO="[e-mail]"
FROM="[e-mail]"

SUBJECT="$HEAD
From: $FROM";
/bin/echo "$BODY" | /bin/mail -s "$SUBJECT" "$TO"

#### EOF

Oracleインストール

追加。
Oracleインストール

トライアル版の取得と事前準備OTNのサイトからお試し版を取得。今回は10g。で、展開。

$ cpio -idmV < ship.db_Disk1.lnxx86-64.cpio
$ cpio -idmV < ship.db_Disk2.lnxx86-64.cpio

アカウント：oracle、グループ：oinstall、dba を作成。oracleアカウントにはプライマリーグループにoinstall、追加グループにdbaを指定。

$ useradd –g oinstall –G dba oracle

oracleアカウントでinstall実行

$ ./runInstaller -ignoreSysPrereqs [-silent -responseFile filename]

パラメータについて

-ignoreSysPrereqs	CentOSなどにinstall時に｢インストール可能なシステムではありません｣的なメッセージが出る場合があります。そんな時はこのオプションをつけてsystem checkを回避。
-silent	XなどGUI環境が使えない場合に利用します。 -silent -responseFile filename という形式で指定します。あらかじめレスポンスファイルというGUIで指定入力する内容を記載したファイルを準備しておきます。サンプルはDisk1/responseにあります。

DB構築は追々。


Web系では一般的にMySQLが主流ですが、データファイルの取り扱いをOracleのようにしないのをよく見ます。

具体的にいうと、Oracleの場合はデータファイルをあらかじめ使用量を見積もりアロケーション/自動拡張Offの運用が一般的ですが、MySQLでInnoDBを利用する場合、ほとんどのDBでautoextendオプションをつけたまま(defaultが付いてるから)にするようです。

ファイルのフラグメンテーションや1blockの効率的な使用を考えるとautoextendはoffで行くのがいいのでは？と思いますが、分散化させる場合でもシェアードディスクとシェアードナッシングになるしそこまで考えなくでも問題ないのかも。

Oracleが自動拡張非推奨の考えを示していた頃からするとDiskもずいぶん進化したしね。

MySQLでもDBの再起動なしにデータファイルの追加/変更/削除ができるともっと便利になるんだろうけどな。