しばちょう先生の試して納得！DBAへの道 indexページ▶▶
私は、Oracle GRID Centerの設立当初からOracleの持つ最新技術をパートナー各社と共同で検証してきました。現在はお客様に最適なシステム導入の提案やパフォーマンス・トラブルの問題解決に従事しております。実機での経験をしているからこそ、瞬時にその機能の適用シナリオも含めて自信を持って自分の言葉（お客様に合わせた言葉）でお客様に提案できますし、早期にトラブル原因の当たりを付けることで解決のアイディアを閃いたりすることが可能になっていると思っています。
はじめまして、こんにちは。この連載を担当する”しばちょう”こと柴田長（しばた つかさ）と申します。
今回の連載は、正に体験して頂くことが主軸となります。単純な機能紹介ではなく手を動かして理解を深めて頂くような連載にしていきたいと考えております。これからDBAを目指される方、実機での作業から数年間離れていて最新機能をキャッチアップしたい方など、多くの方にご活用頂きたいと思います。
使用する環境は、Oracle Database 11g Release 2 Enterprise Editionのデータベースです。以下の資料を参考に作成してください。既存環境を使用されても問題ありませんが、一部の演習内容においてデータベース・コンフィギュレーション・アシスタント（DBCA）のデフォルト設定で作成した際の設定値を前提としているものがありますので、ご留意ください。
Oracle インストールとデータベースの作成・構築
【チュートリアル】意外と簡単!? Oracle Database 11g Release 2 – Windows版 「データベース構築編」（インストール～DB作成～初期設定）
さて、第1回目となる今回は、表や索引を格納する「表領域」に関する演習を実施してみましょう。
各演習問題について、回答例と補足をまとめていきますので、ご自身の回答と比較しながら実施して下さい。尚、SQL内で使用しているディレクトリ構造はLinuxとなっております。
■演習1．「TBS_SMALL」という名前のスモール・ファイル表領域を作成して下さい。ただし、この表領域は100MBのデータ・ファイルを3つで構成されることとします。
SQL> create tablespace TBS_SMALL datafile '/oracle_datafile1/tbs_small01.dbf' size 100M,
'/oracle_datafile1/tbs_small02.dbf' size 100M,
'/oracle_datafile2/tbs_small03.dbf' size 100M;
「スモール・ファイル表領域」という表現を初めて聞いた方もいらっしゃるかも知れませんが、Oracle Database10g Release 1からビッグ・ファイル表領域（大型ファイル表領域）が登場したことから、従来からの表領域はスモール・ファイル表領域（小型ファイル表領域）と呼ばれています。
ここで理解しておいて頂きたいことは、スモール・ファイル表領域は複数のデータ・ファイルで構成できるということです。これは一昔前の32bitのオペレーション・システムにおける1ファイルの最大サイズ制限（2GB）を超えるサイズの表領域を作成する際に非常に有効でした。もちろん、現在は64bit化が進んだことで2GB制限を意識する必要は無くなってきましたが、Oracle Databaseとしての制限に注意する必要があります。
スモール・ファイル表領域の1つのデータ・ファイルの最大サイズ ・[表領域のブロックサイズ] × 4194303 (= 2^22 -1) ' ブロックサイズが8KBの場合は、約32GB
1つのデータ・ファイル内のデータ・ブロック番号は22bitで管理されている為、上記の公式が成り立っています。また、ブロックサイズによって、データ・ファイルの最大サイズが変動することも読み取れるかと思います。
ちなみに、上記回答例のDDL（Data Definition Language）文のように、異なるディレクトリ上のデータ・ファイルで1つの表領域を構成することが可能ですが、Oracle Automatic Storage Management（以降、Oracle ASM）とビッグ・ファイル表領域を組み合わせて活用することで、こういった構成を考える必要が無くなることを今後どこかのタイミングでご紹介します。
SQL構文を完全に覚えていない場合でも「表領域を作成する」= 「create tablespace」とさえ思いつけば、Oracle Databaseのマニュアル「SQL言語リファレンス」で「create tablespace」部分の説明に辿り着くことができると思います。
■演習2．TBS_SMALL表領域のサイズをデータ・ディクショナリ・ビュー「DBA_DATA_FILES」で確認して下さい。
SQL> select tablespace_name, sum(bytes)/1024/1024 "size(MB)" from DBA_DATA_FILES
where tablespace_name = 'TBS_SMALL'
group by tablespace_name;
TABLESPACE_NAME size(MB)
------------------------------ ----------
TBS_SMALL 300
Oracle Databaseには数多くのデータ・ディクショナリ・ビューが用意されており、データベース内部の設定情報等を確認することが可能となっています。DDL文を実行するだけではなく、その設定結果を確認できることがDBAの業務として非常に重要となります。
この演習2で使用したデータ・ディクショナリ・ビュー「DBA_DATA_FILES」は、全てのデータ・ファイルの各種情報を確認することが可能なビューです。各データ・ファイルは必ず1つの表領域に属しますので、その情報も格納されています。上記のSELECT文では、TBS_SMALL表領域に属する3つのデータ・ファイルのサイズの合計（300MB = 100MB + 100MB + 1000MB）を問い合わせています。この結果が300MBではない場合は、演習1での表領域作成時に何かしらのミス・オペレーションを行った可能性がありますね。
ちなみに、データ・ディクショナリ・ビューの各カラムのデータが何を意味しているのかについては、マニュアル「リファレンス」に説明書きがありますので参考にして下さい。
■演習3．TBS_SMALL表領域に新たにデータ・ファイルを追加し、サイズを400MBへ拡張して下さい。
SQL> alter tablespace TBS_SMALL add datafile '/oracle_datafile2/tbs_small04.dbf' size 100M;
簡単過ぎますかね？スモール・ファイル表領域ではデータ・ファイルを追加することで、表領域のサイズを大きくすることが可能です。
■演習4．表領域TBS_SMALLのサイズを500MBへ拡張して下さい。
SQL> alter tablespace TBS_SMALL add datafile '/oracle_datafile2/tbs_small05.dbf' size 100M;
上記回答例のように、演習3で学んだ「新たに100MBのデータ・ファイルを追加して合計500MBへ拡張」した方、ありがとうございます。
ただし、これも確かに正解ではありますが、例えば表領域のサイズを50GBに拡張する場合、同じようにデータ・ファイルの追加を繰り返すでしょうか？「50GB / 100MB = 500」なので、100MBのデータ・ファイルを500個追加すれば良いですが、かなりの作業時間が必要になりますし、作成後のメンテナンスも大変になることが容易に想像できます。さらに、1つのスモール・ファイル表領域に含めることが可能なデータ・ファイルの数は「1022」が上限なので、無限にデータ・ファイルを追加し続けることはできません。
そこで、この演習4では、もうひとつの拡張方法である表領域を構成する既存のデータ・ファイルの拡張で対応します。
SQL> alter database datafile '/oracle_datafile1/tbs_small01.dbf' resize 125M; SQL> alter database datafile '/oracle_datafile1/tbs_small02.dbf' resize 125M; SQL> alter database datafile '/oracle_datafile2/tbs_small03.dbf' resize 125M; SQL> alter database datafile '/oracle_datafile2/tbs_small04.dbf' resize 125M;
もちろん、データ・ファイル「/oracle_datafile2/tbs_small04.dbf」を100MBから200MBに変更すれば、表領域のサイズは500MB（= 100 + 100 + 100 + 200）となりますが、この構成は「美しくない」でしょう。ベテランDBAであれば、きっちりと全てのデータ・ファイルのサイズが同じである方が「気持ち良い」はずです。
もちろん、これには幾つかの理由があります。1つの例としては、表領域上の表にデータが格納され始めると各データ・ファイルはほぼ均等に使用される為、100MBのデータ・ファイルから満杯になっていきます。その結果、200MBのデータ・ファイルのみしか空き領域が存在しない状態になった際、このデータ・ファイルへ書き込みが集中してしまいます。「/oracle_datafile」と「/oracle_datafile2」のパスを構成しているディスクが別々であった場合、特定のディスク（/oracle_datafile2）への書き込みが集中して性能が劣化する可能性を秘めていることになります。また、各ディスクからの読み込むデータ量にも偏りが生じる可能性もあります。
よって、今回は4つの既存のデータ・ファイルを全て均等な大きさに拡張しています。この作業を通じて感じて頂きたい点は2点あります。1点目は、表領域に対する変更作業のはずですが「alter tablespace」文ではなく「alter database」文でデータ・ファイルのサイズを変更しているということです。2点目は、データ・ファイルの数が多くなると管理作業が大変になりそうだなということです。
特に、RAWデバイス上にデータ・ファイルを配置している場合はデータ・ファイルの拡張が難しい為、表領域のサイズを拡張するとなると、管理すべきRAWデバイス数とデータ・ファイル数が増加することになります。しかも、既存のデータ・ファイルにはデータが大量に格納されているが新規に追加したデータ・ファイルは空という状態になる為、一旦データをエクスポートしてインポートし直すという運用を実施された経験がある方もいらっしゃると思います。この課題をOracle ASMで解決できるという記事も以前書いたことがあるので、ご興味があれば読んでみて下さい。（ ※blogs.oracle.comに移行しない場合はこのあたり削除 http://oracledatabase.jp/storage/entry_000151.html）
また、拡張した各データ・ファイルのサイズと、TBS_SMALL表領域の合計サイズを1つのSELECT文で問い合わせるサンプルを書いてみました。「rollup」拡張要素は非常に便利ですので、参考にしてみて下さい。
SQL> select tablespace_name, file_name, sum(bytes)/1024/1024 "size(MB)" from DBA_DATA_FILES
where tablespace_name = 'TBS_SMALL'
group by tablespace_name, rollup(file_name);
TABLESPACE_NAME FILE_NAME size(MB)
------------------------------ ------------------------------------------------ ----------
TBS_SMALL /oracle_datafile1/tbs_small01.dbf 125
TBS_SMALL /oracle_datafile1/tbs_small02.dbf 125
TBS_SMALL /oracle_datafile2/tbs_small03.dbf 125
TBS_SMALL /oracle_datafile2/tbs_small04.dbf 125
TBS_SMALL 500
■演習5．「TBS_BIG」という名前のビッグ・ファイル表領域を作成して下さい。ただし、この表領域のサイズは300MBとして下さい。
SQL> create bigfile tablespace TBS_BIG datafile '/oracle_datafile1/tbs_big01.dbf' size 100M,
'/oracle_datafile1/tbs_big02.dbf' size 100M,
'/oracle_datafile2/tbs_big03.dbf' size 100M;
行1でエラーが発生しました。:
ORA-32774: BIGFILE表領域TBS_BIGに複数のファイルが指定されています
上記の例では、ORA-32774のエラーが発生してしまっています。Oracle Databaseのマニュアル「エラー・メッセージ」でエラーの原因と解決方法を参照することが可能です。
ORA-32774: BIGFILE表領域stringに複数のファイルが指定されています
原因: 複数のデータファイルまたは一時ファイルが、BIGFILE表領域に対するCREATE TABLESPACEコマンドに
指定されました。
処置: 1つのファイルのみを含むようにコマンドを変更するか、かわりにSMALLFILE表領域を作成して下さい。
上記の「処置」部分からも推測できると思いますが、ビッグ・ファイル表領域は必ず1つのデータ・ファイルでのみ構成されます。よって、多くのデータ・ファイルの配置を検討する等の管理作業をする必要が無くなりそうだと、感じてもらえたかと思います。
しかし、複数のデータ・ファイルを含められるスモール・ファイル表領域の方が表領域の最大サイズは大きくなるのではないか？何がビッグ・ファイルだ？と少しでも気を使って考えて頂けていたら尚良いですね。
まずは、ビッグ・ファイル表領域に含まれるデータ・ファイルの最大サイズの公式は次の通りです。
ビッグ・ファイル表領域の1つのデータ・ファイルの最大サイズ ・[表領域のブロックサイズ] × 4194303 (= 2^32 -1) ' ブロックサイズが8KBの場合は、約32TB
ビッグ・ファイル表領域では、スモール・ファイル表領域のデータ・ファイルの最大サイズと比較して約1000倍も大きなデータ・ファイルを作成することが可能です。この理由は、ビッグ・ファイル表領域に含まれるデータ・ファイルを1つに限定することで、従来のスモール・ファイル表領域で複数のデータ・ファイルを識別する為に使用していた10bitをブロック番号へ流用することが可能（22bit + 10bit = 32 bit）になったからです。
しかし、次のように1つの表領域の最大サイズを比較した場合、どちらのタイプでも結局は同じサイズまで拡張できることに気付かされます。
1つの表領域の最大サイズ ・スモール・ファイル表領域の場合： [データ・ファイルの最大サイズ] × 1022個 ' ブロックサイズが8KBの場合は、約32TB（= 32GB × 1022） ・ビッグ・ファイル表領域の場合： [データ・ファイルの最大サイズ] × 1個 ' ブロックサイズが8KBの場合は、約32TB（= 32TB × 1）
ここで、新たなOracle Databaseの制限が登場します。それは、1つのOracle Database内で扱うことが可能なデータ・ファイルの最大数（通常は64000ファイル）です。この制限が存在する為、少ないデータ・ファイル数で同じサイズの表領域を構成可能なビッグ・ファイル表領域を使用することで、Oracle Databaseが扱えるデータ容量が大幅に増加することがご理解頂けたかと思います。
説明が長くなってしまいましたが、演習5の回答例は次のようになりますね。
SQL> create bigfile tablespace TBS_BIG datafile '/oracle_datafile1/tbs_big.dbf' size 300M;
■演習6．表領域TBS_BIGがビッグ・ファイル表領域となっていることをデータ・ディクショナリ・ビュー「DBA_TABLESPACES」で確認して下さい。
SQL> select tablespace_name,bigfile from dba_tablespaces
where tablespace_name in ('TBS_SMALL','TBS_BIG')
TABLESPACE_NAME BIGFILE
------------------------------ ----------------
TBS_BIG YES
TBS_SMALL NO
データ・ディクショナリ・ビュー「DBA_TABLESPACES」に問い合わせることで、データベース内の全ての表領域の情報を確認することが可能です。
ここで1点おまけ情報です。
演習1では「create tablespace」文を実行されたかと思いますが、確実にスモール・ファイル表領域を作成するには「create smallfile tablespace」となります。ここで「確実に」という表現を使用した理由について説明します。「smallfile」を省略した場合には、スモール・ファイル表領域が作成されると認識されている方もいると思いますが、正確にはデータベースのデフォルトの表領域タイプの設定に依存します。このデフォルトの表領域タイプを確認/設定するSQL文は以下の通りとなります。
SQL> -- Checking Default Tablespace Type
col property_value for a32
select PROPERTY_NAME, PROPERTY_VALUE
from DATABASE_PROPERTIES
where PROPERTY_NAME = 'DEFAULT_TBS_TYPE';
SQL> -- Setting Default Tablespace Type [BIGFILE]
alter database set default BIGFILE tablespace;
SQL> -- Setting Default Tablespace Type [SMALLFILE]
alter database set default SMALLFILE tablespace;
■演習7．表領域TBS_BIGのサイズを500MBへ拡張して下さい。拡張方法は問いません。
SQL> alter database datafile '/oracle_datafile1/tbs_big.dbf' resize 500M;
演習4のスモール・ファイル表領域の拡張と同じ問題なので、もちろん正解できましたよね？しかし、可能な限り気付きのある演習問題を出題していますので、少しだけ疑ってみて下さい。そうです。私が期待するパーフェクトな回答例は以下のSQLになります。
SQL> alter tablespace TBS_BIG resize 500M;
ビッグ・ファイル表領域ならではの管理の容易さを体感して頂きたいのです。スモール・ファイル表領域の拡張は「alter database」でデータ・ファイル名を指定した拡張を実施する必要がありましたが、ビッグ・ファイル表領域では「表領域のサイズを変更する」= 「alter tablespace」という直感的なオペレーションとなっています。データ・ファイル名を意識する必要がないということから、データ・ディクショナリ・ビュー「DBA_DATA_FILES」でデータ・ファイル名を確認する作業が不要であることも意味していますね。
以上で、表や索引を格納する表領域を作成することができました。create tablespace文では、ここでご紹介した以外にも沢山のオプションを指定することが可能です。しかしながら、ほとんどのケースにおいては未指定（デフォルト値）で問題ありませんし、詳細を理解する為にはセグメントやエクステントといった構造を把握する必要がある為、今回はここまでのご紹介とさせていただきます。
従来からのスモール・ファイル表領域とビッグ・ファイル表領域の違いについては、是非とも概要マニュアルをご参照下さい。概要マニュアルはOracle Databaseの機能が網羅的にまとめられているだけではなく、例えばデータベース・ブロック内の行断片等の詳細な情報も掲載されているので、これからDBAを目指される方には絶対に読んでおいて頂きたい、私の一番の大好きなマニュアルです。
次回は、「表と表領域の関係」についてになります。是非ご覧下さい。お疲れさまでした。