ORACLE(CREATE TABLESPACE PROFILE USER GRANT ALTER)

このページではoracleのインストール後に必要になる作業用環境について触れてます。

LinuxへのORACLEインストールが完了しました。「よし、テーブルでも作成してみよう。」、そのテーブルはどこの領域に作成しますか・・・？　この時点ではシステム領域しか存在せず、作成するオブジェクトは全てシステム領域内に生成されてしまいます。自分の作業領域は自分で作成するしかありません。

例えばCREATE TABLE文を作ったとしましょう。その「CREATE TABLE文」で指定する表領域はどこを指定してますか？
指定する場所は現時点ではシステム領域しかありません。省略してもエラーにはなりませんが、省略するとシステム領域に生成されてしまいます。この領域内にオブジェクトを追加したり削除したりする訳なので、操作ミスでシステム領域内のオブジェクトを削除してしまったり書き換えてしまったり・・・。個人用であれば再インストールで済むかも知れませんが、職場環境となると100%元に戻さなければなりません。

面倒に感じる方もいるとは思いますが、ORACLE環境で色々試すのであれば必須の作業です。一度準備してしまえば、その領域内であれば、テーブル追加したりデータ登録したり削除してみたりと、システム領域に影響を与えず自由に操作が行えます。

このページでは、「自分用の環境作成手順」を前準備として記載しました。下記に必要と思われる作業を①～⑥にまとめてみましたが、その中で大事な作業は「自分用の表領域を作成すること」「自分用のユーザを作成すること」「作成したユーザにSELECTしたりCREATEしたりDROPしたりできる権限を登録すること」です。これからORACLEを学ばれる方は是非参考にして下さい。

①表領域作成
②プロファイル作成
③ユーザ作成
④権限の付与
⑤作成したユーザでログイン
⑥作成したユーザでテーブル作成

ユーザ(study)用の表領域作成

表領域を作成します。この領域が作業領域になるので、作業で作成するテーブル等のオブジェクトもこの表領域内に作成します。
この領域内で作業している分には他に影響はありません。
表領域の作成にはcreate tablespace文を使います。sizeは後から増やせるので最初は小さめで良いです。segment space managementは、データ容量がsizeの値を超えたらどうするか？を設定します。今回は一般的なautoを指定します。autoは自動拡張なのでsizeを超えてもエラーにせず容量を拡張してくれます。そのため容量を気にせずデータを追加できますが、当然ながらLinuxで割り当てたディレクトリ容量は気にしなくてはなりません。

表領域作成

SQL> create tablespace study_01
　　　datafile /u01/app/oracle/dba_area/study_01.dbf
　　　size 15M
　　　segment space management auto;
2 3 4
表領域が作成されました。

SQL>

ユーザ(study)用の表領域を確認

表領域確認

SQL> set lin 1000
　　　col FILE_NAME for a45
　　　col TABLESPACE_NAME for a15
　　　select TABLESPACE_NAME,FILE_NAME
　　　,BYTES/1024/1024 "全体容量MB"
　　　,MAXBYTES/1024/1024 "自動拡張可能容量MB"
　　　,USER_BYTES/1024/1024 "ユーザ容量全体MB"
　　　,STATUS,AUTOEXTENSIBLE
　　　from dba_data_files
　　　where TABLESPACE_NAME = upper('study_01');
　　　set lin 80
SQL> SQL> SQL> 2 3 4 5 6 7
TABLESPACE_NAME FILE_NAME　　　　　　　　　　　全体容量MB 自動拡張可能容量MB ユーザ容量全体MB STATUS AUTO
--------------- --------------------------------------------- ---------- ------------------ ---------------- --------- ---
STUDY_01　　/u01/app/oracle/dba_area/study_01.dbf　　　15　　　　 0　　　　　　　　　14　AVAILABLE NO

SQL> SQL>

ユーザ(study)のプロファイル作成

profileは、セッション数を制限したり、ログインの失敗回数を制限したり、パスワードの有効期間を管理したりと、何らかの制限を加えたり管理したりします。
failed_login_attemptsはログイン失敗回数です。今回は1で設定してますが、一般的には3が多いかと思います。ココの値は、後から変更できるので一旦登録して適宜変更して下さい。

プロファイル作成

SQL> create profile study_prof limit
　　　connect_time default
　　　failed_login_attempts 1
　　　password_life_time 90
　　　password_lock_time 30
　　　password_grace_time 10
　　　;
2 3 4 5 6 7
プロファイルが作成されました。

