はじめに

Oracle Databaseには、Oracle7(1992年リリース)の頃から存在しているにもかかわらず、知名度が非常に低い、CREATE SCHEMAというSQL文が存在します。地味すぎて格別にお勧めするほどの機能でもない(あくまでも個人的意見です)のですが、このコマンドが何をするものか、何の役に立つのか、少し光を当ててみよう、というのがこの記事の趣旨です。

Oracle Databaseにおけるスキーマとは

まず、スキーマとは何でしょう?　特にOracle Databaseしか知らない方には、理解しにくい概念だと思われます。
マニュアルにはスキーマ(データベース・スキーマ)の説明としてこう書かれています。

「Oracle Databaseにおいて、データベース・スキーマとは、論理データ構造またはスキーマ・オブジェクトの集合です。データベース・スキーマはデータベース・ユーザーによって所有され、そのユーザー名と同じ名前になります。」

この説明ではわかりにくいかもかもしれませんが、テーブルやインデックスのようなデータベースのオブジェクトを格納するための、論理的な集合ないし領域だとお考え下さい。もう少し言い換えると、名前空間とご理解いただくのもいいかもしれません。
他のデータベース製品だとユーザーとスキーマが分かれているものもありますが、Oracle Databaseにおいては、上記引用のとおり、ユーザーとスキーマの名称が常に同じになります。常に同じなのでわかりにくいですし、同じと理解しても特段詰まることもないのですが、厳密には異なる概念です。
実際、マニュアルではユーザーとスキーマを細かく使い分けています。一例を挙げると、SELECT * FROM SCOTT.EMPの「SCOTT」はスキーマですし、GRANT SELECT ON EMP TO KINGの「KING」はユーザーです(ロールでしょ、という茶々はなしで)。

CREATE SCHEMA文とは

コマンド名称から器としてのスキーマを作るんだな、という想像をするかもしれませんが、実際には、ユーザーを作成すると同時に暗黙的にスキーマも作成されるため、このSQLコマンドでスキーマを改めて作成することはできません。実際、存在しないスキーマ(ユーザー)を指定してもエラーになります。
マニュアルにはスキーマはスキーマ・オブジェクトの集合と書かれていますが、このSQLコマンドは、複数のスキーマ・オブジェクトと、関連する権限を一度にまとめて作成するSQLコマンドとなります。といっても、作成できるスキーマ・オブジェクトは、テーブルとビューだけです。インデックスさえ作成できません。だったらSQLスクリプトでCREATE文を一気に流せばいいのでは、という話になるのですが、CREATE SCHEMA文には以下の利点が存在します。

CREATE SCHEMA文内のDDL文全体をトランザクションとして扱える

Oracle Databaseは複数のDDL文を一つのトランザクションとしてまとめてコントロールすることができません。しかし、CREATE SCHEMA文はその例外となります。例として、2番目のCREATE VIEW文の内容が誤っているCREATE SCHEMA文を実行した後、構文的に問題のないはずのTEST表を検索してみましたが、作成されていません。
なお、作成先のスキーマは存在している必要があると言いますか、そのスキーマのユーザーで接続しなければいけません。当然ながら、CREATE SESSION, CREATE TABLE, CREATE VIEWの権限も必要です。

SQL> create schema authorization nakaie
  2    create table test (col1 number primary key, col2 varchar2(100))
  3    create view v_test as select * from test where col3 < 10
  4    grant select on v_test to sh
  5  ;
create schema authorization nakaie
*
ERROR at line 1:
ORA-02427: create view failed


SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQLスクリプトで1文ずつ実行する場合、失敗した個所と、そこに依存している個所だけ流し直す、という形になります。一方、CREATE SCHEMA文は、問題個所を修正して全体を流し直す形となります。比較すると、CREATE SCHEMA文は再実行が簡単、という利点があります。再実行の処理時間そのものは全体を流し直すCREATE SCHEMA文が不利と言えば不利ですが、問題個所だけうまく抜き出して再実行する作業自体、何度もやり直してようやく成功、というパターンもあるので、修正作業時間全体の観点では、どらかが絶対的に有利、というのはないと思います。

CREATE SCHEMA文内の実行順を気にする必要がない

先の不成功スクリプトの問題個所を修正した上で、作成順を入れ替えて実行してみます。上から順番に文を眺めると、一見正しくない順番で実行しているSQLに見えますが、以下の通り成功します。

SQL> create schema authorization nakaie
  2    grant select on v_test to sh
  3    create view v_test as select * from test where col1 < 10
  4    create table test (col1 number primary key, col2 varchar2(100))
  5  ;

Schema created.

各文をSQLスクリプトで実行する場合は実行順を考慮する必要がありますが、CREATE SCHEMA文の場合、同一文内なら実行順を考慮する必要はありません。上記例のようにビューを先に記述したり、他には参照整合性制約を持つ子表から先に記述してもエラーにならない、といった利点が考えられます。

以上、存在がマイナーすぎて何かを見落としている気がしてならないのですが、CREATE SCHEMA文の紹介でした。