【Oracle実験室】No.0014「実験のためのスキーマ環境構築」

No.0014(構築)
実験のためのスキーマ環境構築

■環境

　　□OS：　Windows XP Professional SP3
　　□Oracle：　Oracle Database 11g R2 (Standard)
　　□OSログインユーザ：　ORA_DBAグループ

■経緯

　　今後行うSQL実行環境としてのサンプルデータベースを作成する
　　サンプルデータベースは、4年制大学の学生名簿という想定でテーブルを構築する。
　　実験用に1スキーマを作成する
　　現在、環境構築は、このページにあるような手作業ではなく、バッチ実行で行うことができます。
　　→こちらからスクリプトを入手できます。
　　このスクリプトで構築できる環境は、このページで行った作業で構築できる環境とは異なります。
　　(スクリプトのほうが日々バージョンアップしているため)

■実験

　　1.　SYSDBAで接続し、CREATE USER文で、スキーマ「dicdic」を作成する。


SQL> conn /as sysdba
接続されました。
SQL> CREATE USER dicdic
  2  IDENTIFIED BY Oracle11g
  3  DEFAULT TABLESPACE USERS
  4  TEMPORARY TABLESPACE TEMP;

ユーザーが作成されました。
SQL>

　　2.　作成したユーザーで接続してみる。ユーザーを作っただけではダメで、CreateSession権限が与えられていないと接続すらできないことがわかる。


SQL> conn dicdic/Oracle11g
ERROR:
ORA-01045: ユーザーDICDICにはCREATE SESSION権限がありません。ログオンが拒否されました。


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

　　3.　SYSDBA権限で接続しなおして、create session権限を付与する。


SQL> conn /as sysdba
接続されました。
SQL> grant create session to dicdic;

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

SQL>

　　4.　DBAの接続を切断して、こんどこそ作成したスキーまでの接続が可能になったことを確認する。


SQL> disc
Oracle Database 11g Release 11.2.0.1.0 - Productionとの接続が切断されました。
SQL> conn dicdic/Oracle11g
接続されました。
SQL>

　　5.　さて、これだけではまだ足りない。SYSDBAで接続しなおして、CreateTable権限と、表領域に対する権限を付与する。今後の可用性を考慮し、このふたつはロールを経由して付与する。だから、ここではそのためのロールを作り、ロールに権限を付与して、そのロールをスキーマに付与する。ロール名はdicdic_raleとし、使用可能表領域サイズは300MBとする。


SQL> conn /as sysdba
接続されました。

SQL> CREATE ROLE dicdic_role;

ロールが作成されました。

SQL> GRANT CREATE TABLE TO dicdic_role;

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

SQL> GRANT dicdic_role TO dicdic;

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

SQL> ALTER USER dicdic QUOTA 300M ON USERS;

ユーザーが変更されました。
SQL>

　　6.　いよいよテーブル作成、予め準備したCreateTable文でテーブルを作成する。


SQL> conn dicdic/Oracle11g
接続されました。

SQL> CREATE TABLE TT01_学生
  2    (学生番号     CHAR(9) CONSTRAINT PK_TT01 PRIMARY KEY,
  3     氏名       VARCHAR2(20) NOT NULL,
  4     氏名かな         VARCHAR2(40) NOT NULL,
  5     学科コード         NUMBER(4) NOT NULL,
  6     在籍区分コード       VARCHAR2(2) NOT NULL,
  7     最終学歴コード       NUMBER(1) NOT NULL,
  8     単位数       NUMBER(3) ,
  9     TOEIC       NUMBER(4) ,
 10     生年月日       NUMBER(8) NOT NULL
 11     );

表が作成されました。

SQL>
SQL> CREATE TABLE TM01_最終学位
  2    (学位コード     NUMBER(1) CONSTRAINT PK_TM01 PRIMARY KEY,
  3     最終学位       VARCHAR2(20) NOT NULL
  4     );

表が作成されました。

SQL>
SQL> CREATE TABLE TM02_学部
  2    (学部コード     NUMBER(2) CONSTRAINT PK_TM02 PRIMARY KEY,
  3     学部名       VARCHAR2(20) NOT NULL
  4     );

表が作成されました。

SQL>
SQL> CREATE TABLE TM03_学科
  2    (学科コード     NUMBER(4) CONSTRAINT PK_TM03 PRIMARY KEY,
  3     学科名       VARCHAR2(40) NOT NULL,
  4     学部コード     NUMBER(2) NOT NULL
  5     );

表が作成されました。

SQL>
SQL> CREATE TABLE TM04_在籍区分
  2    (在籍区分コード     VARCHAR2(2) CONSTRAINT PK_TM04 PRIMARY KEY,
  3     在籍区分       VARCHAR2(20) NOT NULL
  4     );

表が作成されました。
SQL>

　　7.　このあと、データの流しこみを行った。データの投入は予め用意したTSVファイルをもとに、Oracleに対してODBC接続したAccess経由で行った。
　　現在、環境構築は、このページにあるような手作業ではなく、バッチ実行で行うことができます。
　　→こちらからスクリプトを入手できます。
　　このスクリプトで構築できる環境は、このページで行った作業で構築できる環境とは異なります。
　　(スクリプトのほうが日々バージョンアップしているため)

■まとめ

　　□　最低限のスキーマ環境を構築するには、以下の流れが必要。
　　　　　スキーマ作成(Create User文)
　　　　　session権限付与(Grant文)
　　　　　CreateTable権限付与(Grant文)
　　　　　表領域内の融通サイズ指定(ALTER USER文)
　　　　　適宜権限をロールに包むなども必要。

【ＰＲ】 Oracle実験室監修 『Oracleアーキテクチャ鳥瞰図』ＰＤＦ （Oracleのアーキテクチャを一目で俯瞰できるイラスト）

Home －実験室－ No.0014

ブログランキング