しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。“しばちょう”こと柴田長（しばた つかさ）です。
さて、第7回目の今回は、第1回と第2回でも作成して頂いた表領域の設定をもう少し深く理解したいというリクエストに答える形で、改めて表領域を扱いたいと思います。SQL言語リファレンスでCREATE TABLESPACE文を読んでみると「自動 or 手動」、「管理」という単語が入り乱れている為、Oracleデータベースを理解する初期の頃に苦しむ部分ではないかと想像します。私自身も理解不十分なままでOracle MasterのDBA試験を受験していた当時のことを思い出します。正確な理解を定着させるためには実機で動作確認をすることが近道ですから、是非とも今回の演習にチャレンジして頂きたいと思います。
以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
■ 1. セグメント内の空き領域の管理方法として自動セグメント領域管理が指定された128MBのビッグ・ファイル表領域TBS_BIG_ASSMを作成して下さい。
sqlplus / as sysdba SQL> create bigfile tablespace TBS_BIG_ASSM datafile '/oracle_datafile1/tbs_big_assm.dbf' size 128M segment space management auto;
ビッグ・ファイル表領域については、第1回で演習しているので復習となりますね。その際は、データ・ファイルのサイズの指定までしか記述していませんでしたが、今回はさらに1行が追加されていることに気付かれたかと思います。演習問題には「セグメント内の空き領域の管理方法として自動セグメント領域管理」という指定があるので、マニュアル「SQL言語リファレンス」の「CREATE TABLESPACE」ページに移動して「自動セグメント領域管理」を検索してみましょう。そこで、下記のような記述を見つけることができます。
segment_management_clause
segment_management_clauseは、永続的なローカル管理表領域に対してのみ有効です。Oracle Databaseが、空きリストまたはビットマップのどちらを使用して、表領域のセグメントにある使用済領域および空き領域を追跡するかを指定できます。この句は、一時表領域では無効です。
AUTO
AUTOを指定すると、ビットマップを使用して表領域のセグメントにある空き領域を管理できます。AUTOを指定すると、この表領域のオブジェクトに対して後で指定する記憶域のPCTUSED、FREELISTおよびFREELIST GROUPSの値は無視されます。この設定を自動セグメント領域管理といい、これがデフォルトです。
MANUAL
MANUALを指定すると、空きリストを使用して表領域のセグメントにある空き領域を管理できます。この設定は使用しないようにして、自動セグメント領域の表領域を作成することを強くお薦めします。
つまり、segment_management_clauseで「AUTO」を指定することで目的の「自動セグメント領域管理」が設定されるということが理解できます。そして次にsegment_management_clauseの書き方を参照してみると、「segment space management auto」と記述する必要があることが解ります。さらに、この句がCREATE TABLESPACE文のどこに配置されるのかを探すことで、最終的に上記のSQL文が完成して実行することができます。
以上で演習1は完了です。と行きたいところですが、本当に目的通りの表領域が作成されているのか気になりますよね。次の演習2では、その確認をしてみましょう。
■ 2. 表領域TBS_BIG_ASSMのエクステント管理方法、割り当てられるエクステントのサイズの方式、セグメント内の空き領域の管理方法を確認してください。
sqlplus / as sysdba SQL> set linesize 100 pagesize 100 col TABLESPACE_NAME for a15 col EXTENT_MANAGEMENT for a17 col ALLOCATION_TYPE for a15 col SEGMENT_SPACE_MANAGEMENT for a24 col BIGFILE for a7 select TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT, BIGFILE from DBA_TABLESPACES where TABLESPACE_NAME = 'TBS_BIG_ASSM' ; TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT BIGFILE --------------- ----------------- --------------- ------------------------ ------- TBS_BIG_ASSM LOCAL SYSTEM AUTO YES
過去の演習でも繰り返しお伝えしていますが、このような確認作業は自分のオペレーションにミスの発見だけに留まらず、作業依頼者に対して安心感を与える材料ともなり信頼を得ることにも繋がりますので、必ず実行するようにしましょう。
DBA_TABLESPACESディクショナリ・ビューに問い合わせると、SEGMENT_SPACE_MANAGEMENT列の値が「AUTO」と表示されたので、TBS_BIG_ASSM表領域は「自動セグメント領域管理」に設定されているのだろうと予想はできますね。ただし、各列が何を示しているのかを今回は正確に確認しておこうと思います。
TABLESPACE_NAME列とBIGFILE列は問題ないとおもいますが、その他のEXTENT_MANAGEMENT列、ALLOCATION_TYPE列、SEGMENT_SPACE_MANAGEMENT列は何を示しているのでしょうか。このような場合は、どのマニュアルを参照すれば良いかが一瞬で思いついた方は非常に優秀です。リファレンス・マニュアルには各ディクショナリ・ビューの各カラムの説明書きが掲載されていますから、それを参照すれば、上記の列が何を示しているのかを読み取ることができます。しかし、今回はDICT_COLUMNSディクショナリ・ビューに対するSELECT文でカッコよく検索してみましょう。
sqlplus / as sysdba
SQL> set linesize 150 pagesize 100
col TABLE_NAME for a15
col COLUMN_NAME for a24
col COMMENTS for a55
select TABLE_NAME, COLUMN_NAME,COMMENTS from DICT_COLUMNS
where TABLE_NAME = 'DBA_TABLESPACES'
and COLUMN_NAME in ('EXTENT_MANAGEMENT', 'ALLOCATION_TYPE', 'SEGMENT_SPACE_MANAGEMENT');
TABLE_NAME COLUMN_NAME COMMENTS
--------------- ------------------------ -------------------------------------------------------
DBA_TABLESPACES EXTENT_MANAGEMENT Extent management tracking: "DICTIONARY" or "LOCAL"
DBA_TABLESPACES ALLOCATION_TYPE Type of extent allocation in effect for this tablespace
DBA_TABLESPACES SEGMENT_SPACE_MANAGEMENT Segment space management tracking: "AUTO" or "MANUAL"
この結果から、EXTENT_MANAGEMENT列はエクステント管理方法が「DICTIONARY」か「LOCAL」を示し、ALLOCATION_TYPE列はエクステント割当ての方式（タイプ）、SEGMENT_SPACE_MANAGEMENT列はセグメント領域管理方法が「AUTO」か「MANUAL」で示すことが理解できます。
とは言え、非常に紛らわしい単語が沢山出てきた印象だと思いますので、ローカル管理表領域に限定して簡単に解説しておきます。詳しくは、概要マニュアルの「12 論理記憶域構造」を参照ください。
エクステント管理方法（EXTENT_MANAGEMENT）：
エクステントの管理方法はローカル管理「LOCAL」とディクショナリ管理「DICTIONARY」の2種類があります。
デフォルト設定はローカル管理であり、ディクショナリ管理と比較して多くのメリット（性能、設計/管理の容易さ、断片化抑制等）があります。明示的に設定する場合には、「EXTENT MANAGEMENT LOCAL」を指定します。
エクステント割当ての方式（ALLOCATION_TYPE）：
エクステントが追加される際のルールであり、自動割当てエクステント「AUTOALLOCATE」と均一エクステント「UNIFORM」の2種類があります。
デフォルト設定は「AUTOALLOCATE」であり、エクステント追加対象のセグメントのサイズに応じてOracle Databaseが自動的に決定する最適サイズを決定します。この場合、DBA_TABLESPACESディクショナリ・ビューのALLOCATION_TYPE列には「SYSTEM」と表示される点に注意が必要です。一方、「UNIFORM」設定では、全てのエクステントを均一に割り当てられます。UNIFORM句の後ろにサイズ指定可能ですが、指定しない場合はデフォルトの1MBが適用されます。
セグメント領域管理方法（SEGMENT_SPACE_MANAGEMENT）：
表領域内に作成されているセグメントのデータ・ブロックの空き状態を管理する方法として、自動セグメント領域管理「AUTO」と手動セグメント管理「MANUAL」の2種類があります。デフォルト設定は「AUTO」です。演習1の解説でも触れたので、ここでの説明は割愛させて頂きます。
つまり、今回皆さんと作成したTBS_BIG_ASSM表領域を正確に表現すると、「ローカル管理で、エクステントの割当て方式が「AUTOALLOCATE（SYSTEM）」、その表領域内のセグメントの空き領域の管理方法が自動セグメント領域管理のビッグ・ファイル表領域」と言うことになります。そして、色々細かく解説しましたが、各デフォルト設定からも理解できるようにCREATE TABLESPACE文において表領域名とデータ・ファイルを指定するだけでも、演習1で作成して頂いた表領域が作成されます。基本的には、最終的にはエクステント割当ての方式（ALLOCATION_TYPE：SYSTEM or UNIFORM）だけを検討・指定すれば十分なケースがほとんどです。
以降の演習では、このエクステント割当て方式の違いについて、実際の動作を確認していきましょう。
■ 3. TRYユーザーで表領域TBS_BIG_ASSM上に、表TBL6のセグメントを作成して下さい。ただし、第一カラムの名前が「col1」でデータ型が「char(1000)」の一つのカラムで構成される表とします。
sqlplus TRY/TRY SQL> create table TBL6 (col1 char(1000)) segment creation immediate tablespace TBS_BIG_ASSM ; ORA-01536: 表領域TBS_BIG_ASSMに対する領域割当て制限を使い果たしました。 SQL> connect / as sysdba alter user TRY quota UNLIMITED on TBS_BIG_ASSM ; SQL> connect TRY/TRY create table TBL6 (col1 char(1000)) segment creation immediate tablespace TBS_BIG_ASSM ; 表が作成されました。
はい、表を作成するだけの演習ですが、これまでの復習として二点注意が必要ですね。一点目は「セグメント作成の遅延」機能であり、上記のCREATE TABLE文では「segment creation immediate」句を指定することで、表を作成するタイミングでセグメントの割り当てを行わせています。すると、ORAエラーが発生してTBS_BIG_ASSM表領域に対するQuota設定が行われていないことに気付くので、Quota設定を実施してから再度、表の作成を行い成功させている例になります。「segment creation immediate」句を指定しなければセグメントが割り当てられない為、次の演習4でデータをINSERTしたタイミングでORAエラーが発生して初めて気付くことを度々やってしまうことがありますね。
ちなみに、Quota設定の確認はDBA_TS_QUOTAS/USER_TS_QUOTASディクショナリ・ビューで確認できます。
■ 4. 表TBL6に対し、次のSQL文でレコードをINSERTしてください。
insert into TBL6 select to_char(LEVEL) from DUAL connect by LEVEL <= 89600 ;
sqlplus TRY/TRY
SQL> insert into TBL6 select to_char(LEVEL) from DUAL connect by LEVEL <= 89600 ;
89600行が作成されました。
SQL> commit ;
SQL> select min(to_number(COL1)), max(to_number(COL1)) from TBL6;
MIN(TO_NUMBER(COL1)) MAX(TO_NUMBER(COL1))
-------------------- --------------------
1 89600
指定されたINSERT+SELECT文をそのまま実行するだけなので特に問題ないと思います。が、「このSQL文は何をしているのだ？」と感じて頂けたら嬉しいです。これまでも大量の連番データを生成するSQLをいくつか演習の中でもご紹介してきましたが、今回のSQL文は驚くぐらい美しい構文ですよね。既にご存知だった方もいらっしゃると思いますが、LEVEL疑似列を使用して連番データを生成する方法なので、是非参考にしてみてください。ただし、上記の実行結果はOracle Database 11g Release 11.2.0.2であり、Oracle 9i Database環境では少し違った結果になりましたので使用される際は十分ご注意くださいね。
■ 5. 表TBL6を構成する各エクステントのブロック数とバイト数を確認して下さい。
sqlplus TRY/TRY SQL> set pagesize 1000 linesize 120 col SEGMENT_NAME for a24 select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS from USER_EXTENTS where SEGMENT_NAME = 'TBL6' ; SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BYTES BLOCKS ------------------------ ------------------------------ ---------- ---------- ---------- TBL6 TBS_BIG_ASSM 0 65536 8 TBL6 TBS_BIG_ASSM 1 65536 8 TBL6 TBS_BIG_ASSM 2 65536 8 .... ............ .. ..... . TBL6 TBS_BIG_ASSM 15 65536 8 TBL6 TBS_BIG_ASSM 16 1048576 128 TBL6 TBS_BIG_ASSM 17 1048576 128 .... ............ .. ....... ... TBL6 TBS_BIG_ASSM 78 1048576 128 TBL6 TBS_BIG_ASSM 79 8388608 1024 TBL6 TBS_BIG_ASSM 80 8388608 1024 .... ............ .. ....... .... TBL6 TBS_BIG_ASSM 83 8388608 1024 ※ 表領域TBS_BIG_ASSMのブロック・サイズは8KB
表セグメントを構成するエクステントは、空き領域が不足することで追加されていきます。つまり、演習4で実行したINSERTにより、必要とされるエクステントが割り当てられていることが予想出来ます。また、TBL6表はローカル管理表領域で自動割当てエクステント「AUTOALLOCATE」が設定されている表領域TBS_BIG_ASSM上に作成されている為、割り当てられたエクステントのサイズを確認することで、「セグメントのサイズに応じてOracle Databaseが自動的に決定する最適サイズ」なるものを実際に見てみましょう。
ということで、USER_EXTENTSデータ・ディクショナリ・ビューに問い合わせることで、表TBL6を構成する全エクステントのサイズを確認した結果が上記の回答例になります。EXTENT_IDが0から83までの全84個のエクステントで構成されており、EXTENT_IDが大きくなるにつれて、エクステントのサイズ（BYTES列、BLOCKS列）が大きくなっていることが理解できると思います。前提としてEXTENT_IDは割り当てられた順番だと読み取って頂いて問題有りません。ここでは、もう少し詳しく分析してみましょう。
EXTENT_IDが0から15までの16個のエクステントのサイズは64KB(= 65536 / 1024)なので、EXTENT_IDが15のエクステントまで割り当てられた際の表TBL6のセグメント・サイズは1MB(= 1024KB = 64KB * 16)です。また、EXTENT_IDが16から78までの63個のエクステントのサイズは1MB(= 1048576 / 1024 / 1024)なので、EXTENT_IDが78のエクステントまで割り当てられた際の表TBL6のセグメント・サイズは64MB(= 1MB + 1MB * 63)です。さらに、EXTENT_IDが79以降のエクステントのサイズは8MBになっていることが理解できます。
つまり、自動割当てエクステント「AUTOALLOCATE」を設定した場合、次のような動作となります。
- 記号セグメント・サイズが1MBまでのエクステント・サイズは64KB
- セグメント・サイズが64MBまでのエクステント・サイズは1MB
- セグメント・サイズがそれ以上の場合のエクステント・サイズは8MB、さらに64MB
今回の演習では64MB単位で割り当てられる部分は確認していませんが、お時間があれば、より大きなデータ・ファイルを用意して試して頂ければと思います。
ある一つの表領域上に大小様々な複数の表セグメントや索引セグメントを配置する構成の場合では、各セグメント・サイズに応じて適切なエクステント・サイズが割り当てられる自動割当てエクステント「AUTOALLOCATE」を選択し、未使用領域の厳密な制御が必要でオブジェクトに割り当てられる領域、エクステントの数とサイズを正確に予測できる場合は、UNIFORMを選択するということがデータベース管理者ガイドにも記載されています。付け足すとしたら、100GBを超えるような巨大な表セグメントを配置する表領域ではUNIFORMを選択することが多いと思います。
■ 6. 表領域TBS_BIG_ASSMのエクステントの割り当てサイズを4MBに均一化する設定をして下さい。sqlplus / as sysdba SQL> drop tablespace TBS_BIG_ASSM ; ORA-01549: 表領域が空ではありません。INCLUDING CONTENTSオプションを使用してください SQL> select SEGMENT_NAME, TABLESPACE_NAME from DBA_SEGMENTS where tablespace_name = 'TBS_BIG_ASSM' ; SEGMENT_NAME TABLESPACE_NAME ---------------- --------------- TBL6 TBS_BIG_ASSM SQL> drop table TRY.TBL6 ; SQL> drop tablespace TBS_BIG_ASSM INCLUDING CONTENTS AND DATAFILES ; SQL> create bigfile tablespace TBS_BIG_ASSM datafile '/oracle_datafile1/tbs_big_assm.dbf' size 128M extent management local uniform size 4m segment space management auto; SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT, BIGFILE from DBA_TABLESPACES where TABLESPACE_NAME = 'TBS_BIG_ASSM' ; TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT BIGFILE --------------- ----------------- --------------- ------------------------ ------- TBS_BIG_ASSM LOCAL UNIFORM AUTO YES
少し引っかけ問題ですね。エクステント割当て方式は表領域の作成時にしか指定することができない為、同じ名前の表領域で自動エクステント割当て「AUTOALLOCATE」から均一エクステント「UNIFORM」に変更したい場合には、表領域の再作成が必要となります。今回は検証環境なので、表領域の削除+作成を簡単に行うことができますが、本番環境ではその表領域上に存在しているセグメントのデータをDataPumpで￥退避しなければならない点に注意してください。
■ 7. 演習4と演習5を再度実行してみてください。
sqlplus TRY/TRY SQL> create table TBL6 (col1 char(1000)) segment creation immediate tablespace TBS_BIG_ASSM ; SQL> insert into TBL6 select to_char(LEVEL) from DUAL connect by LEVEL <= 89600 ; 89600行が作成されました。 SQL> commit ; SQL> set pagesize 1000 linesize 120 col SEGMENT_NAME for a24 select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS from USER_EXTENTS where SEGMENT_NAME = 'TBL6' ; SQL> SQL> 2 SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BYTES BLOCKS ------------------------ ------------------------------ ---------- ---------- ---------- TBL6 TBS_BIG_ASSM 0 4194304 512 TBL6 TBS_BIG_ASSM 1 4194304 512 TBL6 TBS_BIG_ASSM 2 4194304 512 TBL6 TBS_BIG_ASSM 3 4194304 512 TBL6 TBS_BIG_ASSM 4 4194304 512 TBL6 TBS_BIG_ASSM 5 4194304 512 TBL6 TBS_BIG_ASSM 6 4194304 512 TBL6 TBS_BIG_ASSM 7 4194304 512 TBL6 TBS_BIG_ASSM 8 4194304 512 TBL6 TBS_BIG_ASSM 9 4194304 512 TBL6 TBS_BIG_ASSM 10 4194304 512 TBL6 TBS_BIG_ASSM 11 4194304 512 TBL6 TBS_BIG_ASSM 12 4194304 512 TBL6 TBS_BIG_ASSM 13 4194304 512 TBL6 TBS_BIG_ASSM 14 4194304 512 TBL6 TBS_BIG_ASSM 15 4194304 512 TBL6 TBS_BIG_ASSM 16 4194304 512 TBL6 TBS_BIG_ASSM 17 4194304 512 TBL6 TBS_BIG_ASSM 18 4194304 512 TBL6 TBS_BIG_ASSM 19 4194304 512 TBL6 TBS_BIG_ASSM 20 4194304 512 TBL6 TBS_BIG_ASSM 21 4194304 512 TBL6 TBS_BIG_ASSM 22 4194304 512 TBL6 TBS_BIG_ASSM 23 4194304 512 TBL6 TBS_BIG_ASSM 24 4194304 512 TBL6 TBS_BIG_ASSM 25 4194304 512
はい、如何でしょうか。均一エクステント「UNIFORM」という名前の通り、表TBL6のセグメントを構成する全てのエクステントのサイズが4MB(= 4194304 / 1024 /1024)になっていることが確認できたかと思います。
最後にコネタを紹介して終わりにしたいと思います。「あれ？表領域を再作成した後、Quotaの設定をしていないにも関わらずデータをINSERTできてしまった」と思いませんでしたか？実は、表領域を削除してもQuota設定のエントリが残るという仕様があります。とは言え、きちんと削除されたか否かはフラグ管理されており、USER_TS_QUOTASディクショナリ・ビューのDROPPED列に記録されています。ただ、同じ名前の表領域を作成した場合には、残っているエントリが有効になる為、表領域TBS_BIG_ASSMを再作成後に以前設定したQuota設定が生き返るということになります。ちなみに、以下が、私の環境のUSER_TS_QUOTASディクショナリ・ビューに問い合わせた結果であり、表領域LOC_MSSMは削除されている為、DROPPED列では「YES」と出力されています。エントリを削除したいという場合には、KROWNにてその方法を公開しておりますので、そちらを参考にしてみてください。
sqlplus / as sysdba SQL> select * from USER_TS_QUOTAS; TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED ------------------------------ ---------- ---------- ---------- ---------- ------- TBS_BIG 152174592 419430400 18576 51200 NO TBS_BIG_ASSM 109051904 -1 13312 -1 NO LOC_MSSM 0 -1 0 -1 YES
改めて表領域の管理方法を解説させて頂きましたが、様々な設定の種類を整理できたでしょうか？各種データ・ディクショナリ・ビューを使用して、施した設定がどのような動作するのかを実機で確認することが理解を深める一番の方法だと思います。正直、マニュアルを読んだだけでは正確に理解できないことも少なくはありません。そのような場面に直面した際に、直ぐに手を動かして確認したか否かが将来的に大きな技術力の差になってくると私は信じています。そう言う私自身も、未だに日々新たな発見をしている毎日であり、検証ログを蓄積しているフォルダに最低でも1つは追加され続けています。実機で試すという習慣の大切さをこの連載で気付いて頂けたら本当にうれしく思います。
また、私の経験では自分のスキルレベルを向上させるには、目標にしたいと感じさせられる憧れの人物との出会いも非常に大切だと思っています。レベ ルを図る物差しにもなりますし、自分には無い新たな気付きを沢山得られることで成長のスピードも各段にアップします。そのような出会いのチャンス を最後にご紹介させて下さい。
Japan Oracle User Group(JPOUG)のイベントが2012年7月21日にオラクル青山センターにて開催されます。
JPOUG> SET EVENTS 20120721 | Japan Oracle User Group
スピーカーには、Oracle ACEを始め、有名人やエキスパートの方々がエントリされており、興味深い話を聞いたり直接質問できたりと非常に良いチャンスです。ユーザー会への参加が 初めての方でもお気軽にご参加ください。私も会場におりますので、見かけた際は是非お声掛け頂ければ嬉しいです。そして、この連載記事の似顔絵と 実物を比較して下さいね。
今回もありがとうございました。次回以降も頑張っていきますので、よろしくお願いします。