はじめに

こんにちは!当社に新卒で入社してそれなりに経つ「ゆうき」と申します。
Oracle Databaseも携わっていますが、BigDataやCloudなどの最新技術からLTOのテープ輸送手配まで
いろいろな経験をさせていただいています。

今回は、運用中のお客様先で立て続けにデータベース・ファイルの拡張に失敗した事例を耳にしたので、
ベテランでもつまづきやすい、データベースの容量追加にまつわるお話を書いてみたいと思います。

本記事では、実機操作を通してOracle Databaseの理解を深めることを目的としており、
お客様の環境での動作を保証するものではありません。

データベース・ファイルの追加とは

今回の説明では、「容量が不足したから追加しましょう!」という
作業を通してOracle Databaseの理解を深めます。
容量が不足すると、例えば以下のようなエラーが表示されます。

SQL> insert into TEST_TBL values(11,'AA',dbms_random.string('U',2000));
insert into TEST_TBL values(11,'AA',dbms_random.string('U',2000))
*
ERROR at line 1:
ORA-01653: unable to extend table TEST_TBL by 128 in tablespace TEST_SMALL


SQL>

このエラーから、レコードの挿入の際TEST_TBL表を拡張できなかったことがわかります。
TEST_TBL表が格納されているのはTEST_SMALL表領域です。
拡張できない状況を解消もしくは予防するために、データベース・ファイルの拡張や追加を実施します。

データベースの表(テーブル)にレコードを挿入すると、表領域に内容が格納されます。
表領域は、一つまたは複数のデータベース・ファイルから構成されます。

実機演習

Oracle Cloud上に作成したデータベースに、
表領域を新たに作成してその表領域に容量を追加してみます。

現在の状態を確認する

表領域を作成する前に、現在の状態を確認しておきましょう。
あらかじめ確認しておくことで、
既存と同じ名前で表領域を作成しようとしてしまうなどの
作業ミスを防止できます。
例示した以外にも大切な属性は多数ありますが、説明スペースの関係で確認範囲を絞っています。
setやcolで始まるコマンドは、
結果表示の文字幅を指定しているコマンドです。

