はじめてのOracle9i データベース

第3章 サンプルスキーマを作ってみよう
   1.サンプルスキーマを作ってみよう (1)

サポートサービス本部 プレミアムサポート部 ミッションクリティカルサポートグループ
亀井 範行

◆ はじめに

データベースは表の集まりです。データを表に格納する方法を工夫し、表からの取り出し方を工夫することで、データは情報に変わります。従ってデータベースは使い方次第で大きな価値を産み出します。データベースを管理するソフトウェアであるOracleを勉強するのは大変な面もありますが、がんばっていきましょう。


◆ とにかく触ってみよう

これから、Oracleの操作を体験していくわけですが、まずは説明抜きにログインしてインストール時に用意されているサンプル表を検索してみましょう。

OracleをインストールしたOSユーザでOSにログインして以下のように実行してください。

% sqlplus scott/tiger

以下のような出力が表示されていれば正常にログインできています。

% sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on 月 Apr 7 02:41:46 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning option
JServer Release 9.2.0.1.0 - Production
に接続されました。
SQL>

それでは、サンプルデータとして基本中の基本、EMP表を検索してみましょう。
"select * from emp;"と入力してEnterキーを押してください。最後の";"(セミコロン)も忘れずに入力してください。大文字小文字は区別しません。


これがEMP表です。実行したのは、検索を行う"SELECT文"というSQLです。SQLとは、データベースの操作をするための言語です。
"select * from emp;"は、EMP表からすべての列に関するすべての行を検索して表示させるという意味です。"列"(column、カラムとも呼びます)とは縦方向のデータの集まりです。EMP表でいうと、EMPNO列、ENAME列、JOB列 … といった呼び方をします。"行"(row、ローとも呼びます)は横方向のデータの集まりです。EMP表でいうと、一人分のデータが行となります。赤い枠で囲った行は、JONESさんに関するデータを表します。

EMP表は社員に関するデータを持つサンプル表です。それぞれの列の意味は以下のとおりです。

列名 意味
EMPNO 社員番号
ENAME 社員の名前
JOB 職種
MGR 上司の社員番号
HIREDATE 雇った日付
SAL 賃金
COMM コミッション(ボーナス)
DEPTNO 部屋番号

SQL*Plusで上のようにきれいに整った形で出力させるには、ログインしてから
SQL> set pagesize 100
SQL> set linesize 100
と入力して、ターミナル画面を横に広くしてから実行してください。
詳しくはマニュアル「SQL*Plusユーザーズガイドおよびリファレンス」を参照してください。


◆ 表を中心にしてデータベースの仕組みを知る

データベースを構成する仕組みは、物理的なファイルの存在で言うと、
  • インストールされた$ORACLE_HOME以下のファイル
  • データファイル
  • 制御ファイル
  • REDOログファイル
  • その他構成ファイル(初期化パラメータファイルなど)
という分類になります。今回は、論理的にどのように構成されているかを大まかに見ていきます。
まず、データベースで最も中心となる存在(オブジェクトと呼びます)は、"表"です。データベースに存在している他のものはすべて表のセキュリティや操作のパフォーマンスやデータの整合性を保つために存在しているといっても過言ではありません。
まずは"表"を中心にしてデータベースを構成する要素を見ていきましょう。

[表とユーザ]
データベース内に格納される表は"ユーザ"によって作成されます。OS上のユーザと同様にデータベースにもユーザという概念があります。データベース作成時にパスワードを決めたSYSとSYSTEMはデータベース管理用のユーザです。これらのユーザは、データベース上のすべてのユーザのオブジェクトを操作する権限を最初から持っています。そのため、管理作業以外の処理をするにはセキュリティ上ふさわしくありません。実行する処理の目的と、許可する処理にあわせて複数のユーザを作成してください。また、今回の最初にログインしたときに入力した"scott/tiger"は、サンプルユーザのユーザ名/パスワードとして大変有名ですので、このユーザは実運用には適しません。あくまでもサンプルの表だけを扱うユーザとしておいてください。

[表とスキーマ]
ユーザには表の他にも所有できるオブジェクトがあります。
  • 索引 … 表の検索を高速化するためのオブジェクト
  • ビュー … 一つ以上の表のデータを検索する仮想的な表
  • シノニム … 表やビューなどに付けることができる別名
  • 順序(シーケンス) … 一意な順序番号を発生させるためのオブジェクト