SQL>

ユーザ(study)のプロファイル確認

プロファイルの確認

SQL> set lin 300
　　　set pagesize 300
　　　 select * from dba_profiles where profile = upper('study_prof') order by profile;
　　　set pagesize 14
　　　set lin 80
SQL> SQL>
PROFILE　　　　　RESOURCE_NAME　　　　RESOURCE　　LIMIT
---------------- -------------------------------- -------- ----------------------------------------
STUDY_PROF 　COMPOSITE_LIMIT　　　　　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　SESSIONS_PER_USER　　　　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　CPU_PER_SESSION　　　　　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　CPU_PER_CALL　　　　　　　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　LOGICAL_READS_PER_SESSION　　　　KERNEL　　　DEFAULT
STUDY_PROF 　LOGICAL_READS_PER_CALL　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　IDLE_TIME　　　　　　　　　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　CONNECT_TIME　　　　　　　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　PRIVATE_SGA　　　　　　　　　　　　　KERNEL　　　DEFAULT
STUDY_PROF 　FAILED_LOGIN_ATTEMPTS　　　　　　　PASSWORD　　1
STUDY_PROF 　PASSWORD_LIFE_TIME　　　　　　　　　PASSWORD　　90
STUDY_PROF 　PASSWORD_REUSE_TIME　　　　　　　　PASSWORD　　DEFAULT
STUDY_PROF 　PASSWORD_REUSE_MAX　　　　　　　　PASSWORD　　DEFAULT
STUDY_PROF 　PASSWORD_VERIFY_FUNCTION　　　　PASSWORD　　DEFAULT
STUDY_PROF 　PASSWORD_LOCK_TIME　　　　　　　　PASSWORD　　30
STUDY_PROF 　PASSWORD_GRACE_TIME　　　　　　　PASSWORD　　10

16行が選択されました。

SQL> SQL> SQL>

ユーザ(study)作成

ユーザIDをstudyという名称で作成してます。
default tablespaceはユーザの作成先なので、先ほど生成した領域であるstudy_01を指定します。指定し忘れるとシステム領域に生成されてしまいますので注意してください。
quota 13M on study_01の意味は、このユーザに13Mの領域を与え、それ以上は使えなくする設定です。生成したstudy_01表領域が13Mで生成したので、その範囲内にしてます。quota容量は後から変更できます。また、profileを指定してユーザ管理情報を与えて下さい。

ユーザ作成

SQL> create user study
　　　identified by study
　　　default tablespace study_01
　　　temporary tablespace TEMP
　　　quota 13M on study_01
　　　profile study_prof
　　　;
2 3 4 5 6 7
ユーザーが作成されました。

SQL>

ユーザ(study)の確認

ユーザの確認

SQL> set lin 200
　　　set pagesize 200
　　　col username for a20
　　　col password for a15
　　　col ACCOUNT_STATUS for a25
　　　col DEFAULT_TABLESPACE for a15
　　　col TEMPORARY_TABLESPACE for a15
　　　col PROFILE for a15
　　　select username,ACCOUNT_STATUS,default_tablespace,temporary_tablespace,CREATED,PROFILE
　　　 from dba_users
　　　 where username = upper('study') ;
　　　set pagesize 14
　　　set lin 80
SQL> SQL> 2 3 USERNAME　ACCOUNT_STATUS　DEFAULT_TABLESP　TEMPORARY_TABLE　CREATED　PROFILE
-------------------- ---------------------------- ------------------------ --------------------- -------------- ---------------
STUDY　　　　　　OPEN　　　　　　　　　STUDY_01　　　　TEMP　　　　20-02-10　STUDY_PROF

SQL> SQL> SQL>

ユーザ(study)に権限の付与

create sessionは作成したユーザstudyにログイン権限を与えてます。これをしないと例えばsqlplusからログインできません。
default roleは今回は付与しないnoneを指定してます。後から変更できるので、roleを使用する場合にはnoneをallなどに変更できます。
create tableは、ユーザstudyで新規にテーブル作成ができる権限です。既存テーブルのselectやupdateが出来ても、この権限がないとcreate tableできません。同様に、例えばcreate view権限がないと、新規にビューを作成することができないので、権限は適宜与えて下さい。

権限の付与

SQL> grant create session to study;

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

SQL>
SQL> alter user study default role none;

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

SQL>
SQL> grant create table to study;

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

SQL>

ユーザ(study)の権限の確認

権限の確認

SQL> select * from user_sys_privs where username = upper('study') ;

