カテゴリー: Database

[関係データベース]スーパーキー、候補キー、主キー、代替キー

スーパーキーと候補キーの違いがよくわからなかったので、関係データベースにおけるキーについてまとめてみました。

用語について

関係データベースの用語で説明しますが、データベースでどの用語に対応するかについてまとめておきます。

  • 関係（リレーション） → 表（テーブル）
  • タプル → 行（レコード）
  • 属性 → 列（カラム）
  • 空値 → NULL値

各キーの概念の階層化

これから説明するスーパークラス、候補キー、主キー、代替キーは、より広い概念（上位）とより狭い概念（下位）といった階層構造の関係があります。

これらを図示すると以下のようになります。

スーパーキー
└── 候補キー
    ├── 主キー
    └── 代替キー

上位の概念から下位の概念の順に説明していきます。

各キーについて説明する際の具体的な例として、関係”社員”

社員(社員ID, 社員番号, 氏名, 部署, メールアドレス)

を使用します。

スーパーキー（Super Key）

今回説明するキーの中ではもっとも広い概念です。関係のタプルを一意に識別できるすべての属性や属性の組み合わせがスーパーキーです。後述の候補キーとは異なり、余分な属性が含まれてもよいという特徴があり、テーブル内で一意性を保証できる組み合わせであればすべてスーパーキーになります。

関係”社員”の場合、

  • {社員ID}
  • {社員番号}
  • {メールアドレス}
  • {氏名, メールアドレス}
  • {社員番号, 部署}
  • ・・・

などがスーパーキーになります。

候補キー（Candidate Key）

スーパーキーの中でも、最小限の属性で一意性を保証できるキーが候補キーです。余分な属性がないため、スーパーキーよりも狭い概念になります。後述の主キー／プライマリーキーとの大きな違いは空値を許すという点です。

関係”社員”の場合、

  • {社員ID}
  • {社員番号}
  • {メールアドレス}

が候補キーになります。たとえば、{社員ID, 氏名}はスーパーキーですが、氏名がなくても一意性を保証できるため、候補キーにはなりません。

主キー／プライマリーキー（Primary Key）

主キーは、候補キーの中から選んだ１つの候補キーです。前述の候補キーとの違いとして、空値（NULL値）を許しません。

関係”社員”の場合、

  • {社員ID}

が一意かつ空値（NULL値）を許さないため、主キーに適しています。

代替キー／代用キー（Alternate Key）

候補キーのうち、主キーとして選ばれなかったキーです。代替キーは主キーとして選ばれなかった候補キーであるため、空値を許します。

候補キーのうち、空値を許さないキーが1つしかなければ、そのキーが主キーになり、それ以外が代替キーになりますが、主キーとして選択可能なキーが複数ある場合はどれを主キーにするかは設計上の判断次第になるため、必ずこれが主キーになるといったルールがあるわけではありません。

関係”社員”の場合、

  • {社員番号}
  • {メールアドレス}

が代替キーになります。

ナチュラルキーとサロゲートキー

キーの分類には、前述の階層構造と直交する「ナチュラルキー」「サロゲートキー」という考え方があります。

ナチュラルキー／自然キー（Natural Key）

ナチュラルキーとは、データそのものに基づいて一意性を保証するキーです。すなわち、すでに現実世界で意味を持っているデータをプライマリーキーとして使用します。

関係”社員”の場合、

  • {社員番号}
  • {メールアドレス}

がナチュラルキーになります。今回の例にはありませんが、SSN（社会保障番号）、マイナンバー（個人番号）なども社員を一意に特定できるナチュラルキーになります。

サロゲートキー／代理キー（Surrogate Key）

一方、サロゲートキーとは、データベース内で独自に生成され、現実世界のデータとは直接の関係がない一意なキーのことです。主に整数値（自動増分のID）、GUID（グローバル一意識別子）、UUID（ユニバーサル一意識別子）などが使用されます。

関係”社員”の場合、

  • {社員ID}

がサロゲートキーになります。これについては少しわかりづらいので順を追って説明します。

現実世界の社員を分析した結果作成した関係”社員”が

社員(社員番号, 氏名, 部署, メールアドレス)

だったとします。当然ですが、企業においては社員番号で社員を一意に識別することができます。概念データモデルでは{社員番号}を主キーにすることで特に問題なさそうですが、次のケースを考えてみます。

まず、関係”社員”に

社員(社員番号, 氏名, 部署, メールアドレス, 入社年度)

のように属性入社年度を追加します。

次に定年退職後嘱託社員としてシステムに再登録することことを考えてみます。このとき、社員番号は変えずに入社年度は元々の入社年度の情報を残しつつ、嘱託社員として入社したときの入社年度を記録したいとしましょう。具体的には

('S001', '山田太郎', '総務部', 'taro@example.com', 1964)

という社員がいる場合、この社員が2024年に嘱託社員として再雇用された場合、

('S001', '山田太郎', '総務部', 'taro@example.com', 1964)
('S001', '山田太郎', '総務部', 'taro@example.com', 2024)

のようにデータを登録したい場合、社員番号は関係”社員”でタプルを一意に特定するキーとは使用できなくなります。（普通は嘱託社員として再雇用した場合、社員番号は変えることが多いと思いますが、ここでは変えない運用を想定しています）

この場合、

社員(社員番号, 連番, 氏名, 部署, メールアドレス)

のように、属性連番を追加して{社員番号, 連番}で一意になるようにすることもできますが、属性社員IDというタプルを一意に特定する属性を追加する方法もあります。この社員IDはタプルを一意に特定する以外の意味を持たないため、サロゲートキー（ナチュラルキーの代理で使われるキー）とよばれます。

まずはナチュラルキーから検討する

ここではデータベースの用語で説明します。

プロジェクトの方針によってはすべてのテーブルにサロゲートキーを設定する場合もありますが、ナチュラルキーを使いつつ、どうしてもナチュラルキーが使えない場合のみサロゲートキーを使用するという方針もあります。具体的には以下のような場合です。

  • ナチュラルキーではレコードを一意に特定できない
  • レコードインサート時にナチュラルキーが設定されないケースがある
  • 要件や処理の都合でナチュラルキーが一意にならない／一意にならない瞬間がある

主キーにすることで、NOT NULL制約と一意性制約が付与されるため、同じ値が存在しないことを保証できますが、前述のように社員番号ではなく社員IDを主キーとして使用すると、社員番号が重複してしまう可能性があるという問題が起こるため、1件だけ取得したつもりが2件以上返ってきてしまうといった問題につながります。対策として一意性制約を付与するという方法がありますが、意外と忘れがちだったります。