などです。
(詳しくはマニュアル『Oracle9iデータベース概要』 スキーマ・オブジェクト を参照してください)
スキーマとは、一人のユーザが所有するオブジェクトの集まりのことです。
例えば、「SYSTEMユーザ」と言う場合は、ユーザそのものを指します。これに対して「SYSTEMスキーマ」と呼ばれると、SYSTEMユーザが所有する表や索引やビューなどのまとまりのことを指します。また、「SYSTEMユーザの表」と「SYSTEMスキーマの表」はほとんど同じ意味を表します。
今回のタイトルである「サンプルスキーマを作ってみよう」というのは、つまり試しにユーザと所有するオブジェクトを作ってみようということです。

[表と権限]
上で何度か出ている"権限"はOracleにおいて重要な概念です。権限とは、簡単にいうとSQL文を実行するために必要な権利です。 全く権限をもたないユーザはデータベースに対する接続すらできません。接続やデータベースの起動/停止や表の作成など、特定のSQLを実行するために必要な権限を"システム権限"とよびます。
表を作成したユーザは、その表の操作(データの検索、挿入、更新、削除など)をする権限を持ちます。また、他のユーザに表を操作する権限を与えることもできます。このように、他のユーザが所有するオブジェクトに対してアクセスするための権限のことを"オブジェクト権限"と呼びます。他のある人に対しては表に対するすべての操作の権限を与えたいが、他の人には検索のみの権限といった分け方をする場合があります。複数のユーザを作ってそれらのユーザに別々の権限を与えるという使い方をすることになります。

図:他のユーザに権限を与えた場合の動作

[表と表領域]
・表領域とは?
表の論理的な格納先のことを"表領域"と呼びます。表領域は、データの物理的な格納先として一つ以上、"データファイル"と呼ばれるOSファイルを持ちます。ユーザは表などのオブジェクトを作成する際に、格納先としてデータファイルではなく表領域を指定します。表などのオブジェクトは、指定された表領域に属する使用可能なデータファイルに書き込まれます。データファイルが複数の場合には表のデータがある程度のまとまりで複数のデータファイルにまたがって書き込まれます。(下の図を参照してください)

図:表領域とデータファイルと表の関係

データベースにはいくつかの表領域が既に作成されています。たとえば、SYSユーザのオブジェクトはSYSTEM表領域に入っています。SYSTEM表領域はSYSが所有するデータベース管理情報をもった表が格納されています。他のユーザがオブジェクトを作る場合には、新しく表領域を作成してその表領域に入れるようにします。

・表領域を分けると便利な点
すべての表を一つの表領域にだけ格納することもできます。しかし、以下のような理由から表領域はデータの種類ごとに分けておくことをお勧めします。
  1. 一つの表領域の障害でデータベースのデータ全体が失われることを避けられる
  2. データベースをバックアップしやすくなる。また、障害時に対処しやすくなる
  3. 処理が集中する表を別の表領域に配置し、さらに異なる物理ディスクに配置することでI/Oが分散される

※注意
表領域やデータファイルはオブジェクトとは呼びません。また、特定のユーザ・スキーマの所有物ではありません。


◆ サンプルスキーマを作成する

それでは、実際にサンプルスキーマを作成してみましょう。

1. 表領域の作成
ユーザを作成する前に、表領域を作成します。この表領域は次に作成するユーザのオブジェクトがデフォルトで作成される表領域です。前に述べたとおり、表領域には一つ以上のデータファイルが属します。データファイルは実際にハードディスク上にファイルとして存在しますので、それだけの空き領域が存在している必要があります。データファイルのサイズは、通常格納されるデータの量に応じて設計することになります。
今回は、わかりやすくするためにデータベース ora920 の既存のデータファイルと同じディレクトリに新しいデータファイルが作られるようにファイルパスを決めます。

※注意
本来はハードディスクへの負荷分散や耐障害性を高めるために別々のハードディスクに分散させて配置することをお勧めします。

既存のデータファイルがどこに存在しているかは、データベースを作成した後は覚えていられないでしょう。しかしこういった情報はデータベースの中に保存されています。データベース管理ユーザもしくはそれに準ずる権限を持ったユーザはDBA_DATA_FILESというビューを通して検索できます。
SYSTEMユーザで接続して確認しましょう。

SQL> connect system/<systemユーザのパスワード>
接続されました。

SQL> select file_name, tablespace_name, bytes from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES
------------------------------ ----------
/home1/ora920/app/oracle/oradata/ora920/system01.dbf
SYSTEM                          262144000

/home1/ora920/app/oracle/oradata/ora920/undotbs01.dbf
UNDOTBS1                        209715200