[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 10 05:58:01 2019
Version 18.6.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0

SQL>
SQL>
SQL>
SQL> set linesize 120 pagesize 50000
SQL>

「set linesize 数字」と「set pagesize 数字」を
1行にまとめて記載しています。
linesizeは1行の文字数を、
pagesizeは指定した数のレコードごとに列名を繰り返し表示します。

 

 

 

 

 

SQL>
SQL>
SQL> col FILE_NAME for a60
SQL> col SIZE(MB) for 99,999,999

「column カラム名 format フォーマット指定」を
省略して記載しています。
「column」→「col」
「format」→「for」

 

 

 

 

 

SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            870
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5

SQL>
SQL>
SQL>
SQL> col BIGFILE for a7
SQL> select TABLESPACE_NAME, BIGFILE from dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ -------
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO

SQL>

表領域を作成する

SQL> set timing on
SQL> create tablespace TEST_SMALL datafile size 100M;

Tablespace created.

Elapsed: 00:00:01.58
SQL> set timing off
SQL>

ここで、ORA-02236エラーが出てしまう場合は、
対処を後述(「ORA-02236が発生した場合の対処」の章)していますので、
そちらで回避できるか確認してみてください。

「set timing on」とすることで、
SQLコマンドのタイミング統計が
最後に表示されるようになります。
今回は表領域を、1秒58で作成できたようです。

 

 

 

 

 

SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            870
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                        100

SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, BIGFILE from dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ -------
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
TEST_SMALL                     NO

6 rows selected.

SQL>

サイズ100MBのTEST_SMALL表領域が作成できました。

表領域を拡張する(データベース・ファイルを拡張)

SQL> set timing on
SQL> alter database datafile '+DATA/ORCL/DATAFILE/test_small.281.1013234399' resize 500M;

Database altered.

Elapsed: 00:00:03.84
SQL> set timing off
SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            870
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                        500

SQL>

先の手順で作成したデータベース・ファイルのサイズを100MBから500MBに拡張できました。

SQL>
SQL>
SQL> set timing on
SQL> alter database datafile '+DATA/ORCL/DATAFILE/test_small.281.1013234399' resize 32G;
alter database datafile '+DATA/ORCL/DATAFILE/test_small.281.1013234399' resize 32G
*
ERROR at line 1:
ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks


Elapsed: 00:00:00.02
SQL> set timing off
SQL>

ORA-01144エラーから、データベース・ファイル1つで扱えるブロック数の上限が4,194,303 blocksということがわかります。

SQL>
SQL>
SQL> select TABLESPACE_NAME, BLOCK_SIZE from DBA_TABLESPACES;

TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
SYSTEM                               8192
SYSAUX                               8192
UNDOTBS1                             8192
TEMP                                 8192
USERS                                8192
TEST_SMALL                           8192

SQL>
SQL>
SQL>
SQL> set timing on
SQL> alter database datafile '+DATA/ORCL/DATAFILE/test_small.281.1013234399' resize 33554424k;

Database altered.

Elapsed: 00:04:42.71
SQL> set timing off
SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            870
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                     32,768

SQL>

データベース・ブロック・サイズを確認し、データベース・ファイルを上限まで拡張しました。
算出方法は以下の通りです。
8k × 4,194,303blocks = 33,554,424k (約32767.99MB)

エラーメッセージから、上限より1block多いというのがわかっているので、
32Gから8kを引くという考え方もできますね。

 

 

 

 

 

この対処から、データベース・ブロック・サイズの設定によって、
データベース・ファイル1ファイルの上限サイズが変わることがわかります。
例えば、データベース・ブロック・サイズが16kならば、
データベース・ファイルの上限は67,108,848kとなります。

表領域を拡張する(データベース・ファイルを追加)

さらに表領域の容量が必要になった場合は、データベース・ファイルを追加します。

SQL> set timing on
SQL> alter tablespace TEST_SMALL add datafile size 100M;

Tablespace altered.

Elapsed: 00:00:01.48
SQL> set timing off
SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            870
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                     32,768
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.282.1013234911                        100

6 rows selected.

SQL>

TEST_SMALL表領域に対して、2つ目のデータベース・ファイルができました。
ここでマニュアルを参照してみます。

https://docs.oracle.com/cd/E96517_01/refrn/physical-database-limits.html#GUID-939CB455-783E-458A-A2E8-81172B990FE9
Oracle Database
Release 18

Database Reference
第IV部 付録
A データベースの制限事項
A.2 物理データベースの制限

の内容を確認すると、
1つの表領域に対して作れるデータベース・ファイルは1,022が上限
ということがわかりました。

気になることがあったときは、
インターネット検索エンジンを使うのも便利ですが、
Oracle Databaseはマニュアルが充実しています。
マニュアルバグでなければ正しい情報が載っていますので、
積極的にマニュアルを活用してみましょう。

 

 

 

 

 

よって、データベース・ブロック・サイズが8kの表領域では、
最大でも約31.94TBしかデータを格納できないことがわかります。

算出方法は以下の通りです。
33,554,424k × 1,022ファイル = 34,292,621,328k = 約31.94TB

あらゆるデータを一つの表領域に格納してしまうと、
この上限に到達した際に表領域の拡張では対処できなくなってしまいます。
あらかじめ設計や運用で、上限に到達しないシステムにするのが望ましいでしょう。

ORA-02236が発生した場合の対処

前述した演習では、Oracle Cloudで自動作成されたデータベースに対して作業を実施しています。
そのため、OMF(Oracle Managed Files)という機能が有効化されており、
データベース・ファイル名が自動で生成されるようになっています。
よって、OMFが無効の環境ではエラーが発生してしまいます。
OMFが無効の環境で作業する際は以下のように、
任意のデータベース・ファイル名を指定しましょう。

SQL> create tablespace TEST_SMALL datafile size 100M;
create tablespace TEST_SMALL datafile size 100M
                                      *
ERROR at line 1:
ORA-02236: invalid file name


SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
USERS                          /u01/app/oracle/datafile/orcl/users01.dbf                              5
UNDOTBS1                       /u01/app/oracle/datafile/orcl/undotbs01.dbf                          260
SYSAUX                         /u01/app/oracle/datafile/orcl/sysaux01.dbf                           820
SYSTEM                         /u01/app/oracle/datafile/orcl/system01.dbf                           730

4 rows selected.

SQL>
SQL>
SQL>
SQL> create tablespace TEST_SMALL datafile 'TEST_SMALL.dbf' size 100M;

Tablespace created.

SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
USERS                          /u01/app/oracle/datafile/orcl/users01.dbf                              5
UNDOTBS1                       /u01/app/oracle/datafile/orcl/undotbs01.dbf                          260
SYSAUX                         /u01/app/oracle/datafile/orcl/sysaux01.dbf                           820
SYSTEM                         /u01/app/oracle/datafile/orcl/system01.dbf                           730
TEST_SMALL                     /u01/app/oracle/product/11.2.0/dbhome_1/dbs/TEST_SMALL.dbf           100

5 rows selected.

SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, BIGFILE from dba_tablespaces;

TABLESPACE_NAME                BIG
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
TEST_SMALL                     NO

6 rows selected.

SQL>

サイズ100MBのTEST_SMALL表領域が作成できました。
データベース・ファイル名をフルパスで指定していない場合、

初期化パラメータファイルなどと同じディレクトリ配下にデータベース・ファイルが作成されます。

bigfile表領域について

前述の確認コマンドで気になった方もいらっしゃると思いますが、
表領域の作成時に、指定しないでコマンドを実行すると、
あらかじめ設定された表領域タイプ(デフォルトはsmallfile表領域)として表領域が作成されます。
コマンドでbigfile表領域を指定して表領域を作成してみましょう。

SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            880
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                     32,768
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.282.1013234911                        100

6 rows selected.

SQL>
SQL>
SQL>
SQL> col BIGFILE for a7
SQL> select TABLESPACE_NAME, BIGFILE from dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ -------
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
TEST_SMALL                     NO

6 rows selected.

SQL>
SQL>
SQL>
SQL> set timing on
SQL> create bigfile tablespace TEST_BIG datafile size 100M;

Tablespace created.

Elapsed: 00:00:01.90
SQL> set timing off
SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            880
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                     32,768
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.282.1013234911                        100
TEST_BIG                       +DATA/ORCL/DATAFILE/test_big.283.1013391051                          100

7 rows selected.

SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, BIGFILE from dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ -------
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
TEST_BIG                       YES
TEST_SMALL                     NO

7 rows selected.

SQL>
SQL>
SQL>
SQL> set timing on
SQL> alter tablespace TEST_BIG add datafile size 100M;
alter tablespace TEST_BIG add datafile size 100M
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace


Elapsed: 00:00:00.01
SQL> set timing off
SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            880
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                     32,768
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.282.1013234911                        100
TEST_BIG                       +DATA/ORCL/DATAFILE/test_big.283.1013391051                          100

7 rows selected.

SQL>

ORA-32771エラーからわかる通り、bigfile表領域のデータベース・ファイルは一つです。
しかし、smallfile表領域よりもデータベース・ファイル1つで扱えるブロック数の上限が多くなっています。
ここで、前述したマニュアルを再び確認すると、
2の32乗=4,294,967,296 blocksまでデータベース・ファイルを拡張できると書かれています。
よって、データベース・ブロック・サイズが8kの表領域では、
8k × 4,294,967,296 blocks = 34,359,738,368k = 32TB
まで拡張できるので、smallfile表領域とほぼ同等の容量が扱えます。

SQL>
SQL>
SQL> set timing on
SQL> alter database datafile '+DATA/ORCL/DATAFILE/test_big.283.1013391051' resize 500M;

Database altered.

Elapsed: 00:00:03.68
SQL> set timing off
SQL>
SQL>
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "SIZE(MB)" from DBA_DATA_FILES;

TABLESPACE_NAME                FILE_NAME                                                       SIZE(MB)
------------------------------ ------------------------------------------------------------ -----------
SYSTEM                         +DATA/ORCL/DATAFILE/system.261.1012010237                            880
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.262.1012010305                            600
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.263.1012010333                          130
USERS                          +DATA/ORCL/DATAFILE/users.274.1012011961                               5
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.281.1013234399                     32,768
TEST_SMALL                     +DATA/ORCL/DATAFILE/test_small.282.1013234911                        100
TEST_BIG                       +DATA/ORCL/DATAFILE/test_big.283.1013391051                          500

7 rows selected.

SQL>

bigfile表領域でも、smallfile表領域と同じようにデータベース・ファイルの拡張ができました。

おわりに

本記事では、実際の作業コンソールログをそのまま掲載してみましたが、いかがでしたでしょうか。
人に見やすいログを残せるということは、他人にだけではなく、未来の自分にも助けになることがあります。
別に汚いログでもエンジニアとして生きていけますが、
エンジニアだからこそ、工夫しながらお仕事していけたらいいなと思っています。

投稿者プロフィール

技術チーム
技術チーム
DBひとりでできるもんを盛り上げるべく、技術チームが立ち上がり早6年。ひとりでできるもんと言いつつ、技術者が読んでプッとなるような、極めてピンポイントでマニアックな技術ネタを執筆しています!