カテゴリー: Oracle

投稿日:

【Oracle】SQLPlusで空白除去

OracleのSQLPlusでSELECT結果をspoolでファイル出力した際、
CHAR型やVARCHAR2型の出力結果がサイズにあうように自動で空白埋めされてしまい、
想定結果ととても比較し難い。

「SET TRIMSPOOL ON」にしても空白が削除されないのは、
これが「行末のトリム処理を制御する」からのようです。
「項目ごとのトリム処理を制御する」なら便利だったのに。

色々調べてもSQLPlusの設定だけでやるのは無理なようで、
結局下記のようにパイプでつなげることにしました。

SELECT 項目1 || 't' || 項目2 || 't' || 項目3 FROM テーブル名;
投稿日:

【Oracle】実行計画SQLPlus

Oracleの実行計画を取得した時のメモ。

sqlplus DBユーザ名/DBパス名@サービス名

SQLPlusだけで実行計画を取得します。
通常のSQLPlusではautotraceをoff、termoutをonにすると綺麗に取得できます。

-- ログ初期設定
set autotrace on
set echo off
set timing on
set time on
set termout off
set feedback 1
set colsep 't'
set pagesize 30000
set linesize 30000
set trimspool on

col PLAN_PLUS_EXP format a200;

spool trace_test.log;

prompt ======================
prompt  実行計画
prompt ======================
prompt 【対応前】
SELECT XXXX FROM TEST_TBL;

prompt ======================
prompt  実行計画
prompt ======================
prompt 【対応後】
SELECT YYYY FROM TEST_TBL;

set autotrace off
spool off;

2回以上同じSQLを実行する際は以下のコマンドを実行し、
キャッシュと共有プールから履歴を削除すると良い。
ただし、他の人がSQLを実行していると影響がでてしまうので、
環境を占有していることが条件となる。

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
投稿日:

【Oracle】実行トレース取得

Oracleの実行計画を取得した時のメモ。

sqlplus DBユーザ名/DBパス名@サービス名

SQLPlusにログイン後に測定したいSQLを投げる。

ALTER SESSION SET SQL_TRACE=TRUE;
SELECT COMPANY_CD, USER_ID, SLIP_NO FROM TEST_TBL;
ALTER SESSION SET SQL_TRACE=FALSE;
exit;

tkprofコマンドでトレースファイルから実行計画を取得する。

# DIAGNOSTIC_DESTの初期化パラメータによる
cd $ORACLE_HOME/diag/rdbms/DB名/インスタンス名/trace
# 実行計画を取得
tkprof トレースファイル名 出力ファイル名 explain=DBユーザ名/DBパス名

tkprofはオプションでパフォーマンスの悪い順に並べたりできるようです。

投稿日:

【Oracle】データエクスポートとインポート

Oracleデータ復旧をメモしたので、
ついにでOracleデータエクスポートとインポートメモ。

#!/bin/sh
# 環境設定
DB_INF="DBユーザ名/DBパスワード@サービス名"
# エクスポート
exp ${DB_INF} FILE=[テーブル名].dmp LOG=[テーブル名].log TABLES=([テーブル名]) ROWS=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y STATISTICS=NONE TRIGGERS=N QUERY="WHERE [カラム名] = '[値]' "

#!/bin/sh
# 環境設定
DB_INF="DBユーザ名/DBパスワード@サービス名"
# インポート
imp ${DB_INF} FILE=[テーブル名].dmp LOG=[テーブル名].log TABLES=([テーブル名]) ROWS=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y STATISTICS=NONE

オプション一部整理。

  • 実行モード
    FULL／OWNER／TABLES

    FULL：データベース指定
OWNER：ユーザ指定
TABLES：テーブル指定
  • 移行対象
    ROWS／INDEXES／GRANTS／CONSTRAINTS／TRIGGERS／STATISTICS／TRIGGERS

    ROWS：表データ移行フラグ、Nにすると表定義のみ(デフォルト：Y)
