【Oracle】表領域について

１．背景

今のプロジェクトの会議で表領域の話題が出て気になったので。

２．環境

Windows 11 Pro 22H2

Oracle Database 21c Express Edition

Visual Studio Code 1.94.2 (system setup)

３．Oracleの表領域とは？

Oracleでテーブルデータを格納する場所。
CREATE TABLE時に表領域を指定でき、
テーブルはその表領域上に作成される。

以下は表領域の種類の例。

SYSTEM 表領域

データベースの作成時に自動的に作成される表領域で、
データベースのオープン中は常にオンラインになる。

一時表領域

SQL文の処理中に生成された一時データを格納する表領域で、
主にソート処理に使用される。

SYSAUX 表領域

データベースの作成時に必ず作成され、
SYSTEM 表領域の補助表領域として機能する。

余談

表領域には、複数のスキーマのデータが格納されることがある。

スキーマは論理的なデータベースオブジェクト（テーブル、ビュー、インデックスなど）のまとまりで、ユーザーによって所有される。

表領域を作成する際は、CREATE TABLE時に指定する。指定しない場合は、
各ユーザーに設定されたデフォルト表領域上に作成される。

４．表領域を確認するSQL

COLUMN 表領域名 FORMAT A20
COLUMN 合計サイズ_MB FORMAT 999,999,999.99
COLUMN 使用済みサイズ_MB FORMAT 999,999,999.99
COLUMN 使用率_パーセント FORMAT 999.99

SELECT
    a.tablespace_name AS 表領域名,
    ROUND(SUM(a.bytes) / 1024 / 1024, 2) AS 合計サイズ_MB,
    ROUND(SUM(a.bytes - NVL(b.bytes, 0)) / 1024 / 1024, 2) AS 使用済みサイズ_MB,
    ROUND((SUM(a.bytes - NVL(b.bytes, 0)) / SUM(a.bytes)) * 100, 2) AS 使用率_パーセント
FROM
    dba_data_files a
LEFT JOIN
    (SELECT tablespace_name, file_id, SUM(bytes) AS bytes
     FROM dba_free_space
     GROUP BY tablespace_name, file_id) b
ON
    a.tablespace_name = b.tablespace_name AND a.file_id = b.file_id
GROUP BY
    a.tablespace_name
ORDER BY
    a.tablespace_name;

５．実行結果

C:\Users\username>sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 日 10月 27 16:04:48 2024
Version 21.3.0.0.0

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



Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SQL> COLUMN 表領域名 FORMAT A20
SQL> COLUMN 合計サイズ_MB FORMAT 999,999,999.99
SQL> COLUMN 使用済みサイズ_MB FORMAT 999,999,999.99
SQL> COLUMN 使用率_パーセント FORMAT 999.99
SQL>
SQL> SELECT
  2      a.tablespace_name AS 表領域名,
  3      ROUND(SUM(a.bytes) / 1024 / 1024, 2) AS 合計サイズ_MB,
  4      ROUND(SUM(a.bytes - NVL(b.bytes, 0)) / 1024 / 1024, 2) AS 使用済みサイズ_MB,
  5      ROUND((SUM(a.bytes - NVL(b.bytes, 0)) / SUM(a.bytes)) * 100, 2) AS 使用率_パーセント
  6  FROM
  7      dba_data_files a
  8  LEFT JOIN
  9      (SELECT tablespace_name, file_id, SUM(bytes) AS bytes
 10       FROM dba_free_space
 11       GROUP BY tablespace_name, file_id) b
 12  ON
 13      a.tablespace_name = b.tablespace_name AND a.file_id = b.file_id
 14  GROUP BY
 15      a.tablespace_name
 16  ORDER BY
 17      a.tablespace_name;

    表領域名               合計サイズ_MB 使用済みサイズ_MB 使用率_パーセント
-------------------- --------------- ----------------- -----------------
SYSAUX                        670.00            607.44             90.66
SYSTEM                      1,350.00          1,343.56             99.52
UNDOTBS1                      115.00             36.06             31.36
USERS                           5.00              2.69             53.75

SQL>

６．参考リンク