こういったことから、基本的にはナチュラルキーを主キーにできないかを検討し、どうしても難しい場合はサロゲートキーを使用するのがよいでしょう。

外部キー（Foreign Key）

今回の内容とは関係ありませんが、外部キーというキーもあります。

外部キーは、ある関係の候補キーの属性または属性の組を参照するキーです。外部キーは、関係間の関係性を定義し、データの整合性を保証する役割を果たします。

  • 関係間の関係性をあらわす
    ある関係が他の関係のデータを参照することによって、関係間にリレーションを形成します。
  • 一貫性の保証
    参照元の関係（親）に存在しないデータを参照することができなくなります。

まとめ

関係データベースでは、

  • スーパーキー
  • 候補キー
  • 主キー
  • 代替キー

というキーがあり、それらは階層化された関係を持ちます。また、これらのキーと直交する概念として、

  • ナチュラルキー
  • サロゲートキー

というものもあります。

これらの用語の違いを理解することで、キーの設計を円滑に進められるでしょう。

[SQL]INSERT SELECTでテーブルから直接INSERTする

先日投稿した記事のテーブルを使って、商品別売上実績のレコードが必ず存在するようにしてみます。

月末処理で売上平均金額を求める前に売上のない商品について0円の商品別売上実績レコードを作成する状況を想定しています。

ここではSELECTした結果をINSERTするINSERT SELECTを使ってデータを登録します。

INSERT SELECTとは

INSERT SELECTとは、INSERTするデータをSELECTで作成する手法のことです。例えば、テーブルAにテーブルBから抽出したレコードをそのままINSERTする場合、以下のように書きます。

INSERT INTO A
SELECT
  *
FROM
  B

この例では、テーブルAとテーブルBは同じカラムを持っており、テーブルBから全件抽出してそのままテーブルAにINSERTしています。このやり方はバックアップ対象と同じカラムを持つバックアップ用のテーブルを作成して、そこにテックアップ対象のレコードを全件抽出しておく、というときによく使います。

SELECT時に対象のカラムと行を絞り込む

今回実現したいのは、SELECTするテーブルとINSERTするテーブルのカラムは異なっています。これはSELECTするカラムをINSERTするカラムに合わせることで対応します。

もう一つ、すべてのカラムを抽出するのではなく、すでにINSERTするテーブルに存在している商品コードはINSERTする必要がない（INSERTすると一意制約違反となる）ため、商品テーブルにあって商品別売上実績テーブルにない商品コードだけINSERTするように対象レコードを絞り込みます。

作成したSQLは以下のようになります。

INSERT INTO sales_by_product
SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

INSERTするテーブルのカラムと同じカラムを抽出する

SELECTではINSERTするテーブルと同じカラムを抽出します。

SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1

INSERT先の商品別売上実績テーブルはproduct_codeとtotal_sales_amountを持っているため、これらのカラムだけになるように抽出しています。product_codeはproductsテーブルのカラムを使用し、total_sales_amountは今回やりたいことに合わせてデフォルト値の0を設定するようにしています。0 total_sales_amountのように固定値を直接記述することができるかどうかは使用しているRDBMSによって異なりますので注意してください。

INSERTするテーブルに存在していないレコードのみ抽出する

少しややこしいですが、NOT EXISTSを使ってsales_by_productに存在しない商品コードのみ抽出するようにします。

FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

SELECTしているproducts T1とNOT EXISTS内のSELECT文のsales_by_product T2をproduct_codeで結合することで、T1、T2双方に存在するsales_by_productが抽出できます。ということは、T1はNOT EXISTS以外に絞り込み条件を持たないので、すべてのT1のレコード（すべての商品）について、それぞれのレコードに対応するT2（商品別売上実績）を抽出してみて、それが存在しなかったら（NOT EXISTS）、SELECTされるということになります。

NOT EXISTS内のSELECTしている列が1となっていますが、これは行が抽出できていること自体にしか意味がないので、無駄なデータを抽出しないために適当な1という値を指定しているだけです。'x'とか3とかでも問題ありませんし、データ量のことを気にしないのであれば*でも構いません。

INSERTする前にSELECTだけを実行してみる

実際にINSERTをする前にINSERT部分以外のSELECT文単独で実行結果を確認しておきます。実務においてもまずはINSERTしたい形と同じかをSELECTだけで確認し、それが確認できたらINSERTするのが安全です。

SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

まずは、INSERT前の商品別売上実績テーブルを確認してみます。

SELECT * FROM sales_by_product;

=>
S001,50
S003,250
S004,350
S006,450

商品コードS002、S005が欠損していることがわかります。これが売上合計金額0で抽出できればINSERTしたい内容と一致します。

SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

=>
S002,0
S005,0

問題なさそうです。

では実際にINSERTまでやって、再度全件抽出してみます。

INSERT INTO sales_by_product
SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

SELECT * FROM sales_by_product;

=>
S001,50
S002,0
S003,250
S004,350
S005,0
S006,450

商品コードS002とS005が売上合計金額0で登録されていることが確認できました。

トランザクション機能を使って確認後にコミットする

今回は自動トランザクションでINSERTしたら自動コミットされるようにしています。

実務では手動トランザクションで明示的にコミットするまではDBにコミットされないようにしておくことで、INSERT後の確認でOKになるまでは作業のやり直しができるようにしておくことが重要です。

まとめ

INSERT SELECTを使ってあるテーブルから直接INSERTする方法について確認しました。バッチなどでのバルクインサートやテーブルのバックアップ・リストアなどINSERT SELECTを行うシーンは意外とあります。

INSERTしたい結果を抽出できるSELECT文を書いて、その前にINSERT INTO テーブル名を付けるということを覚えておけば、それほど悩むこともないと思います。細かい点を抜きにすればUPDATE SELECTとは異なり、RDBMSによって構文が変わるということもないので、どれかのRDBMSでやり方を覚えておけば他のRDBMSでもほぼそのまま適用できます。

[SQL]AVG関数の集計対象にNULLの値は含まれるのか？

データベーススペシャリスト試験の過去問で気になった問題があったので実機で試すことにしました。

現場ではこのような状況にならないようにSQLを書くので気にしたこともありませんでしたが、AVG関数の対象にNULLが含まれている場合、それは分子・分母から除外されて計算されます。

この点について実際にSQLを実行しながら確認していきます。

環境構築

動作確認用のMySQLのコンテナを作成します。

$ docker run -d --name test-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql:8.3

コンソールまたはGUIツールで接続し、データベースを作成します。

CREATE DATABASE test;
USE test;

今回、動作確認はすべてDataGripで行っていますが、その他のツールを使用しても結果は変わりません。

