しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。 “しばちょう”こと柴田長（しばた つかさ）です。
今回はデータベースの基本的なオペレーションに立ち返り、表領域の縮小する際に重宝するSQLをご紹介したいと思います。
どちらかと言えば、表領域を現状よりも大きくする機会の方が圧倒的に多いはずなので、実際に小さくするというオペレーションを実行した経験は少ないのではないかと思います。とは言え、ミスオペで表領域を大きくし過ぎてしまった。一時的な作業の為に表セグメントのサイズが大きくなることで表領域の自動拡張機能により肥大化してしまった。開発環境や検証環境等でディスク容量の制限により、既に割り当ててしまった表領域を小さくする必要がある等、考えてみればいくらでも表領域の縮小を行う機会は思い浮かびます。そのような機会に問題無くオペレーションできるよう、是非とも今回の演習を体験しておいて頂きたいと思います。
表領域の縮小なんて、ALTER TABLESPACE RESIZE文（こちらはビッグ・ファイル表領域の場合に限定、従来のスモール・ファイル表領域の場合はALTER DATABASE DATAFILE RESIZE文）を実行するだけでしょ？はい、その通りですね。表領域を拡大する際のSQL文とまったく同じです。しかしながら、「表領域を縮小する」=「表領域内の空き領域を解放する」という事ですが、表領域内に空き領域があるはずなのに表領域を縮小できず、上記のRESIZE文を実行時にORA-03297が発生してしまうという良く陥りがちなケースがあります。このような場合に、スマートにどのセグメントが邪魔をしているのかを調査し対処できるカッコ良いDBAを目指し、今回の演習に挑戦してみてくださいね。
以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
【今回ご紹介するネタ一覧（逆引き）】
- ローカル管理、自動セグメント領域管理、エクステントの割り当て方式が1MB均一のビッグ・ファイル表領域の作成（演習1）
- 表領域の空き領域のサイズを確認（演習3）
- 索引作成で使用する領域の事前見積もり方法（演習4）
- 表領域縮小の邪魔をしているセグメントの確認方法（演習6）
- セグメントの移動方法（演習7）
1. 表セグメントを格納する550MBのビッグ・ファイル表領域「TBS17_FOR_TAB」を作成してください。ただし、ローカル管理、自動セグメント領域管理、エクステントの割り当て方式が1MB均一としてください。また、この表領域をデフォルト表領域とするユーザー「TRY」を作成してください。
sqlplus / as sysdba SQL> create bigfile tablespace TBS17_FOR_TAB datafile '+DATA(DATAFILE)' size 550m extent management local uniform size 1m segment space management auto ; 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 = 'TBS17_FOR_TAB' ; TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT BIGFILE --------------- ----------------- --------------- ------------------------ ------- TBS17_FOR_TAB LOCAL UNIFORM AUTO YES create user TRY identified by TRY default tablespace TBS17_FOR_TAB ; alter user TRY quota 550m on TBS17_FOR_TAB ; grant CONNECT, RESOURCE to TRY ;
毎度お馴染みになってきた本検証前の準備ですね。この演習問題を読んだだけである程度のSQL文や実行の流れがイメージ出来さえすれば、細かな部分はマニュアルを参照しながらでも良いと思います。もしくは、このようなSQL文を個人PC内に保存しておき、必要な場面でさっと見直せるような仕組みを持っているだけでも、圧倒的に作業スピードは変わってくるでしょうね。
表領域の作成については「第7回 表領域の管理方法を理解」で詳しく解説していますので、そちらを復習してみてください。一点だけ今回初めて記載させて頂いたのは、データ・ファイルの指定方法の部分で、ASM（Automatic Storage Management）ディスク・グループとファイル・テンプレートを使用しています。これらについては機会があれば連載でご紹介させて頂きますが、ご興味があれば、マニュアル「Automatic Storage Management管理者ガイド」の「ディスク・グループのテンプレート管理」をご参照ください。
ユーザー作成については「第2回 表と表領域の関係」でご紹介しています。初登場はRESOURCEロールです。とは言え、検証環境では良く使用されるロールですのでご存じの方が多いと思っています。このロールに組み込まれている権限はバージョン毎に異なる可能性がありますので、環境毎にDBA_SYS_PRIVSディクショナリ・ビューに問い合わせることで確認することができます。また、このロールを付与したユーザーは自動的にシステム権限「UNLIMITED TABLESPACE」が付与されるようです。よって、上記回答例内のalter user文による表領域の割り当て制限は意味が無いということですね。。。（この動作は、Oracle Database 11g Release 2にて確認しています。その他のバージョンによって異なる可能性があるのでご注意ください）
2. 次のSQL文を使用して、TRYスキーマ100MBの表を5つ作成してください。
sqlplus TRY/TRY SQL> create table TAB1 (col1 number NOT NULL, col2 date, col3 varchar2(100), col4 varchar2(100)) ; insert /*+append */ into TAB1 select LEVEL, SYSDATE+1/24/3600, rpad(to_char(LEVEL),100,'A'), rpad(to_char(LEVEL),100,'A') from DUAL connect by LEVEL <= 402000 ; commit ; create table TAB2 as select * from TAB1; create table TAB3 as select * from TAB1; create table TAB4 as select * from TAB1; create table TAB5 as select * from TAB1; create unique index PK_TAB1_COL1 on TAB1(COL1) ; create unique index PK_TAB2_COL1 on TAB2(COL1) ; create unique index PK_TAB3_COL1 on TAB3(COL1) ; create unique index PK_TAB4_COL1 on TAB4(COL1) ; create unique index PK_TAB5_COL1 on TAB5(COL1) ; alter table TAB1 add primary key(COL1) using index; alter table TAB2 add primary key(COL1) using index; alter table TAB3 add primary key(COL1) using index; alter table TAB4 add primary key(COL1) using index; alter table TAB5 add primary key(COL1) using index; exec dbms_stats.gather_schema_stats(ownname=>'TRY'); set linesize 150 pages 5000 col SEGMENT_NAME for a24 select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS order by 1; SEGMENT_NAME BYTES/1024/1024 ------------------------ --------------- PK_TAB1_COL1 7 PK_TAB2_COL1 7 PK_TAB3_COL1 7 PK_TAB4_COL1 7 PK_TAB5_COL1 7 TAB1 100 TAB2 100 TAB3 100 TAB4 100 TAB5 100
はい。特に問題無く実行できたと思います。
INSERT文で使用しているLEVEL疑似列については以前から連載内でご紹介してきていますが、ユニークかつ連番のレコードを作成する際には非常に便利なので覚えておきましょう。また、DBMS_STATSパッケージを使用してオプティマイザ統計情報を収集しています。引数としてはスキーマ名のみとしていますが、この状態でもOracle Database 11gでは実行時間が高速化していると言うポイントについては「第8回 オプティマイザ統計情報の管理 ～統計収集の高速化を体験してみる～」の演習6で解説していますので、是非参照してみてください。
3. TRYスキーマのTAB2表とTAB5表を削除した後、TBS17_FOR_TAB表領域の空き領域を確認してください。
sqlplus TRY/TRY
SQL> -- TAB2表とTAB5表を削除
drop table TAB2 ;
drop table TAB5 ;
purge recyclebin ;
SQL> -- TBS17_FOR_TAB表領域内に格納されている全セグメントの合計サイズを確認
select SUM(BYTES)/1024/1024
from USER_SEGMENTS
where TABLESPACE_NAME = 'TBS17_FOR_TAB' ;
SUM(BYTES)/1024/1024
--------------------
321
SQL> -- TBS17_FOR_TAB表領域内の空き領域を確認
select TABLESPACE_NAME, FILE_ID, BLOCK_ID, BYTES/1024/1024
from USER_FREE_SPACE
where TABLESPACE_NAME = 'TBS17_FOR_TAB' ;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES/1024/1024
------------------------------ ---------- ---------- ---------------
TBS17_FOR_TAB 7 12928 100
TBS17_FOR_TAB 7 51328 100
TBS17_FOR_TAB 7 65024 7
TBS17_FOR_TAB 7 67712 21
演習の都合上、2つの表を削除して頂きました。2つの表を削除するだけなら、TAB表4とTAB5表でも良いじゃないか？と疑問をお持ちの方もいらっしゃるかと思いますが、ここではTAB2表とTAB5表を削除して頂くことが以降の演習を盛り上げる為には必要不可欠なのです。経験豊富な方は、この時点で「にやり」とされているのではないでしょうか。
この時点でUSER_SEGMENTSディクショナリ・ビューに問い合わせることで、TBS17_FOR_TAB表領域内に格納されている全セグメントの合計サイズは321MBであることが解ります。また、USER_FREE_SPACEディクショナリ・ビューに問い合わせることで、TBS_17_FOR_TAB表領域には全部で228MB（100+100+7+21）の空き領域が残っていることも解ります。
このUSER_FREE_SPACEディクショナリ・ビューの出力結果を見ると4つの領域に分割されて空き領域が存在していることが実は解ります。少し出力したカラムが足りないのですが、一つ目の空き領域はBLOCK_ID=12928から100MB分（128ブロック=100MB/8KB）、二つ目の空き領域はBLOCK_ID=51328ですから、一つ目のBLOCK_ID=12928に128ブロック分を足した13056と大きく離れています。データ・ファイルの領域管理では先頭から順番にBLOCK_IDが振られているので、この2つの空き領域（三つ目、四つ目も同じく）は分段されていることが読み取れてしまうのです。
では、何故、このように空き領域が分段されているのでしょう？それは空き領域のサイズからも推測することができます。一つ目と二つ目の空き領域は100MBとなっていて奇妙ですよね？そうです。これはTAB2表とTAB5表が元々存在していた領域なのですね。空の状態のデータ・ファイルには、セグメントを作成した順番でブロックが先頭から割り当てられていきます。よって、今回の演習では、TAB1表、TAB2表、・・・TAB5表を順番に作成し、その後、各表の主キー（索引セグメント）を順番に作成しました。表を削除した場合には、その使用していた領域だけが解放され、残されたセグメントが先頭に移動するわけではありません。よって、このような空き領域の分段（断片化とも表現される）が発生しているのですね。
ちなみに、3つ目の空き領域は7MBですが、これは、TAB2表の主キー用の索引セグメントが削除された痕跡です。では、TAB5表の主キー用の索引セグメントが削除された痕跡はどこへ言ったのか？と言うと、この索引セグメントはデータ・ファイル内では一番後ろ（最後）に作成されたセグメントだったので、その後ろは元々空き領域として存在していました。この索引セグメントが削除されることで空き領域となりましたが、元々存在していた空き領域との間で残存するセグメントに割り当てられているブロックが存在していないので、この二つの空き領域が一つにまとめられたのです。
4. TAB1表のCOL3列にBツリー索引を作成した際の索引セグメントのサイズを見積もってください。
sqlplus TRY/TRY
SQL>
var used_bytes number ;
var alloc_bytes number ;
exec DBMS_SPACE.CREATE_INDEX_COST( -
ddl => 'create index IDX_TAB1_COL3 on TAB1(COL3)', -
used_bytes => :used_bytes, -
alloc_bytes => :alloc_bytes);
select :used_bytes/1024/1024, :alloc_bytes/1024/1024 from dual;
:USED_BYTES/1024/1024 :ALLOC_BYTES/1024/1024
--------------------- ----------------------
38.7210846 50
お客様から「○○を机上で見積もってください！」と依頼を受けるケースは多いですよね。そのような場合はこれまでの経験値をフル活用したり、本番データに似ているレコード（正確には本番レコードではなく、自作物）を格納した検証環境で試してみたり、曖昧なシステム負荷の予測値を難しい数式に当てはめて計算して安全率を掛けて・・・のように日々苦しんでいるDBAの皆さんに朗報です。索引セグメントのサイズは実際に索引を作成しなくても、オプティマイザ統計情報さえ正しく取得されている環境であれば見積もることが可能です！って少し強気で宣言してみましたが、あっ、知っていましたかね？もし知っているのであれば、私の連載の記事のレベルでは満足されていないかもしれませんね。。。まあ、「DBAへの道」って事で許して下さいね。
はい、これから作成したい索引セグメントのサイズを見積もる為には、DBMS_SPACEパッケージのCREATE_INDEX_COSTプロシージャを使用します。前提条件としては、正確なオプティマイザ統計情報を収集済みであることです。今回は演習1において取得していますので、おそらく正確な値がはじき出されるはずです。ということで、実際に索引を作成する際のDDL文を第一引数「dd;l」に指定して実行してみると、二つの値が戻ってきます。一つ目のUSED_BYTESは実際の索引データを示すバイト数、二つ目のALLOC_BYTESは表領域に作成されたときの索引のサイズ（単位：バイト）なので、二つ目の値に注目すれば良いことが解ります。よって、TAB1表のCOL3列にBツリー索引を作成した場合、約50MBのサイズになることが見積もれたと言う事になります。
ちなみに、プロシージャ内の引数と引数の間で改行を入れたい場合は、「-」を付けるのをお忘れなく。また、VARIABLE（VARに省略可能）で、バインド変数を宣言しておくと、このようなプロシージャの戻り値の受け取りと参照が非常に楽になりますので、こちらも参考にしてみてください。
と言う事で、TAB1表とTAB3表、TAB4表の3つの表のCOL3列にBツリー索引を作成しようとすると、50MB×3=150MBが必要になることが予測できたので、今回はこれらの索引を格納する200MBの表領域を作成してみましょう。ただし、これ以上のディスクの空き領域が無いという仮定の下で、以降の演習にチャレンジしてみてください。
5. ディスクの空き領域が無いという仮定の下、索引セグメントを格納する200MBの表領域「TBS17_FOR_IDX」を作成する為にTBS17_FOR_TAB表領域を350MBに縮小してください。
sqlplus / as sysdba SQL> alter tablespace TBS17_FOR_TAB resize 350m ; ORA-03297: ファイルには、リクエストしたRESIZE値を超える使用中のデータが含まれています。
ディスクの空き領域が無いと言う事は、新しい表領域を作成することはできません。よって、既存の表領域のサイズを縮小することで、新しい表領域を作成する領域を確保する必要が出てきます。と言う事で、今回の演習では550MBのTBS17_FOR_TAB表領域を350MBに縮小することで、索引用の新たな200MBの表領域を作成する領域を確保してみましょう。
表領域の縮小なんて、ALTER TABLESPACE RESIZE文（こちらはビッグ・ファイル表領域の場合に限定、従来のスモール・ファイル表領域の場合はALTER DATABASE DATAFILE RESIZE文）を実行するだけで簡単でしょ？って思って、見事にORA-03297を引いてしまった方、ありがとうございます。
演習3でも確認している通り、TBS17_FOR_TAB表領域内の空き領域の合計としては228MB存在しているので、既存の550MBから200MB小さい350MBに縮小するのは問題なさそうな気がしますが、ORA-03297で使用中のデータが含まれると怒られてしまいました。
と言うのは、既に演習3の解説でも詳しく述べさせていただきましたが、空き領域が分割されていることが原因なのです。RESIZE文は、そのデータ・ファイル内に格納されているセグメントは移動せず、一番後ろに格納されているセグメントよりも後ろの空き領域しか解放することができないのです。つまり、演習3の解答例の最後で確認している四つ目の空き領域である21MBしか解放できないと言う事になります。350MBへ縮小したいのであれば、既存のセグメントを先頭から350MB以内の領域へ移動させる必要があると言う事になります。
では、具体的にどのセグメントが350MB以降に格納されていてRESIZEの邪魔をしているのかを確認してみましょう。
6. 次のSQL文を使用して、TBS17_FOR_TAB表領域の350MB以降に格納されているセグメントを特定してください。
sqlplus / as sysdba
SQL>
set linesize 150 pages 5000
col SEGMENT_NAME for a24
select distinct T1.TABLESPACE_NAME, T1.FILE_ID,
T1.SEGMENT_TYPE, T1.OWNER||'.'||T1.SEGMENT_NAME "SEGMENT_NAME", T1.SEGMENT_TYPE
from DBA_EXTENTS T1
where ((T1.BLOCK_ID + 1)
* (select BLOCK_SIZE
from DBA_TABLESPACES T2
where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
and TABLESPACE_NAME = 'TBS17_FOR_TAB'
order by 1, 2;
sizeに値を入力してください: 350
TABLESPACE_NAME FILE_ID SEGMENT_TYPE SEGMENT_NAME SEGMENT_TYPE
--------------- ---------- ------------------ ------------------------ ------------------
TBS17_FOR_TAB 7 INDEX TRY.PK_TAB1_COL1 INDEX
TBS17_FOR_TAB 7 INDEX TRY.PK_TAB3_COL1 INDEX
TBS17_FOR_TAB 7 INDEX TRY.PK_TAB4_COL1 INDEX
TBS17_FOR_TAB 7 TABLE TRY.TAB4 TABLE
少し複雑なSQL文ですが、絶対に役に立つので是非とも保存しておいてください。
この結果から、TAB4表、PK_TAB1_COL1索引、PK_TAB3_COL1索引、PK_TAB4_COL1索引が350MB以降に格納されてしまっていることが確認できました。よって、これらのセグメントをデータ・ファイルの先頭に近い空き領域に移動させることができれば、良さそうな気がしてきましたね。
7. 演習6で特定したセグメントを移動することで、TBS17_FOR_TAB表領域の断片化を解消（空き領域を全てのセグメントの後ろへ）して下さい。
sqlplus TRY/TRY
SQL>
connect TRY/TRY
alter table TAB4 move ;
alter index PK_TAB4_COL1 rebuild online ;
alter index PK_TAB1_COL1 rebuild online ;
alter index PK_TAB3_COL1 rebuild online ;
set linesize 150 pages 5000
col SEGMENT_NAME for a24
select distinct T1.TABLESPACE_NAME, T1.FILE_ID,
T1.SEGMENT_TYPE, T1.OWNER||'.'||T1.SEGMENT_NAME "SEGMENT_NAME", T1.SEGMENT_TYPE
from DBA_EXTENTS T1
where ((T1.BLOCK_ID + 1)
* (select BLOCK_SIZE
from DBA_TABLESPACES T2
where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
and TABLESPACE_NAME = 'TBS17_FOR_TAB'
order by 1, 2;
レコードが選択されませんでした。
表セグメントを移動させる場合にはいくつか方法がありますが、今回の演習では、ALTER TABLE MOVE文を使用してみましょう。このメンテナンス中にもアプリケーションからの更新処理を受け付けなければならない場合は、表のオンライン再定義を使用することを検討してみてください。索引を移動させるためには、再作成が必要となります。ALTER INDEX REBUILD ONLINE文を使用することでメンテナンス中にもアプリケーションからの更新処理を受け付けることが可能となります。
いかがでしょうか？各セグメントの移動を実施した後、再度、350MB以降に存在しているセグメントを確認してみましたが、何も出力されなくなっていますね。
8. 改めて、TBS17_FOR_TAB表領域を350MBへ縮小して索引セグメントを格納する200MBの表領域「TBS17_FOR_IDX」を作成してください。
sqlplus / as sysdba SQL> alter tablespace TBS17_FOR_TAB resize 350m ; create bigfile tablespace TBS17_FOR_IDX datafile '+DATA(DATAFILE)' size 200m extent management local uniform size 1m segment space management auto ; alter user TRY quota 200m on TBS17_FOR_IDX ; connect TRY/TRY create index IDX_TAB1_COL3 on TAB1(COL3) tablespace TBS17_FOR_IDX ; create index IDX_TAB3_COL3 on TAB3(COL3) tablespace TBS17_FOR_IDX ; create index IDX_TAB4_COL3 on TAB4(COL3) tablespace TBS17_FOR_IDX ; set linesize 150 pages 5000 col SEGMENT_NAME for a24 select TABLESPACE_NAME, SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where TABLESPACE_NAME = 'TBS_FOR_IDX' order by 1,2; TABLESPACE_NAME SEGMENT_NAME BYTES/1024/1024 ------------------------------ ------------------------ --------------- TBS17_FOR_IDX IDX_TAB1_COL3 50 TBS17_FOR_IDX IDX_TAB3_COL3 50 TBS17_FOR_IDX IDX_TAB4_COL3 50
と言う事で、無事に既存のTBS17_FOR_TAB表領域を350MBまで縮小させることができ、新たな200MBの表領域を作成するディスクの空き領域を確保することができましたね。
ちなみに、演習4においてDBMS_SPACE.CREATE_INDEX_COSTプロシージャで見積もった索引セグメントのサイズは見事に50MBで一致していることも確認できていますので、どれだけ精度が高いのかをご納得して頂けたかと思います。もちろん、オプティマイザ統計情報の精度が高いというのが前提となりますので、DBMS_SPACE.CREATE_INDEX_COSTプロシージャを使用する際には、この点に十分ご注意くださいね。
さて、いかがでしたでしょうか？データベース管理の基本操作である表領域の縮小のシナリオでしたが、意外にも気を付けるポイントがあることや、このようなメンテナンス作業を通してデータベースの仕組みの理解が進んでいくという体験をして頂けたかと思います。やはり、実際にSQL文を実行することで得られる知識の納得感は本で読むだけよりも圧倒的に高く、自分の中でスムーズに消化していく感じがしますよね？と言う事で、是非ともいつでも操作できるデータベース環境を手元に用意してあると素敵ですよね。
今回もありがとうございました。次回も頑張りますので、どうぞよろしくお願いします。