INDEXES：索引移行フラグ(デフォルト：Y)
GRANTS：権限移行フラグ(デフォルト：Y)
CONSTRAINTS：制約移行フラグ(デフォルト：Y)
STATISTICS：統計情報移行フラグ、移行したくない場合はNONEを指定
TRIGGERS：トリガー移行フラグ、エクスポート時のみ指定可能(デフォルト：Y)
  • その他

    QUERY：エクスポート条件条件
投稿日:

【Oracle】データ復旧

Oracleのデータ復旧メモ。
TEST_TBLからデータを削除してコミットしてしまったけど復旧したい。

１．バックアップしたデータからの復旧(全てのバージョン)
２．ログ・マイナーを使用した復旧(Oracle8i以降)
３．フラッシュバック･クエリを使用した復旧(Oracle9i以降)

フラッシュバック・クエリの例を記載します。

CREATE TABLE TMP_TEST_TBL AS SELECT COMPANY_CD, USER_ID, SLIP_NO,
TO_DATE(TO_CHAR(UPD_DT, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') AS UPD_DT
FROM TEST_TBL AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '100' MINUTE);

100分前の状態に戻します。
「AS OF TIMESTAMP」句からフラッシュバック･クエリとなっている。
「SYSTIMESTAMP」はシステムの日時を戻す関数なので、
「SYSTIMESTAMP」から「INTERVAL ‘100’ MINUTE」を引くことで「今から100分前」を表している。
「INTERVAL」には「MINUTE」の他にも「HOUR」や「DAY」を扱えるが、遡るのに限度はあるようです。

INSERT INTO TEST_TBL SELECT COMPANY_CD, USER_ID, SLIP_NO,
TO_DATE(TO_CHAR(UPD_DT, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') AS UPD_DT
FROM TMP_TEST_TBL

これで助かったことがあるので、メモ。

投稿日:

【Oracle】実行中のセッションとSQL

実行中のセッションとSQLを確認します。

  • 実行中のセッション

    SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM, SQL_ID FROM V$SESSION WHERE USERNAME IS NOT NULL AND SID != USERENV('SID');
  • 実行中のSQL

    SELECT SQL_TEXT FROM V$SQLTEXT WHERE SQL_ID='実行中のセッションで確認したSQLIDを指定する。';
投稿日:

【Oracle】インスタンスの起動停止とリスナーの起動停止

Oracleのインスタンスの起動停止とリスナーの起動停止の方法です。

  • インスタンス起動

    su - oracle
※パスワードなし
sqlplus / as sysdba

    startup

    以下の内容が画面に出力されます。

    ORACLEインスタンスが起動しました。

Total System Global Area  413372416 bytes
Fixed Size                  2227040 bytes
Variable Size             306185376 bytes
Database Buffers          100663296 bytes
Redo Buffers                4296704 bytes
データベースがマウントされました。
データベースがオープンされました。
  • リスナー起動

    su - oracle
※パスワードなし
lsnrctl start listener

    listenerを省略するとデフォルトリスナーを指定したことになります。

  • リスナー停止

    su - oracle
※パスワードなし
lsnrctl stop listener

    listenerを省略するとデフォルトリスナーを指定したことになります。

  • インスタンス停止

    su - oracle
※パスワードなし
sqlplus / as sysdba

    shutdown

    以下の内容が画面に出力されます。

    データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
  • インスタンス停止(セッションがすべてINCACTIVEになるのを待つ場合)
    immediateをつけることで以下の動作となる。

    新しい接続は許可されず、新しいトランザクションは開始できません。
すべての未コミットのトランザクションは、ロールバックされます。
現在データベースに接続しているユーザーが切断されるのを待機しません。
既存の接続を全て切断しますが、SQL実行中やロールバック中などのアクティブなセッションが存在すると、その完了を待機します。
1時間たっても完了しない場合、「ORA-01013」が発生する。

    「ORA-01013」が発生すると既に新規セッションを受け付けていないため、
    どのセッションが残っているのか調査が難しいため、セッションが残っているかを確認してからの方が良い。

    shutdown immediate
  • インスタンス停止(ACTIVEやKILLEDセッションが合っても強制終了したい場合)

    shutdown abort
投稿日:

【Oracle】Oracle導入

  • Oracle取得
    Oracleを導入したくなったので、VMware上のCentOSに導入します。
    導入するバージョンは「Oracle Database XE」です。
    Oracle 11gの無料版でLinuxは64bit版のみのようです。

    インストールするのにCentOSに下記が必要。

    glibcは2.3.4-2.41以上
makeは3.80以上
binutilsは2.16.91.0.5以上
gccは4.1.2以上
libaioは0.3.104以上
  • Oracle導入
    yumコマンドでインストール済のパッケージを全て確認します。

    yum list installed

    glibc.x86_64            2.12-1.107.el6_4.5
make.x86_64             1:3.81-20.el6
binutils.x86_64         2.20.51.0.2-5.36.el6
gcc.x86_64              4.4.7-3.el6
libaio.x86_64           0.3.107-10.el6

    問題なしということで、インストール作業を進めます。
    このOracleは外とは繋がないので、専用ユーザは作成せずにいきました。

    Linux64bit版のzipファイルをCentOSにアップロードします。

    cd /home/ユーザ名/oracle
unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

    Disk1ディレクトリの中に解凍されているので、
    中にあるrpmパッケージをインストールします。

    cd Disk1
rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

    準備中...                ########################################### [100%]

This system does not meet the minimum requirements for swap space.  Based on
the amount of physical memory available on the system, Oracle Database 11g
Express Edition requires 1976 MB of swap space. This system has 1854 MB
of swap space.  Configure more swap space on the system and retry the
installation.

エラー: %pre(oracle-xe-11.2.0-1.0.x86_64) scriptlet failed, exit status 1
エラー:   install: スクリプト %pre の実行に失敗しました (2)。oracle-xe-11.2.0-1.0 をスキップします。

    失敗・・・。
    スワップ領域が不足しているようです。。
    スワップ領域を調べてみることに。

    swapon -s

    既にいくつかスワップ領域を消費していました。
    ずっとサスペンドだったからでしょうか。
    再起動するとスワップ領域の消費が0になりました。
    というわけで、再度インストールします。

    cd Disk1
rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

    準備中...                ########################################### [100%]
   1:oracle-xe              ########################################### [100%]
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

    無事インストールできました。

  • Oracle導入後の設定
    次に「/etc/init.d/oracle-xe configure」を実行しろと記載があるので、実行します。

    /etc/init.d/oracle-xe configure

    ポート：XXXX(デフォルトの8080は他と競合しそうなので変えました。)
    リスナー：1522(気分で変えました。)
    DBパスワード：パスワード
    OS起動時に自動起動：y

    Oracleの環境変数は下記で確認できます。

    view /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

    Oracleを使うユーザに適用しておきます。

    vi /etc/profile

    下記内容を最終行に追加
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
  • Oracle導入後の動作確認(サーバ側)
    TeraTermを再起動し、「/etc/profile」を読み込みなおし、
    動作確認を行います。
    インストール時にOracleユーザとしてsystemが作られています。

    sqlplus system/パスワード@サービス名

    SQLに接続できれば成功です。
    インストール時のファイルを削除しておきます。

    rm -fr /home/ユーザ名/oracle

    無料版なのにプロシージャまで試せるようなので、なかなか満足です。
    インストール手順詳細は下記参照。
    http://docs.oracle.com/cd/E36055_01/install.112/b66469/toc.htm#CIHHJEHF

  • Oracle導入後の動作確認(クライアント側)
    VMwareをインストールしているPCにもOracleクライアントをインストールし
    sqlplusで接続できるように
    サーバ側のリスナーポートを開放しておきます。

    iptables -I INPUT 23 -p tcp -m tcp --dport 1522 -j ACCEPT

    23は「iptables -L –line-numbers」で確認し、
    挿入したい行番号の次番号を指定しました。