商品テーブルと商品別売上実績テーブルを作成する

問題文自体は記載しませんが、令和4年秋期 データベーススペシャリスト試験 午前IIの問7の問題となります。手元に問題集がある方はそちらを参照してください。

概要を簡単に説明すると、商品テーブルと商品別売上実績テーブルが提示され、それらに対して問題文で与えられたSQLを実行するとどのような結果が返ってくるかを問う問題です。

AVG関数やGROUP BYの働き、左外部結合LEFT OUTER JOINによる結合についての理解度を問う問題となっています。

問題文と同様の商品テーブルと商品別売上実績テーブルを作成します。

-- 商品テーブル
CREATE TABLE products (
    product_code VARCHAR(4), -- 商品コード
    product_name VARCHAR(255), -- 商品名
    product_rank CHAR(1), -- 商品ランク
    PRIMARY KEY (product_code)
);

商品テーブルには商品を一意に特定する商品コードに加え、商品を格付けするための商品ランクというカラムがあることがわかります。

-- 商品別売上実績テーブル
CREATE TABLE sales_by_product (
    product_code VARCHAR(4), -- 商品コード
    total_sales_amount integer, -- 売上合計金額
    PRIMARY KEY (product_code)
);

商品別売上実績には商品ごとに売上合計金額が格納されていることがわかります。この問題ではこの売上合計金額から売上平均金額を求めています。　

次に問題文と同じデータをインサートします。

-- 商品テーブル
INSERT INTO products VALUES ('S001', 'PPP', 'A');
INSERT INTO products VALUES ('S002', 'QQQ', 'A');
INSERT INTO products VALUES ('S003', 'RRR', 'A');
INSERT INTO products VALUES ('S004', 'SSS', 'B');
INSERT INTO products VALUES ('S005', 'TTT', 'C');
INSERT INTO products VALUES ('S006', 'UUU', 'C');

-- 商品別売上実績テーブル
INSERT INTO sales_by_product VALUES ('S001', 50);
INSERT INTO sales_by_product VALUES ('S003', 250);
INSERT INTO sales_by_product VALUES ('S004', 350);
INSERT INTO sales_by_product VALUES ('S006', 450);

登録したデータを確認しておきます。

まずは商品テーブルです。

SELECT * FROM products;

=>
S001,PPP,A
S002,QQQ,A
S003,RRR,A
S004,SSS,B
S005,TTT,C
S006,UUU,C

次に商品別売上実績テーブルです。すべての商品について売上実績があるというわけではないことがわかります。

SELECT * FROM sales_by_product;

=>
S001,50
S003,250
S004,350
S006,450

問題のSQLを確認する

まずは問題文のSQLを確認します。

SELECT
    AVG(T2.total_sales_amount) AS `売上平均金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
GROUP BY
    T1.product_rank
;

商品ランクごとに売上合計金額の平均を取得し、商品ランクがAのレコードのみ表示しています。

結果を確認するまえに、商品ランクがAのレコードを集計せずに抽出するとどうなるか確認してみます。

SELECT
    T1.product_code AS `商品コード`,
    T1.product_name AS `商品名`,
    T2.total_sales_amount AS `売上合計金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=>
S001,PPP,50
S002,QQQ,
S003,RRR,250

上記の実行すると、商品ランクAの商品コードS002の売上合計金額がNULLになっていることがわかります。

このSQLで抽出された3レコードについてAVG関数を適用したときに、分子は50+250=300であることははっきりしていますが、分母は2でしょうか？3でしょうか？

その答えを確認するために問題文のSQLを実行してみます。

SELECT
    AVG(T2.total_sales_amount) AS `売上平均金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
GROUP BY
    T1.product_rank
;

=> 150.0000

実行結果をみると、150となっているので、2で割っていることがわかります。すなわち、NULLでない値について平均を取得していることがわかります。

COUNT関数の動作を確認する

もし、AVG関数を使わずにSUM関数とCOUNT関数を使うとどうなるのでしょうか？

COUNT関数について確認してみましょう。

COUNT(*)、COUNT(T1.product_code)、COUNT(T2.product_code)、COUNT(T2.total_sales_amount)のそれぞれの結果を確認してみます。説明の都合上、COUNT(*)を最後に掲載します。

COUNT(T1.product_code)の結果

COUNT(T1.product_code)でカウントしてみると、3が返ってきます。

SELECT
    COUNT(T1.product_code) AS `商品数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 3

これは特に問題ないと思います。

COUNT(T2.product_code)の結果

次にCOUNT(T2.product_code)でカウントしてみます。

SELECT
    COUNT(T2.product_code) AS `売上実績数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 2

すると、2が返ってきます。商品コードS002に対応する商品別売上実績テーブルのレコードがないため、2とカウントされます。

COUNT(T2.total_sales_amount)の結果

COUNT(T2.total_sales_amount)についても確認します。

SELECT
    COUNT(T2.total_sales_amount) AS `売上実績数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 2

これも2と返ってきます。

COUNT(*)の結果

最後にCOUNT(*)でカウントしてみます。

SELECT
    COUNT(*) AS `レコード数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 3

これは駆動表である商品テーブルのレコード数が返ってきます。COUNT関数を使うとき、特に意識せずにCOUNT(*)を使いがちですが、何をカウントしたいのか明確にしないと意図しない結果が得られる場合がある点に注意が必要です。

そもそも売上平均金額はいくらなのか？

問題文で問われている内容から離れて、そもそも「売上平均金額」はいくらと計算されるのが正しいのでしょうか？

商品コードS002の売上合計金額を0と考えるなら、(50+0+250)÷3=100と計算されるのが正しいと思います。そう考えると、このデータの場合、NULLを含むカラムに対して安易にAVG関数を使うのは悪手のように思います。

100と計算されるにはどうすればいいでしょうか？

商品別売上実績のレコードが必ず存在するようにする

商品別売上実績に売上のない商品のレコードが存在しないことが問題ですので、すべての商品のレコードが存在する（売上実績がなければ売上合計金額は0になる）ようにするのが解決方法となります。

必ず存在するようにデータが登録されていれば、LEFT OUTER JOINではなくINNER JOINで結合できるようになりますが、テスト環境のようなデータが十分にメンテナンスされていない環境では正しく取得できない場合がある点に気をつける必要があります。

NVL関数を使用してNULLを0とみなす

今回の問題文のテーブルおよびデータで運用するなら、NVL関数を使ってNULLを0として計算対象に含める方法がよいでしょう。MySQLにはNVL関数がないため、IFNULL関数を使います。

SELECT
    AVG(IFNULL(T2.total_sales_amount, 0)) AS `売上平均金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