/home1/ora920/app/oracle/oradata/ora920/indx01.dbf
INDX                             26214400

/home1/ora920/app/oracle/oradata/ora920/tools01.dbf
TOOLS                            10485760

/home1/ora920/app/oracle/oradata/ora920/users01.dbf
USERS                            26214400

見ると、すべてのデータファイルが/home1/ora920/app/oracle/oradata/ora920以下にありますので、このディレクトリに新しく作ることにします。

SQL> create tablespace test
  2  datafile '/home1/ora920/app/oracle/oradata/ora920/test1.dbf' size 10m ,
  3  '/home1/ora920/app/oracle/oradata/ora920/test2.dbf' size 10m;

表領域が作成されました。

上の例では、2つのデータファイルを含んだ表領域を作成しました。

2. ユーザの作成
次に、ユーザを作成します。作成時には、ユーザ名、パスワード、デフォルト表領域、一時表領域、表領域の利用可能領域制限を指定します。

[デフォルト表領域]
"default tablespace"句で設定します。
ユーザがオブジェクトを作成する際に、作成先の表領域を指定することができます。指定しなかった場合に作成先となる表領域がこのユーザのデフォルト表領域です。

[一時表領域]
"temporary tablespace"句で設定します。
ユーザが特定の処理中にメモリ上で処理しきれなかったデータを一時的にハードディスク上で処理するための領域が一時表領域です。

[表領域の領域制限]
"quota サイズ on 表領域名"で指定します。
このユーザがどの表領域をどれだけ使用していいかを設定します。

パスワードは "identified by"の後に記述します。

SQL> create user test920 identified by test920
  2  default tablespace test
  3  temporary tablespace temp
  4  quota unlimited on test;
ユーザーが作成されました。

ユーザ作成後は、そのユーザに対してデータベースへの接続権限を与えます。作成しただけではデータベースに接続できません。以下は接続できない例です。

SQL> connect test920/test920
ERROR:
ORA-01045: user TEST920 lacks CREATE SESSION privilege; logon denied


警告: Oracleにはもう接続されていません。

このエラーメッセージでは、test920ユーザにはCREATE SESSIONという権限が与えられていないために接続が拒否されたことを表しています。test920ユーザに接続するための権限を与える方法は2つあります。一つは、CREATE SESSION 権限を直接与えることです。
もう一つは、CREATE SESSION 権限を含んだ"ロール"を与えることです。ロールとは、一つ以上の権限をまとめて与えられるようにしたセットです。Oracleで用意しているロールのうち、一般的に使われることが多いのは以下の3つです。

CONNECT データベースへの接続や表などの作成をする権限
RESOURCE 表領域への無制限アクセスやプロシージャ、トリガーなどの作成をする権限
DBA データベースに対するすべてのシステム権限

CONNECTロールをtest920ユーザに与えます。

SQL> grant connect to test920;

権限付与が成功しました。

それではtest920ユーザで接続してみましょう。また、ユーザについての情報を確認するためのビューもチェックします。

SQL> connect test920/test920
接続されました。
SQL> select * from user_users;

USERNAME                          USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
LOCK_DAT EXPIRY_D DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
-------- -------- ------------------------------ ------------------------------
CREATED  INITIAL_RSRC_CONSUMER_GROUP
-------- ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
TEST920                                26 OPEN
                  TEST                           TEMP
03-04-17 DEFAULT_CONSUMER_GROUP

3. 表の作成
それでは、表を作ってみましょう。以下のSQLを入力してください。
(大文字、小文字は区別しません)

SQL> CREATE TABLE EMP
  2  (EMPNO NUMBER(4),
  3  ENAME VARCHAR2(10),
  4  JOB VARCHAR2(9),
  5  MGR NUMBER(4),
  6  HIREDATE DATE,
  7  SAL NUMBER(7, 2),
  8  COMM NUMBER(7, 2),
  9  DEPTNO NUMBER(2));

表が作成されました。
SQL>
表を作成する時には、
CREATE TABLE 表名 (列名 データ型 [, …]);
とします。データ型とは、その列にどんな種類のデータがどの程度のサイズで格納されるかを決めるものです。EMP表に含まれるデータ型は以下のとおりです。
NUMBER … 数値データ。(全体の桁数)もしくは(全体の桁数, 小数桁数)と書きます。
VARCHAR2 … 可変長文字列。カッコ内はバイト数です。最大4000バイトまで定義できます。
DATE … 秒単位の精度を持つ日付と時刻データ

