タグ: oracle

Oracle Database@Google Cloud、ついに日本上陸──クラウド移行とAI活用を加速するマルチクラウドの要石

2025年6月13日、オラクルとGoogle Cloudは日本市場に向けて「Oracle Database@Google Cloud」の提供を正式に開始しました。これは、Google Cloudの東京リージョン（アジア北東1）において、オラクルのフル機能データベースサービスが、クラウドネイティブな形で利用可能となる歴史的な一歩です。

従来、Oracle Databaseは自社クラウド（OCI）やオンプレミス、もしくはライセンス持ち込み型のクラウドでの利用が一般的でしたが、今回の発表は、Oracleが自らのデータベースを第三者クラウド上で提供・運用する初の試みです。このことにより、日本国内のクラウド移行、データ主権の確保、AI活用が一層加速されることが期待されます。

クラウドでも“フル機能のOracle”を

この新サービスでは、以下の先進的なOracleデータベースがGoogle Cloud上で利用可能です：

  • Oracle Exadata Database Service on Dedicated Infrastructure
    最新のX11Mアーキテクチャを採用し、AI・分析・OLTP（オンライントランザクション処理）における高性能を実現。Real Application Clusters（RAC）にも対応。
  • Oracle Database 23ai
    JSONとリレーショナルを統合した「JSON Relational Duality Views」や、「Oracle AI Vector Search」など、AI時代を見据えた機能を300以上搭載。
  • Oracle Autonomous Database
    フルマネージド型のクラウドデータベースで、Google CloudのAPI・UIに完全統合。パフォーマンス、可用性、セキュリティのすべてにおいて高水準を提供。
  • Oracle Base Database Service（近日提供）
    軽量な仮想マシンベースのデータベース。19cや23aiなどを従量課金で利用可能。ローコード開発もサポート。

AIとクラウドネイティブ開発の融合

Oracle Database@Google Cloudは、GoogleのGeminiやVertex AIなどのAI基盤と統合可能であり、開発者にとってはクラウドネイティブなAIアプリケーション開発の出発点となります。

また、JSON×RDB統合、非構造データの検索、AI推論基盤との連携など、データ活用の可能性が大きく広がります。

パートナーエコシステムの再構築へ

OracleとGoogle Cloudは、再販プログラムの創設により、日本国内のSIerやクラウド事業者と連携し、企業のマルチクラウド導入を強力に支援します。Google Cloud Marketplace経由での提供が可能になり、導入・運用の敷居が大きく下がる点も魅力です。

株式会社システムサポートをはじめとする主要パートナーは、この環境を活かして、より柔軟なクラウド構成とデータソリューションの提供を目指しています。

今後の展開：グローバル対応へ

今回提供開始となったのは東京リージョンですが、今後12カ月以内に大阪（アジア北東2）、ムンバイ（アジア南1）など、複数リージョンでの展開が予定されています。これは、Oracleの「分散クラウド戦略」の一環であり、パブリック、プライベート、マルチクラウドを柔軟に組み合わせるポートフォリオ戦略が基盤となっています。

おわりに

「Oracle Database@Google Cloud」の登場により、日本の企業は“クラウドネイティブでありながら、Oracleの完全な機能性を享受できる”という新たな選択肢を手にしました。特に、AI活用、データレジデンシー、既存DB資産の活用に悩む企業にとっては、待望のソリューションとなるでしょう。

これからの日本におけるクラウド移行、AI統合の中核を担う存在として、この発表は今後の市場を左右する大きな転換点といえます。

参考文献

[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  /

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

参考文献

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