GROUP BY
    T1.product_rank
;

=> 100.0000

AVG関数に渡す前にNVL関数（IFNULL関数）でNULLを0に変換することで売上合計金額÷ランク別商品数で平均を求めることができるようになります。

まとめ

NULLを含むAVG関数およびCOUNT関数のふるまいについて確認しました。

「平均」と一口に言ってもNULLを集計に含めるのかどうかによって計算結果が変わります。

  • 「平均」の計算方法についての正しい仕様を確認する
  • 結合先が必ず存在するのか存在しないことがあるのかを確認する
  • 集計にNULLが含まれることがあるのであれば、NULLを含む場合の集計が正しいことを確認する

といった基本的な点をしっかりと確認していくことが重要です。

[Oracle]WITH句で階層問い合わせを行う

OracleではWITH句を使って階層問い合わせを行うことができます。

サンプルデータ

説明として、階層を持つフォルダを管理するFOLDER_TBLとフォルダに格納するファイルを管理するFILE_TBLを用意します。

CREATE TABLE FOLDER_TBL (
  FOLDER_ID NUMBER(10) NOT NULL,
  PARENT_ID NUMBER(10),
  FOLDER_NAME VARCHAR2(255) NOT NULL,
  PRIMARY KEY (FOLDER_ID),
  FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER_TBL(FOLDER_ID) ON DELETE CASCADE
);

FOLDER_TBLはフォルダを識別するためのFOLDER_IDとあるフォルダとその上位フォルダを紐づけるためのPARENT_IDを持っています。PARENT_IDは最上位フォルダの場合はNULLになります。

CREATE TABLE FILE_TBL (
  FILE_ID NUMBER(10) NOT NULL,
  FOLDER_ID NUMBER(10) NOT NULL,
  FILE_NAME VARCHAR2(255) NOT NULL,
  FILE_SIZE NUMBER(10),
  PRIMARY KEY (FILE_ID),
  FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER_TBL(FOLDER_ID) ON DELETE CASCADE
);

ファイルはFILE_IDによって識別され、格納先のフォルダに紐づけるためのFOLDER_IDを持っています。FOLDER_IDはNULLになることはなく、必ずどこかのフォルダに紐づきます。

-- FOLDER_TBLテーブルにデータを登録するSQL
INSERT INTO FOLDER_TBL (FOLDER_ID, parent_id, FOLDER_NAME) VALUES (1, NULL, 'Folder 1');
INSERT INTO FOLDER_TBL (FOLDER_ID, parent_id, FOLDER_NAME) VALUES (2, 1, 'Folder 1-1');
INSERT INTO FOLDER_TBL (FOLDER_ID, parent_id, FOLDER_NAME) VALUES (3, NULL, 'Folder 2');

-- FILE_TBLテーブルにデータを登録するSQL
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (1, 1, 'file1.txt', 100);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (2, 1, 'file2.txt', 200);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (3, 2, 'file3.txt', 150);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (4, 2, 'file4.txt', 300);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (5, 3, 'file5.txt', 50);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (6, 3, 'file6.txt', 250);

サンプルデータを用意しました。

FOLDER_TBLは以下のようなデータとなっています。最上位階層は二つ、うち一つにはサブフォルダがあります。

FOLDER_IDPARENT_IDFOLDER_NAME
1NULLFolder 1
21Folder 1-1
3NULLFolder 2

FILE_TBLは以下のようなデータになっています。各フォルダに2ファイルずつファイルが格納されています。

FILE_IDFOLDER_IDFILE_NAMEFILE_SIZE
11file1.txt100
21file2.txt200
32file3.txt150
42file4.txt300
53file5.txt50
63file6.txt250

下層から上層に向かって階層問い合わせをする

あるファイルを指定し、その格納フォルダから最上位フォルダまでの階層を取得してみます。ここではサブフォルダにあるfile3.txtが格納されているフォルダから上位フォルダへ向かって階層問い合わせを行います。

WITH FOLDER_HIERARCY (
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_PATH
) AS (
  -- 最下層のフォルダ
  SELECT
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_NAME FOLDER_PATH
  FROM
    FOLDER_TBL FD1
  WHERE
    -- フォルダに格納されているファイルのファイル名が'file3.txt'
    EXISTS(SELECT 1 FROM FILE_TBL FL1 WHERE FD1.FOLDER_ID = FL1.FOLDER_ID AND FL1.FILE_NAME = 'file3.txt')
  UNION ALL
  SELECT
    FD2.FOLDER_ID,
    FD2.PARENT_ID,
    FD2.FOLDER_NAME,
    FD2.FOLDER_NAME || ' / ' || BASE.FOLDER_PATH
  FROM
    FOLDER_HIERARCY BASE -- このテーブルに対して
    INNER JOIN FOLDER_TBL FD2 -- このテーブルを結合していく
    ON
      BASE.PARENT_ID = FD2.FOLDER_ID -- 下位フォルダのPARENT_IDに一致するFOLDER_IDのフォルダは1つ上位のフォルダとなる
)
SELECT
  FOLDER_PATH || ' / file3.txt'
FROM
  FOLDER_HIERARCY
WHERE
  PARENT_ID IS NULL -- PARENT_IDがNULLのレコードに絞り込むと最上位フォルダまでのパスが得られる
;

ポイントは、

  • 階層問い合わせをするときは項目を明示する（しないとエラーになる）
  • 基準となるレコードに対して上位のレコードを結合していく

です。下層から上層に向かって階層問い合わせする場合は、基準となるテーブル（BASE）の親IDと一つ上位となるテーブル（FD2）のIDを結合する点に気をつけましょう。

上層から下層に向かって階層問い合わせをする

同じことを逆からやってみましょう。

WITH FOLDER_HIERARCY (
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_PATH
) AS (
  -- 最上層のフォルダ
  SELECT
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_NAME FOLDER_PATH
  FROM
    FOLDER_TBL FD1
  WHERE
    FD1.PARENT_ID IS NULL
  UNION ALL
  SELECT
    FD2.FOLDER_ID,
    FD2.PARENT_ID,
    FD2.FOLDER_NAME,
    BASE.FOLDER_PATH || ' / ' || FD2.FOLDER_NAME
  FROM
    FOLDER_HIERARCY BASE -- このテーブルに対して
    INNER JOIN FOLDER_TBL FD2 -- このテーブルを結合していく
    ON
      BASE.FOLDER_ID = FD2.PARENT_ID -- 上位フォルダのFOLDER_IDに一致するPARENT_IDのフォルダは1つ下位のフォルダとなる
)
SELECT
  FOLDER_PATH || ' / ' || FILE_NAME