USERNAME　　　　　　　　PRIVILEGE　　　　　　　　　　　　　ADM
------------------------------ ---------------------------------------- ------
STUDY　　　　　　　　　CREATE TABLE　　　　　　　　　　　　NO
STUDY　　　　　　　　　CREATE SESSION　　　　　　　　　　　NO

SQL>

ログアウト

作業領域とユーザが作成できました。これでテーブル作成したり削除したりデータ投入したり、色々試せる環境が整ったので新規に作成したユーザIDでログインしてから始めます。

ログアウト

SQL>
SQL> exit;
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Productionとの接続が切断されました。
[server@localhost ~]$
[server@localhost ~]$

ユーザ(study)でログイン

新規に作成したユーザIDでログインします。

ログイン

[server@localhost tokyo]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on 月 2月 17 11:16:07 2020

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ユーザー名を入力してください: study
パスワードを入力してください:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
に接続されました。
SQL>
SQL> show user
ユーザーは"STUDY"です。
SQL>

ユーザ(study)が所有するオブジェクトを確認

作成したばかりのユーザなので、現時点でオブジェクトは何も無いですが、ユーザが持っているオブジェクトに何があるかを確認するには、このuser_objectを確認すると良いでしょう。
ちなみに、ココで言っているオブジェクトとは作成したもの全てになるので、テーブルだけに限らず、インデックスを作成すればココにインデックスも表示されます。トリガーを作成すればトリガーも、その他シーケンス、シノニム、プロシジャー・・・など。所有するオブジェクト全てが表示されることになります。

オブジェクトを確認

ユーザ(study)所有のテーブル作成

大事なことですが、tablespaceで自分の領域であるstudy_01を忘れずに指定して下さい。忘れるとシステム領域に作成されてしまいます。
職場などの失敗の許されない大事な本番環境でも、業務用テーブルがシステム領域に生成されているのを良く見かけます。業務で使用するテーブルを意図的にシステム領域内に生成する事はありえません。テーブル生成時に「指定忘れ」してるのです。

テーブル作成

SQL> create table study_table (
　　　 col_a number(5) NOT NULL
　　　,col_b number(3) NOT NULL
　　　,col_c number(3) NOT NULL
　　　,col_d number(2) NOT NULL
　　　,col_e number(1) NOT NULL
　　　,col_f varchar2(20) NOT NULL
　　　,col_g varchar2(20) NOT NULL
　　　)
　　　tablespace study_01
　　　;
2 3 4 5 6 7 8 9 10 11
表が作成されました。

SQL>

作成したテーブル確認

テーブル確認

SQL> set lin 500
　　　col OBJECT_NAME for a40
　　　col OBJECT_TYPE for a20
　　　col SUBOBJECT_NAME for a20
　　　col EDITION_NAME for a20
　　　select OBJECT_NAME,OBJECT_TYPE,OBJECT_TYPE,CREATED,LAST_DDL_TIME from user_objects;
　　　set lin 80
SQL> SQL> SQL> SQL> SQL>
OBJECT_NAME　　　　　　　　　OBJECT_TYPE　　　　　OBJECT_TYPE　CREATED　LAST_DDL
---------------------------------------- -------------------- -------------------- -------- --------
STUDY_TABLE　　　　　　　　　TABLE　　　　　　　TABLE　　　　　　　20-02-19　20-02-19

SQL> SQL>

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME　　　　　　TABLESPACE_NAME
------------------------------ ------------------------------
STUDY_TABLE　　　　　　STUDY_01

SQL>

SQL> desc study_table
名前 NULL? 型
----------------------------------------- -------- ----------------------------
COL_A NOT NULL NUMBER(5)
COL_B NOT NULL NUMBER(3)
COL_C NOT NULL NUMBER(3)
COL_D NOT NULL NUMBER(2)
COL_E NOT NULL NUMBER(1)
COL_F NOT NULL VARCHAR2(20)
COL_G NOT NULL VARCHAR2(20)

SQL>
SQL>

SQL> select count(*) from study_table;

COUNT(*)
----------
0

SQL>

ユーザ(study)からログアウト

ログアウト

SQL>
SQL> exit;
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Productionとの接続が切断されました。
[server@localhost ~]$

自分用の作業環境を構築して1テーブル作成してみました。
自分のユーザIDで自分の表領域内を操作してる分には他に影響ありません。本番前のテスト環境としても十分使用できます。

oracle初めの一歩をご覧いただきありがとうございました。