Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

CREATE SCHEMA文とは

はじめに

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文の紹介でした。

nakaie
日本オラクル所属のOracle Database中心のエンジニアです。 投稿内容は個人の見解であり所属する組織の公式見解ではありません。 Twitter : @HNakaie
oracle
Oracle Cloudは、最先端の機能をSoftware as a Service、Platform as a ServiceおよびInfrastructure as a ServiceおよびData as a Serviceとして提供します。
https://cloud.oracle.com/ja_JP/home
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away