FROM
  FOLDER_HIERARCY T1
  INNER JOIN FILE_TBL T2
  ON
    T1.FOLDER_ID = T2.FOLDER_ID
WHERE
  FILE_NAME = 'file3.txt'
;

上層から下層に向かって階層問い合わせする場合は、基準となるテーブル（BASE）のIDと一つ下位となるテーブル（FD2）の親IDを結合する点に気をつけましょう。

いずれの検索結果もFolder 1 / Folder 1-1 / file3.txtとなります。

[Oracle]ユーザー（スキーマ）を作成する

ローカル環境で使用する開発用・テスト用のユーザーを作成します。

今回使用した環境は以下のとおりです。

  • Oracle Database XE 21c

本記事で実行しているSQLは、すべてSYSTEMユーザーで実行しています。

テーブルスペースを確認する

まずは使用するテーブルスペースを確認します。

-- テーブルスペースを一覧表示する
SELECT TABLESPACE_NAME, INITIAL_EXTENT FROM DBA_TABLESPACES;
TABLESPACE_NAMEINITIAL_EXTENT
SYSTEM65536
SYSAUX65536
UNDOTBS165536
TEMP1048576
USERS65536

ここではDEFAULT TABLESPACEにUSERSを使用し、TEMPORARY TABLESPACEにTEMPを使用します。

ユーザーを作成する

どのテーブルスペースを使用するかが決まったら、ユーザーを作成します。

-- ユーザーを作成する
CREATE USER EXAMPLE IDENTIFIED BY PASSWORD DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

ここではEXAMPLEというユーザー（スキーマ）をPASSWORDというパスワードで作成しています。

このままではログインすらできないユーザーですので、作成したユーザーに権限を与えます。ここではローカルでの開発用・テスト用のユーザーですので、作成しそうなオブジェクトを作成する権限は一通り与えておきます。

-- ユーザーに権限を与える
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE TRIGGER, CREATE SYNONYM, UNLIMITED TABLESPACE TO EXAMPLE;

作成したユーザー名とパスワードでログインすれば、ユーザー名と同じスキーマが使用可能になっています。

パスワードの有効期限を確認・設定する（オプション）

この手順は必須ではありませんが、念のためパスワードの有効期限を確認しておきます。プロジェクトや職場のポリシーにもよりますが、開発用・テスト用のユーザーですので、パスワードの有効期限切れにはならない方が運用が楽だと思います。

今回使用したプロファイルはDEFAULTですので、DEFAULTプロファイルの有効期限を確認します。

-- DEFAULTプロファイルの有効期限を確認する
SELECT PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME' AND PROFILE = 'DEFAULT';
PROFILERESOURCE_NAMERESOURCE_TYPELIMIT
DEFAULTPASSWORD_LIFE_TIMEPASSWORDUNLIMITED

今回はOracle XEを使用していますが、特に設定変更していない状態でUNLIMITEDになっています。

ここがUNLIMITED以外になっていて、無期限に変更したい場合は以下のSQLを実行します。

-- DEFAULTプロファイルの有効期限を無期限に変更する
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

SQL実行後に再度確認して、UNLIMITEDになっていることを確認してください。

Apple Silicon MacのDockerでOracle Databaseを動かす

執筆時点（2023/03/01）での暫定的な対応となる点にご注意ください。将来的にはOracle DatabaseがARMに対応する可能性があります。
Oracle DatabaseのARM対応については、以下の動画の26:40 – 27:22をご覧ください。

Oracle Database: What’s new, what’s next

Apple Silicon MacでOracle Databaseを動かす選択肢はいくつかありますが、よく知られている方法は私の環境ではうまく生きませんでした。

ここでは私の環境で成功した方法を共有します。

この方法は、

Running Oracle Database on Docker on Apple M1 Chip

で紹介されている方法になります。

手順を実行するためには、

  • Homebrew
  • Docker

が必要となります。

Colimaをインストールする

まずはColimaをインストールします。Dockerの--platform linux/x86_64ではうまくいきませんでしたので、ご注意ください。

$ brew install colima

Homebrewでインストールしますので、インストールしていない場合は先にインストールしてください。

次にColimaを起動します。起動するとDockerのコンテキストが変更されます。

$ colima start --arch x86_64 --memory 4

ここでは参考にした記事と同じく4(GB)のメモリを割り当てていますが、必要に応じて増やしてください。

$ docker context ls
NAME                TYPE                DESCRIPTION                               DOCKER ENDPOINT                                     KUBERNETES ENDPOINT   ORCHESTRATOR
colima *            moby                colima                                    unix:///Users/user1/.colima/default/docker.sock
default             moby                Current DOCKER_HOST based configuration   unix:///var/run/docker.sock                                               swarm
desktop-linux       moby                                                          unix:///Users/user1/.docker/run/docker.sock

現在のコンテキストがcolimaになっていることを確認してください。変更されていない場合は、

$ docker context use colima

で変更してください。

Oracle Databaseを起動する

Oracle DatabaseをDockerで起動します。

$ docker run -d -p 1521:1521 -e ORACLE_PASSWORD=<パスワード> -v oracle-volume:/opt/oracle/oradata gvenzl/oracle-xe

<パスワード>部分は任意のパスワードを設定してください。このパスワードはSYSユーザーとSYSTEMユーザーのパスワードになります。

例えば、パスワードをpasswordにした場合は以下のようになります。

$ docker run -d -p 1521:1521 -e ORACLE_PASSWORD=password -v oracle-volume:/opt/oracle/oradata gvenzl/oracle-xe

すぐに制御が返ってきますので、少し経ったら接続してみましょう。ローカルにSQL*Plusはインストールされていないと思いますので、何かしらのデータベース接続ツールを使用してください。

私はDataGripを使って接続しています。ユーザーはsystem、パスワードは先ほど起動時に設定したパスワードを入力してください。

まとめ

恐らくこれが現状では最も簡単にApple Silicon MacでOracle Databaseを動作させる方法だと思います。将来的にはVirtualBoxなどでもっと簡単にOracle Databaseを動作させることができるようになる可能性がありますので、暫定的な対応とお考えください。

[Oracle]SQL*Plusで実行計画を取得する（SET AUTOTRACE）

実行計画の確認にはいくつかを方法がありますが、ここでは最も手軽に実施できるSQL*Plusでの実行計画の取得の方法について解説します。

実行計画を取得するための設定を行う

私がよく使っている設定をご紹介します。とりあえずこの設定を使っていただければ実行計画を取得できるようになります。

SQL> set lines 1000
SQL> set pages 1000
SQL> set timing on
SQL> set autotrace traceonly

以上です。

