Oracleで元表から新しい表を作成する方法

Oracle

データベース・リンクの作成

データベース・リンクとは2つの物理的なデータベース・サーバー間の接続のことで、これにより、クライアントからそれらのサーバーに1つの論理データベースとしてアクセスできるようになる。
異なるデータベース間で、マテリアライズド・ビューを作成するときに必要である。また、異なるデータベース上の表から、直接的に新しい表を作成する場合にも便利である。


データベース・リンクの種類

  • 接続ユーザー・リンク
    • ユーザーはユーザー自身として接続。ユーザーにはローカル・データベースのアカウントと同じユーザー名を持つリモート・データベースのアカウントが必要。
  • 固定ユーザー・リンク
    • ユーザーは、リンク内で参照されるユーザー名とパスワードを使用して接続。
  • 現行ユーザー・リンク
    • ユーザーは、グローバル・ユーザーとして接続。ローカル・ユーザーは、ストアド・プロシージャのコンテキスト内ではグローバル・ユーザーとして接続できる。



データベース・リンクのタイプ

  • プライベート
    • 所有者はリンクを作成したユーザー。ローカル・データベースの特定のスキーマにリンクを作成。プライベート・データベース・リンクの所有者またはスキーマ内のPL/SQLサブプログラムのみが、このリンクを使用して、対応するリモート・データベース内のデータベース・オブジェクトにアクセス可。
  • パブリック
    • 所有者はPUBLICと呼ばれるユーザー。データベース全体にわたるリンクを作成。データベース内のすべてのユーザーとPL/SQLサブプログラムが、パブリック・リンクを使用して、対応するリモート・データベース内のデータベース・オブジェクトにアクセス可。
  • グローバル
    • 所有者はPUBLICと呼ばれるユーザー。ネットワーク全体にわたるリンクを作成。Oracleネットワークでディレクトリ・サーバーを使用すると、そのディレクトリ・サーバーは、ネットワーク内のすべてのOracle Databaseに対するグローバル・データベース・リンクを（ネット・サービス名として）自動的に作成および管理する。どのデータベースのユーザーとPL/SQLサブプログラムでも、グローバル・リンクを使用して、対応するリモート・データベース内のオブジェクトにアクセス可。



データベースリンクを作成する権限の付与

  • CREATE SESSION権限
    • リモート・データベースを参照するために必要
  • CREATE DATABASE LINK権限
    • プライベート・データベース・リンクの作成に必要
  • CREATE PUBLIC DATABASE LINK権限
    • グローバル・データベース・リンクの作成に必要



ローカル・ネーミング・パラメータの追加

  • $ORACLE_HOME/network/admin/tnsnames.oraを編集して、データベース・リンクの対象となるサーバーを追加する。
  • 失敗する場合は以下のように設定
    • ネット・サービス名を '.'（ドット）でつながれた名前にしない。
    • ネット・サービス名とサービス名を一致させる。
    • 初期化パラメータGLOBAL_NAMESをfalseに設定する。

以下の例では、固定ユーザーでプライベートなデータベース・リンクを作成している。

  • ローカル・ネーミング・パラメータの追加

    • $ORACLE_HOME/network/admin/tnsnames.ora

[ネット・サービス名]=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=[アドレス])(PORT=[ポート番号]))
    (CONNECT_DATA=(SERVICE_NAME=[サービス名]))
  )

  • データベース・リンクの作成

    • CREATE DATABASE LINK databaselink_name
CONNECT TO [ユーザー名] IDENTIFIED BY [パスワード]
USING '[ネット・サービス名]';



元表からマテリアライズド・ビューを作成

マテリアライズド・ビューとは「実体のあるビュー」のこと。


参考サイト



利用方法

  • リモート・データベース上に存在するデータをローカル・データベース上に定期的にコピーする目的で使用される。スナップショットと同じもの。
  • ローカル・データベース上のデータの集計や結合処理を高速化するために使用される。



権限

  • GRANT CREATE MATERIALIZED VIEW TO [ユーザー名];



リフレッシュ

  • 完全リフレッシュ（全データ）
    • REFRESH complete
  • 高速リフレッシュ（差分のみ）
  • 高速リフレッシュ可能なら高速、そうでないなら完全（デフォルト）
    • REFRESH force
  • リフレッシュなし（手動でのリフレッシュ指示も無効）
    • never REFRESH



再計算タイミング

  • コミット時
    • on commit
  • 一定間隔
    • start with '日時' next 次の計算日時を求める式
    • start with '2005/2/6' next sysdate + 1
    • 以下のSQLで実行タイミングを確認できる。
    • SELECT last_date,last_sec,next_date,next_sec,interval,what FROM user_jobs;
  • 指定時（デフォルト）
    • on demand



手動で計算し直す



クエリーリライト機能

以下の例では、"REFRESH fast on commit"を指定することで、元表の更新がリアルタイムにマテリアライズド・ビュー（スナップショット）に反映されている。

CREATE MATERIALIZED VIEW [マテリアライズド・ビュー名]
REFRESH fast on commit
AS SELECT *
FROM [元表名];



元表からテーブルを作成

元表からテーブルを作成するときに、プライマリキーも設定できる。

CREATE TABLE [表名] AS SELECT * FROM [元表名];

ALTER TABLE [表名] ADD CONSTRAINT [表名_PK]
PRIMARY KEY ([プライマリキー1],[プライマリキー2],…);

データの更新は"TRUNCATE"後に"INSERT INTO SELECT"でもよい。


データの挿入

"INSERT INTO"の構文は以下の通り。

INSERT INTO [表名] ([列名1],[列名2],…)
VALUES (
    [値|式|SELECT文|問い合わせ]…
);
または、
INSERT INTO [表名] ([列名1],[列名2],…)
[SELECT文|問い合わせ];



インラインビューを使った更新

deptnoが'100'所属の全員のusernameに「氏」を追加する。インラインビューを使った更新は、WHERE句に同じ副問い合わせを繰り返し記述するよりもパフォーマンスがよいことが多い。

UPDATE (
    SELECT
        deptno, username
    FROM user_master
    WHERE deptno = '100'
)
SET
    username = username || '氏';
/*
こちらの方がパフォーマンスが劣る
WHERE
    deptno = '100';
*/



テーブルのインポート／エクスポートの方法

ダンプ・ファイルの作成に使用したexpより前のバージョンのimpは使用できないので、注意すること。
以下のコマンドをコマンドラインから直接実行するか、ファイルに記述しshスクリプトとして実行する。
テーブルを「ora.dmp」にエクスポートする
各種パラメータは「par.txt」から読み込むこともできる。

exp usr02/passwd file=ora.dmp tables=table1,table2



ダンプファイル「ora.dmp」をインポートする。
各種パラメータは「par.txt」から読み込むこともできる。

imp usr01/passwd file=ora.dmp parfile=par.txt

パラメータファイル「par.txt」の記述例

fromuser=usr02
touser=usr01
tables=table1,table2