⇧ amaizing...
Oracle Database のユーザー（スキーマ）と表領域の関係は？
とりあえず、
⇧ 上記の記事で、「WSL 2（Windows SubSystem for Linux 2）」の「Oracle Linux 8.7」に「Oracle Database 23c（Free-Developer Release）」をインストールして、データベースに接続するところまでできたので、「ユーザー（スキーマ）」を追加していこうと思うのですが、公式のドキュメントによりますと、
DEFAULT TABLESPACE句
ユーザーのスキーマ内に作成されるオブジェクトを格納するデフォルトの表領域を指定します。この句を省略した場合、ユーザーのオブジェクトはデータベースのデフォルトの表領域に格納されます。データベースのデフォルトの表領域が指定されていない場合、ユーザーのオブジェクトはSYSTEM表領域に格納されます。
⇧ とあって、「表領域」なるものが必要らしい。
表領域と物理データファイル（xxx.dbf）
「Oracle Database 23c（Free-Developer Release）」を「Oracle Preinstallation RPM」でインストールした場合に自動的に作成される「PDB（Pluggable DataBase）」の「FREEPDB1」の表領域一覧を確認してみる。
select tablespace_name from dba_tablespaces;
TABLESPACE_NAME SYSTEM SYSAUX UNDOTBS1 TEMP USERS
で、公式のドキュメントによりますと、
Oracle Databaseは、表領域と呼ばれる小さい論理領域に分割されています。
各表領域は、1つ以上の物理データファイルに対応しています。データファイルには、表や索引など、論理データベース構造の内容が含まれています。各データファイルを関連付けられる表領域およびデータベースは1つのみです。
⇧「xxx.dbf」というファイルと「表領域」は関係してるっぽい。（Windows用のドキュメントだけど）
「Oracle Linux 8.7」で「Oracle Database 23c（Free-Developer Release）」を「Oracle Preinstallation RPM」でインストールした場合、「/opt/oracle/oradata/FREE」ディレクトリに配置されていた。
表領域とは
公式のドキュメントの「CREATE TABLESPACE」の説明に、
⇧『表領域については、『Oracle Database概要』を参照してください。』とあるので、リンクを押下すると、以下のページに遷移しました。
Physical Storage Structures
The physical database structures are the files that store the data.
When you execute a CREATE DATABASE statement, the following files are created:
-
Data files
Every Oracle database has one or more physical data files, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the data files.
-
Control files
Every Oracle database has a control file. A control file contains metadata specifying the physical structure of the database, including the database name and the names and locations of the database files.
-
Online redo log files
Every Oracle Database has an online redo log, which is a set of two or more online redo log files. An online redo log is made up of redo entries (also called redo log records), which record all changes made to data.
Many other files are important for the functioning of an Oracle database server. These include parameter files and networking files. Backup files and archived redo log files are offline files important for backup and recovery.
⇧ データベースが作成される時に、作成されるファイルの1部である「Data files」の内の1つが「xxx.dbf」ってことらしいんだけど、『表領域については～』の遷移先として、上記の説明が適切なのかが全く分からん...
「Data files」が以下の「Data File」と同じ意味合いなのかは不明ですが、
⇧「表領域」を作成するには、物理的なファイルが必要ということらしい。
Overview of Tablespaces
A tablespace is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files.
A database must have the SYSTEM and SYSAUX tablespaces. The following figure shows the tablespaces in a typical database. The following sections describe the tablespace types.
⇧ 上図のようなイメージらしい。
表領域は全部で3種類？
で、公式のドキュメントによりますと、
表領域には次の3つのタイプがあります。
-
永続的
永続表領域を使用して、ユーザーおよびアプリケーション・データを格納します。Oracle Databaseでは、永続表領域を使用して、システム・データなどの永続データが格納されます。各ユーザーにデフォルトの永続表領域が割り当てられます。
-
自動UNDO管理モードで実行されているデータベースは、UNDO表領域にUNDOデータを透過的に作成し、管理します。Oracle Databaseでは、トランザクションのロールバック、読取り一貫性、データベース・リカバリ、およびOracle Flashback Queryなどの機能を提供する目的でUNDOデータが使用されます。データベース・インスタンスが持つことができるアクティブなUNDO表領域は1つのみです。
-
一時
一時表領域は、SQL文によるソート処理の実行時に作成されるような一時的なデータの保存に使用されます。Oracle Databaseは、データベースの作成時に一時表領域を確保します。一時表領域グループを作成する場合は、さらに一時表領域を作成します。一般的な状況では、追加の一時表領域を作成する必要はありません。極度に大きなデータベースがある場合に、追加の一時表領域を構成することをお薦めします。
物理ファイルはデータファイルとは対照的に一時ファイルと呼ばれる一時表領域を構成します。
TEMP表領域は通常、一時表領域を明示的に割り当てられていないユーザーに対するデフォルトの一時表領域として使用します。
⇧「表領域」は全部で3種類に分けられるらしい。
ただ、他のドキュメント（『https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-3502CA78-FBC9-4927-B455-0ECB22E53066』）だと、
- Permanent Tablespaces（永続的）
- Temporary Tablespaces（一時）
の2つで大別してるっぽくて噛み合わない...
ユーザー作成には表領域が必要ってのは分かったのだが...
で、ユーザーを作成する際に「表領域（おそらく、『Permanent Tablespaces（永続的）』）」が必要ってことが分かったのだけど、新規の「表領域」を作成する際に新しく「xxx.dbf」を作成しておく必要があるのかが分からん...
「表領域」「一時表領域」を作成後、「ユーザー」を作成。「ユーザー」に権限を付与
実際に、「表領域」「一時表領域」を作成後、「ユーザー」を作成。「ユーザー」に権限を付与してみた。
-- 表領域を作成
CREATE TABLESPACE dev_web DATAFILE '/opt/oracle/oradata/FREE/dev_web.dbf'
SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
-- 一時表領域を作成
CREATE TEMPORARY TABLESPACE temp_dev_web TEMPFILE '/opt/oracle/oradata/FREE/temp_dev_web.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
-- ユーザー（スキーマ）作成
CREATE USER dev_web
IDENTIFIED BY pass
DEFAULT TABLESPACE dev_web
TEMPORARY TABLESPACE temp_dev_web
QUOTA UNLIMITED ON dev_web;
--ユーザー（スキーマ）に権限付与
GRANT ALL PRIVILEGES TO dev_web;
⇧ SQL文で指定した物理ファイル名を作成してくれるっぽい。
新しく作成した「ユーザー」でデータベースに接続できました。
新しく作成したユーザーでDB接続し直して、テーブルを作成
新しく作成したユーザーでDB接続し直しておきます。
んでは、テーブルを作成してみます。
IPAの「データベーススペシャリスト試験（DB）」の「2022年度、令和4年度」「午後Ⅱ」「問題冊子」を抜粋して参考にしました。
1.施設(施設コード, 施設区分,施設名,住所,電話番号, ...) 2.客室タイプ (客室タイプコード, 客室タイプ名、定員, 階数, 部屋数, 間取り面積, ペット同伴可否, 備考, ...) 3.価格区分(価格区分コード, 価格区分名,標準単価、価格設定規則) 4.施設客室タイプ (施設コード, 客室タイプコード, 価格区分コード) 5.客室(施設コード, 客室タイプコード, 客室番号, 禁煙喫煙区分, 客室状態, 備考) 6.客室状況(施設コード, 客室番号,年月日,予約可否) 7.客室在庫 (施設コード, 客室タイプコード, 禁煙喫煙区分, 年月日, 予約可能数, 割当済数) 8.プラン(施設コード, プランコード, プラン名, チェックイン時刻, チェックアウト時刻, 開始年月日, 終了年月日, 朝食有無, 夕食有無, 禁煙喫煙区分,備考) 9.プラン明細 (施設コード, プランコード, 客室タイプコード, 利用料金, 連泊割引率) 10.会員(会員番号,氏名,カナ氏名,メールアドレス,電話番号,生年月日,住所,•••) 11.オプション(施設コード, オプション番号, オプション名,単価, ...) 12.予約(施設コード,予約番号, プランコード, 客室タイプコード,予約状態, 会員予約区分, 当日予約フラグ, 利用開始年月日, 泊数, 人数, 客室数, キャンセル年月日, ...) 13.会員予約(施設コード, 予約番号, 会員番号) 14.非会員予約 (施設コード, 予約番号,氏名, カナ氏名、メールアドレス、電話番号、住所) 15.オプション予約 (施設コード, 予約番号, オプション予約明細番号, オプション番号, 利用数, ...) 16.宿泊(施設コード, 宿泊番号, 客室番号,予約番号, 人数, チェックイン年月日, チェックアウト年月日) 17.宿泊者(施設コード, 宿泊番号, 明細番号,氏名,カナ氏名,住所,電話番号, 前泊地, 後泊地) 18.オプション利用 (施設コード, 宿泊番号, オプション利用番号, オプション番号,利用数, 請求番号,請求明細番号) 19.請求(請求番号,施設コード,宿泊番号,宿泊料金, オプション利用料金, 請求合計金額) 20.請求明細(請求番号, 請求明細番号, 請求金額)
⇧ 試しに、「施設」テーブルを作成してみます。
-- マスターテーブル -- 論理名：施設 -- 物理名：W_M_FACILITY CREATE TABLE dev_web.W_M_FACILITY ( FACILITY_CODE VARCHAR2(3) NOT NULL ,FACILITY_CLASS CHAR(1) NOT NULL ,FACILITY_NAME VARCHAR2(160) NOT NULL ,ADDRESS VARCHAR2(255) NOT NULL ,TEL VARCHAR2(13) NOT NULL ,CREATE_TIME TIMESTAMP NOT NULL ,UPDATE_TIME TIMESTAMP NOT NULL ) TABLESPACE dev_web; -- インデックス CREATE UNIQUE INDEX I_W_M_FACILITY ON W_M_FACILITY (FACILITY_CODE); -- 主キー ALTER TABLE dev_web.W_M_FACILITY ADD CONSTRAINT P_W_M_FACILITY PRIMARY KEY (FACILITY_CODE) USING INDEX I_W_M_FACILITY; -- 論理名（テーブル名） COMMENT ON TABLE dev_web.W_M_FACILITY IS '施設'; -- 論理名（カラム名） COMMENT ON COLUMN dev_web.W_M_FACILITY.FACILITY_CODE IS '施設コード'; COMMENT ON COLUMN dev_web.W_M_FACILITY.FACILITY_CLASS IS '施設区分'; COMMENT ON COLUMN dev_web.W_M_FACILITY.FACILITY_NAME IS '施設名'; COMMENT ON COLUMN dev_web.W_M_FACILITY.ADDRESS IS '住所'; COMMENT ON COLUMN dev_web.W_M_FACILITY.TEL IS '電話'; COMMENT ON COLUMN dev_web.W_M_FACILITY.CREATE_TIME IS '作成日時'; COMMENT ON COLUMN dev_web.W_M_FACILITY.UPDATE_TIME IS '更新日時';
⇧ テーブル作成できました。
マスター系のテーブルには履歴テーブルを持たした方が良いのかも知らんけど、IPAの問題見る限り、施設名とか変更する想定とか無いのかな？
もしくは、施設名が変更になったら、新規レコード追加するような運用を考えてるんかな？
とりあえず、テーブルを追加できたということで。
毎度モヤモヤ感が半端ない....
今回はこのへんで。