この設定を行った状態でSQLを実行すると以下のようになります。

SQL> select * from dept
  2  /

レコードが選択されませんでした。

経過: 00:00:00.02

実行計画
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    30 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     1 |    30 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        449  bytes sent via SQL*Net to client
        369  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

実行計画と統計情報が出力されていることがわかります。

上記手順を実施しているときにエラーが出る場合があります。その場合は後述の「手順の実施時にエラーが出る場合」を参照してください。

これだけだとよくわからないと思いますので、各コマンドについて解説します。

表示幅を広げる

linesizeで結果行の幅を設定します。実行計画の出力内容は表を含むため、途中で折り返されるとかなり見にくいです。そこでとりあえず折り返されないような大きな値を設定しています。

正式にはlinesizeですが、linesでも設定できます。タイプ数が少ない方が楽なのでlinesを使用しています。

set lines 1000

1ページの行数を増やす

実行計画の出力の途中で改ページすると見づらいため、1ページの行数も増やしています。こちらもとりあえずで増やしていますが、普通に使う分には問題は起こらないと思いますが、あまりにも長いSQLの実行計画を確認するときに、改ページされるようであれば値を増やしてください。

先ほどと同様にpagesizeが正式な設定ですが、pagesでも設定できます。

set pages 1000

タイミング統計をONにする

タイミング統計をONにすることで、実行時間を10ミリ秒単位で出力できるようになります。

経過: 00:00:00.02

実行時間もチューニングの重要な情報源となりますし、出力結果が邪魔になることもないので、ONにしておきましょう。

set timing on

実行計画の設定を行う

実行計画の出力設定は、autotraceで行います。出力できる内容は、

  • 実行結果
  • 実行計画
  • 統計情報

の3つがありますが、実行結果を必要としない場合はtraceonlyを設定し、実行結果を確認したい場合はoffに切り替えるのがおすすめです。

設定説明
set autotrace on explain実行結果と実行計画を表示する
set autotrace on statistics実行結果と統計情報を表示し、実行計画をは表示しない
set autotrace on実行結果、実行計画、統計情報を表示する
set autotrace traceonly実行計画と統計情報を表示し、実行結果は表示しない
set autotrace offautotraceの設定をOFFにする
set autotrace traceonly

手順の実行時にエラーが出る場合

これから説明する手順を実行したときに、以下のようなエラーが出る場合があります。

SQL> set autotrace traceonly
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

このエラーが出る場合は、以下の手順を実行してPLUSTRACEロールを有効にしてください。

SYSDBAでCDBにログインする

ロールを追加するため、SYSDBAでCDBにログインします。

ORACLE_SID=ORCLCDB sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 29 15:44:05 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

PDBに切り替える

ロールを作成するPDBに切り替えます。ここではpdb1というPDBに切り替えています。

SQL> alter session set container=pdb1;

Session altered.

SQL>

ロールを作成するPDBというのは、実行計画を取得するPDBのことです。

PLUSTRACEロールを作成するSQLファイルを実行する

OracleDatabaseに用意されているSQLファイルを実行します。SQLファイルのファイル名はplustrace.sqlではなくplustrce.sqlですので、打ち間違いに注意してください（aがない）。

SQL> @?/sqlplus/admin/plustrce.sql
SQL> 
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL> create role plustrace;

Role created.

SQL> 
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 
SQL> set echo off

PLUSTRACEロールをユーザーに付与する

作成したPLUSTRACEロールをユーザーに付与します。ここでは、user01ユーザーに設定しています。

SQL> grant plustrace to user01;

Grant succeeded.

SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

再度、該当ユーザーでログインしてautotraceの設定を行ってください。

外部キー制約でのON DELETE CASCADEオプションの使い方

外部キー制約でのON DELETE CASCADEオプションの使い方を具体的な例を使って解説します。

ON DELETE CASCADEオプションは親テーブル（参照先のテーブル）を削除するときに子テーブル（参照元のテーブル）も一緒に削除するために使用するオプションになります。

解説に使用するデータベースとテーブルについて

解説に使用するデータベースとテーブルについて説明します。

確認にはOracle Database 19cを使用しています。若干の構文の違いはありますが、他の主要なデータベースでも同様の操作が可能です。

まずは親テーブルを作成します。親テーブルのMAINテーブルは主キーIDのみを持ったテーブルです。

CREATE TABLE MAIN
(
  ID NUMBER(3),
  CONSTRAINT MAIN_PK PRIMARY KEY (ID)
)

次に子テーブルを作成します。子テーブルのSUBテーブルはIDとSEQを主キーに持つテーブルです。

CREATE TABLE SUB
(
  ID  NUMBER(3),
  SEQ NUMBER(2),
  CONSTRAINT SUB_PK PRIMARY KEY (ID, SEQ)
)

ON DELETE CASCADE オプションなしの場合

では、ON DELETE CASCADEオプションを使用しない場合の動作を見てみましょう。

SUBテーブルのIDに外部キー制約を設定します。

SQL> ALTER TABLE SUB ADD CONSTRAINT SUB_ID_FK
   2 FOREIGN KEY (ID) REFERENCES MAIN(ID)

表が変更されました。

では、データを投入して削除時の動作を確認します。

SQL> INSERT INTO MAIN VALUES (1)

1行が作成されました。
SQL> INSERT INTO SUB VALUES (1, 1)

1行が作成されました。
SQL> INSERT INTO SUB VALUES (1, 2)

1行が作成されました。

削除する前にデータを確認しておきましょう。

SQL> SELECT * FROM MAIN

ID
--
 1

1行が選択されました。
SQL> SELECT * FROM SUB

ID SEQ
-- ---
 1   1
 1   2

2行が選択されました。

MAINテーブルのID=1のレコードにSUBテーブルのID=1のレコードが2件ぶら下がっている形になっています。

データが投入できましたので、親テーブルMAINテーブルのレコードを削除してみます。

SQL> DELETE FROM MAIN WHERE ID = 1
DELETE FROM MAIN WHERE ID = 1
*
行1でエラーが発生しました。:
ORA-02292: 整合性制約(USER01.SUB_ID_FK)に違反しました - 子レコードがあります

整合性制約（外部キー制約）で削除に失敗しました。ON DELETE CASCADEオプションがない場合は、子テーブルを削除してからでないと親テーブルを削除できません。

念のためデータが削除されていないことを確認しておきます。

SQL> SELECT * FROM MAIN

ID
--
 1

1行が選択されました。
SQL> SELECT * FROM SUB

ID SEQ
-- ---
 1   1
 1   2

2行が選択されました。

ON DELETE CASCADEオプションありの場合

