目次
はじめに
こんにちは！当社に新卒で入社してそれなりに経つ「ゆうき」と申します。
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表領域と同じようにデータベース・ファイルの拡張ができました。
おわりに
本記事では、実際の作業コンソールログをそのまま掲載してみましたが、いかがでしたでしょうか。
人に見やすいログを残せるということは、他人にだけではなく、未来の自分にも助けになることがあります。
別に汚いログでもエンジニアとして生きていけますが、
エンジニアだからこそ、工夫しながらお仕事していけたらいいなと思っています。
投稿者プロフィール