作成したのはEMP表です。さっきSCOTTユーザでログインしたとき検索したのもEMP表です。このようにデータベース上では、異なるスキーマであれば同じ名前の表が存在していても構いません。もし、TEST920ユーザにSCOTTユーザのEMP表を検索する権限が与えられている場合には、以下のようにして検索することができます。

-- SCOTTユーザで接続します
SQL> connect scott/tiger
接続されました。
-- SCOTTスキーマのEMP表に対する検索権限をTEST920ユーザに与えます
SQL> grant select on emp to test920;

権限付与が成功しました。
-- TEST920ユーザで接続します。
SQL> connect test920/test920
接続されました。
-- SCOTTスキーマのEMP表を検索します。
SQL> select empno, ename from scott.emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
(以下略)
-- TEST920スキーマのEMP表を検索します。
SQL> select empno, ename from emp;

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

上記のように別のスキーマの表を検索するには、検索権限が与えられる必要があります。検索するときのfrom句には"スキーマ名.表名"という書き方をします。スキーマ名を記述しない場合には、指定された表名を自分のスキーマから検索します。


4. 表にデータを挿入、更新、削除する
3で検索したとおり、TEST920スキーマのEMP表は空ですので、データを入れてみましょう。

SQL> INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK', 7902,
  2  '80-12-17',  800, NULL, 20);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,
  2  '81-02-20', 1600,  300, 30);

1行が作成されました。

SQL> insert into emp(empno, ename, job, sal)
  2  values (1414, '亀井', 'ANALYST', 500);

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17        800                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       1600        300         30
      1414 亀井       ANALYST                              500

データの挿入には、INSERT文を使います。1番目と2番目のINSERTのように"INSERT INTO 表名" の後にカッコで列名を列記しない場合には、すべての列に対してVALUES句でデータを記述する必要があります。3番目のように列名を列記している場合には、それらの列に対するデータをVALUES句で記述します。ENAME列やHIREDATE列などのようにNUMBER型以外の型で定義されている列のデータは''(半角のシングルクォーテーション)で囲みます。
データ挿入後に内容を確定させる場合には、"COMMIT;"と入力します。

それでは、次に全員の給料を一律で倍にして確定するという処理をしてみます。
給料を表すのはSAL列です。データを更新するには、UPDATE文を使用します。


SQL> update emp set sal=sal * 2;

3行が更新されました。
SQL> commit;

コミットが完了しました。

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17       1600                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       3200        300         30
      1414 亀井       ANALYST                             1000

さらに亀井さんの給料だけに500追加しましょう。

SQL> update emp set sal=sal + 500 where empno = 1414;

1行が更新されました。

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17       1600                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       3200        300         30
      1414 亀井       ANALYST                             1500

実行される対象の列に条件をつける場合にはWHERE句を使います。
しかし、変更をCOMMITで確定させる前に、亀井さんだけに追加することに異議が発生したので、残念ながら取り消しすることにします。このときは"ROLLBACK;" を実行すると前回のCOMMIT以降の変更が取り消しされます。

SQL> rollback;

ロールバックが完了しました。

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17       1600                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       3200        300         30
      1414 亀井       ANALYST                             1000

では次に、ALLENさんが会社を去るということで、彼についての行を削除します。WHERE句で条件を設定しないと、全員消えてしまいます。

SQL> delete from emp where empno=7499;

1行が削除されました。

SQL> commit;

コミットが完了しました。

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17       1600                    20
      1414 亀井       ANALYST                             1000

ここで、削除をCOMMITしたにも関わらず、ALLENさんが会社に復帰することになったと連絡がありました。データを戻したい場合、どんな対処が考えられるでしょうか。次回はOracle9iの機能を利用して復旧する方法をご紹介します。


◆ 終わりに

いろいろなコマンドを実行して試していただくために説明を大きく省いた部分などがあります。不明な点に関しては以下のマニュアルや市販の書籍などを参考に調べて見てください。体験して分からない点を調べるというサイクルを繰り返すことが上達の早道と思います。すべてOTNからダウンロードできます。以下のマニュアル名をクリックすると、9.2のドキュメントがダウンロードできます。

SELECT、INSERT、UPDATE、DELETE、GRANT、CREATE TABLESPACE、CREATE USER等のSQL文についての文法や例を確認したい場合:
Oracle9i SQLリファレンス

SQL*Plusの使い方についてもっと知りたい:
SQL*Plusユーザーズガイドおよびリファレンス

表や他のスキーマ・オブジェクト、ユーザや権限についてもっと知りたい:
Oracle9i データベース管理者ガイド
Oracle9i データベース概要