では、ON DELETE CASCADEオプションをつけた場合の削除の動きを確認します。

すでに設定済みの外部キーを削除しておきます。

SQL> ALTER TABLE SUB DROP CONSTRAINT  SUB_ID_FK

表が変更されました。

今度はON DELETE CASCADEオプション付きで外部キー制約を設定します。

SQL> ALTER TABLE SUB ADD CONSTRAINT SUB_ID_FK
   2 FOREIGN KEY (ID) REFERENCES MAIN(ID)
   3 ON DELETE CASCADE

表が変更されました。

では、もう一度削除を試します。

> DELETE FROM MAIN WHERE ID = 1

1行が削除されました。

今度は削除できました。

削除後のデータを確認します。

SQL> SELECT * FROM MAIN

レコードが選択されませんでした。
SQL> SELECT * FROM SUB

レコードが選択されませんでした。

削除したMAINテーブルのレコードだけでなく、SUBテーブルのID=1のレコードも削除されています。

どういった場合にON DELETE CASCADEオプションを使うか

まず、外部キー制約を使用する場合に限られているということが前提となります。

上記の前提の上で、以下の2つのどちらの効果を狙いたいかで決めるとよいでしょう。

  • 子テーブルにデータがあるときに親テーブルが削除されるのを防ぎたい
  • 親テーブルが削除されるときに子テーブルをまとめて削除したい

もう少し具体的な例で説明します。

ON DELETE CASCADEオプションを使いたくないケース

ON DELETE CASCADEオプションを使いたくないケース、使うべきでないケースとして挙げられるのが、マスタテーブルに対して外部キー制約を設定しているケースです。

例えば、注文テーブルにある顧客番号に対して、顧客マスタの顧客番号への外部キーを設定している場合、ON DELETE CASCADEオプションを設定していると、注文テーブルのレコードの削除によって顧客マスタのレコードが削除される場合があります。
これは望ましい挙動でないことから、マスタテーブルへの外部キー設定の場合はON DELETE CASCADEオプションを使用するべきではありません。

ON DELETE CASCADE オプションを使いたいケース

ON DELETE CASCADEオプションを使いたいケースとして挙げられるのが、子テーブルが親テーブルに対して外部キー制約を設定しているケースです。

例えば、注文テーブルと注文明細テーブルがあり、注文明細の注文番号に対して、注文テーブルの注文番号への外部キーを設定している場合は、ON DELETE CASCADEオプションを設定していると、注文テーブルのレコード削除時に注文明細テーブルのレコードをまとめて削除してくれるため便利です。
ON DELETE CASCADEオプションを設定していない場合は、先に注文明細テーブルの該当する注文番号のレコードを先に削除してからでないと注文テーブルのレコードを削除できないため、ON DELETE CASCADEオプションを使うことが削除を行うプログラムの構造をすっきりとさせることができる効果があります。

まとめ

前述の例をまとめると以下のようになります。

CREATE TABLE 注文
(
  注文番号 NUMBER(10),
  顧客番号 NUMBER(3),
  CONSTRAINT 注文_PK PRIMARY KEY (ID)
)

CREATE TABLE 注文明細
(
  注文番号 NUMBER(10),
  注文明細連番 NUMBER(10),
  CONSTRAINT 注文明細_PK PRIMARY KEY (注文番号, 注文明細番号)
)

CREATE TABLE 顧客マスタ
(
  顧客番号 NUMBER(3),
  CONSTRAINT 顧客マスタ_PK PRIMARY KEY (顧客番号)

-- 注文から顧客マスタへの外部キーではON DELETE CASCADEオプションをつけない
ALTER TABLE SUB ADD CONSTRAINT 注文_顧客番号_FK
FOREIGN KEY (顧客番号) REFERENCES 顧客マスタ(顧客番号)

-- 注文明細から注文への外部キーではON DELETE CASCADEオプションをつける
ALTER TABLE SUB ADD CONSTRAINT 注文明細_注文番号_FK
FOREIGN KEY (注文番号) REFERENCES 注文(注文番号)
ON DELETE CASCADE

このようにON DELETE CASCADEオプションを適切に設定する／しないことでデータを安全に、効率よく管理できるようになります。

PDBにローカルユーザーを作成する（Oracle Database 19c）

PDBにローカルユーザーの作成を行う手順について解説します。

前提

手順に使用しているデータベース環境およびCDB、PDBの設定、作成するローカルユーザーは以下のとおりです。

手順に沿ってローカルユーザーを作成する際は適宜読み替えてください。

項目値
バージョンOracle Database 19c (19.3.0)
CDB
　SIDORCLCDB
PDB
　PDBローカル管理者ユーザーPDBADMIN
　PDBローカル管理者パスワードoracle
　SIDPDB1
ローカルユーザー
　ユーザーuser01
　パスワードpassword

PDBローカル管理者ユーザーにDBA権限を与える

PDBローカル管理者ユーザーを使ってローカルユーザーを作成するため、PDBローカル管理者ユーザーにDBA権限を付与します。

PDBローカル管理者ユーザーの権限を確認する

まずはCDBにOS認証でCDBに接続しますが、ORACLE_SID環境変数が必要となるため、ORACLE_SID環境変数を確認します。

$ echo $ORACLE_SID

この環境はDocker上に構築したOracle Database 19cですが、ログインしてみるとORACLE_SID環境変数は設定されていませんでした。

ORACLE_SID環境変数を設定してSQL*Plusを起動します。

$ ORACLE_SID=ORCLCDB sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 19 14:36:24 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

念のため、CDBに接続出来ていることを確認しましょう。

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

PDBについても確認します。今回はすでに作成済みのPDBであるPDB1にローカルユーザーを作成していきます。

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL>

PDB1に切り替えます。

SQL> alter session set container=pdb1;

Session altered.

SQL>

PDB1に切り替わったことを確認します。

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>

PDBローカル管理者ユーザーPDBADMINに必要な権限があるかどうか確認します。

ここでは、以下の2つのテーブルを確認していきます。

  • SYS.DBA_ROLE_PRIVS – データベース内のすべてのユーザーおよびロールに付与されたロールを示す
  • SYS.DBA_SYS_PRIVS – ユーザーおよびロールに付与されたシステム権限を示す

まずはSYS.DBA_ROLE_PRIVSを確認します。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'PDBADMIN'
  4  /

GRANTEE  GRANTED_ROLE ADM
-------- ------------ ---
PDBADMIN PDB_DBA      YES

SQL>

PDB_DBAというロールを付与されていることがわかります。

次にSYS.DBA_SYS_PRIVSも確認しておきましょう。

SQL> select GRANTEE, PRIVILEGE, ADMIN_OPTION FROM SYS.DBA_SYS_PRIVS where GRANTEE = 'PDBADMIN'
  2  /

no rows selected

SQL>

特に権限は付与されていません。

では、PDBADMINに与えられていたPDB_DBAロールについても確認していきます。

まずは、SYS.DBA_ROLE_PRIVSを確認します。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'PDB_DBA'
  4  /

GRANTEE GRANTED_ROLE ADM
------- ------------ ---
PDB_DBA CONNECT      NO

SQL>

CONNECTロールが付与されているだけでした。

次にSYS.DBA_SYS_PRIVSを確認します。

SQL> SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'PDB_DBA'
  4  /

GRANTEE PRIVILEGE                                ADM
------- ---------------------------------------- ---
PDB_DBA CREATE PLUGGABLE DATABASE                NO
PDB_DBA CREATE SESSION                           NO

SQL>

CREATE PLUGGABLE DATABASE、CREATE SESSIONが付与されているだけで、DBA関連の権限は付与されていないことがわかります。

PDB_DBAロールにDBAロールを付与する

PDBローカル管理者ユーザーでローカルユーザーを作成できるようにするために、PDBローカル管理者ユーザーに付与されていたPDB_DBAロールにDBAロールを付与することにします。

SQL> GRANT DBA TO PDB_DBA;

Grant succeeded.

SQL>

PDB_DBAロールにDBAロールが付与されているはずなので、確認しておきましょう。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'PDB_DBA'
  4  /

GRANTEE GRANTED_ROLE ADM
------- ------------ ---
PDB_DBA DBA          NO
PDB_DBA CONNECT      NO

もともと付与されていたCONNECTロールに加え、DBAロールが付与されていることを確認できました。

これでPDBローカル管理者ユーザーPDBADMINでローカルユーザーの作成ができるようになったので、一度ログアウトしましょう。

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

ローカルユーザーを作成する

今度はPDBADMINユーザーでPDB1へログインします。

$ sqlplus pdbadmin/oracle@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 19 15:09:27 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 19 2022 13:56:08 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

くどいようですが、念のためPDB1に接続していることを確認します。

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>

テーブルスペースの確認

ローカルユーザーに割り当てるテーブルスペースを作成するべきかどうかを判断するために、テーブルスペースを確認します。

SQL> SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, MAX_EXTENTS, MAX_SIZE, STATUS, CONTENTS
  2  FROM SYS.DBA_TABLESPACES
  3  /

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT MAX_EXTENTS   MAX_SIZE STATUS CONTENTS
--------------- ---------- -------------- ----------- ---------- ------ ---------------------
SYSTEM                8192          65536  2147483645 2147483645 ONLINE PERMANENT
SYSAUX                8192          65536  2147483645 2147483645 ONLINE PERMANENT
UNDOTBS1              8192          65536  2147483645 2147483645 ONLINE UNDO
TEMP                  8192        1048576             2147483645 ONLINE TEMPORARY
USERS                 8192          65536  2147483645 2147483645 ONLINE PERMANENT


SQL> SELECT FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, STATUS
  2  FROM SYS.DBA_DATA_FILES 
  3  /

FILE_NAME                                      TABLESPACE_NAME      BYTES     BLOCKS STATUS
---------------------------------------------- --------------- ---------- ---------- ---------
/opt/oracle/oradata/ORCLCDB/PDB1/undotbs01.dbf UNDOTBS1         104857600      12800 AVAILABLE
/opt/oracle/oradata/ORCLCDB/PDB1/sysaux01.dbf  SYSAUX           367001600      44800 AVAILABLE
/opt/oracle/oradata/ORCLCDB/PDB1/system01.dbf  SYSTEM           283115520      34560 AVAILABLE
/opt/oracle/oradata/ORCLCDB/PDB1/users01.dbf   USERS              5242880        640 AVAILABLE


SQL>

デフォルトに使用したいUSERSテーブルスペース、テンポラリに使用したいTEMPテーブルスペースがすでに存在しているため、テーブルスペースは作成せず進めます。

ローカルユーザーを作成する

では、ローカルユーザーuser01を作成します。テーブルスペースは先ほど確認したUSERSテーブルスペースとTEMPテーブルスペースを使用します。

SQL> CREATE USER user01
  2  IDENTIFIED BY password
  3  DEFAULT TABLESPACE USERS
  4  QUOTA UNLIMITED ON USERS
  5  TEMPORARY TABLESPACE TEMP
  6  /

User created.

SQL>

ローカルユーザーに権限を付与する

ユーザーを作成しただけでは何もできないユーザーになっているため、必要な権限を付与します。ここでは以下の3つを付与します。

  • RESOURCEロール – テーブル等のオブジェクト作成を可能にするロール
  • CREATE SESSION権限 – データベースに接続するために必要な権限
  • UNLIMITED TABLESPACE権限 – テーブルスペースを制限なしで使用できる権限
SQL> GRANT RESOURCE TO user01
  2  /

Grant succeeded.

SQL> GRANT CREATE SESSION TO user01
  2  /

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO user01
  2  /

Grant succeeded.

SQL>

ロール、権限が付与されているか確認します。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'USER01' 
  4  /

GRANTEE GRANTED_ROLE ADM
------- ------------ ---
USER01  RESOURCE     NO


SQL> SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'USER01'
  4  /

GRANTE PRIVILEGE                                ADM
------ ---------------------------------------- ---
USER01 UNLIMITED TABLESPACE                     NO
USER01 CREATE SESSION                           NO


SQL>

意図したとおりに設定できていることが確認できました。

動作確認を行う

user01ユーザーが使用可能か確認するために、一度切断します。

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

user01ユーザーでログインしてみます。

$ sqlplus user01/password@PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 19 15:28:15 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 19 2022 15:27:20 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

問題なく接続できていることを確認できました。

次にテーブルを作成し、データを追加、検索をしてみましょう。

SQL> CREATE TABLE emp
  2  (
  3    empno   VARCHAR2(10) NOT NULL,
  4    empname VARCHAR2(50),
  5    gender  NUMBER(1, 0)
  6  )
  7  /

Table created.

SQL> INSERT INTO emp VALUES ('ABC', 'DEF', '1')
  2  /

1 row created.

SQL> SELECT * FROM emp 
  2  /

EMPNO      EMPNAME                                                GENDER
---------- -------------------------------------------------- ----------
ABC        DEF                                                         1

SQL>

動作確認に使用したテーブルは不要なため、削除しておきます。

SQL> DROP TABLE emp 
  2  /

以上でローカルユーザーの作成が完了しました。

参考文献

モバイルバージョンを終了