月別アーカイブ: 2016年3月

ブロンプトンで春の三浦半島を行く

たまには「サイクル」も

ブログタイトルを「サイクル＆オラクル」にしてからオラクル関連の記事ばかりを書いているので久々に自転車関連の記事を書いてみる。

ちょうど１ヶ月前にブロンプトンという英国生まれのフォールディング・バイク（折りたたみ自転車を買って以来、その面白さにすっかりはまっているのだが、今日は昨年９月５日に初めて１００Km超えをした三浦半島をブロンプトンでゆっくり回ってみようと思い出発した。

三崎口駅までは輪行で

新逗子駅にて

 

ブロンプトンのいいところは輪行がとても楽だということだ。走行状態からゆっくりやっても３分ぐらいで写真のような状態にすることができる。展開も同じくらいの時間しかかからないので電車に乗せるというハードルがものすごく低い。

今日は、自宅からJR藤沢駅までの３km強を走った後、藤沢駅から東海道線に乗った。隣の大船駅で乗り換え横須賀線で逗子へ。JR逗子駅から京急新逗子駅は歩いてもそれほど時間はかからない距離だが１１kgのブロンプトンを担いで歩くのはやや辛いところがあるので、展開して４分自転車をこぎ新逗子駅で再び輪行態勢に。

新逗子から電車に乗り、金沢八景で乗り換え三崎口まで行く。横須賀線の鎌倉駅でどっと降りてからずっと車内はすいていたので大きな荷物を抱えながらも座って行くことができた。

乗り換えが３回で、そのうち１回は展開収納を行ったので、かなり面倒なはずなのだがブロンプトンではあまり苦にならない。

自宅を出てから三崎口まで１時間４５分くらいだったが、昨年の９月はロードバイクで３時間位かかったことを思い出す。

三崎口駅

コースはガイドブックを参考に

コースは「新版 神奈川・伊豆・箱根・富士 自転車散歩」に載っていた三崎口駅をスタート／ゴールとする約３０kmのコースで、前日に「ルートラボ」でコースを作ってiPhoneの「Runtastic Road Bike PRO GPS サイクルコンピュータ」で使えるようにしておいた。

ロードバイクのようにスピード重視の自転車ではないので、急坂があれば押して登るし、今日はできるだけ景色を楽しみながらゆっくり走ることにした。

三崎口駅からしばらくは単調な登りが続くが、ブロンプトンは６速しかないにも関わらずグイグイ登っていく。マウンテンバイクほどではないが少々の坂であれば全く苦にならない。

油壺付近の景色に見とれる

油壺マリーナ

三崎港を一気に目指すのではなく、途中西に向かって油壺方面の海岸線に沿って走る。この近辺は写真のようなヨットハーバーが続き外国の景色を見ているようだ。ここは神奈川県の景勝５０選にもなっているらしい。

三浦漁港

一方こちらは三崎漁港。多くの漁船が並んでのどかな風景が続く。ここも癒やされる景色だ。

城ヶ島へ渡り昼食

三崎港付近には観光客相手にまぐろを食べさせる店が多いが、今日の第１の目的は城ヶ島にある知人に教えてもらった「しぶき亭」という店でマグロを食べることにしていたので、橋を渡って城ヶ島へ行く。

城ヶ島大橋

城ヶ島大橋は乗用車であれば通行料を徴収されるが、自転車はダダで渡ることができる。橋からの眺めもよいしちょっと得した気分になれる。

しぶき亭はサイクリストの味方

「しぶき亭」はホームページに書いてあるようにサイクリストにとって優しい店だ。店の外にはバイクラックが置いてあるし、サイクリストだとわかるとゲソ天をサービスで付けてくれるらしい。

しぶき亭のバイクラック

ブロンプトンをラックにチェーンで結びつけていたら、店のおじさんが出てきて「今１３人のバイク集団がやってきて満席だからしばらく待てるか」と言ってきたので、何分くらい待つのか聞いたら「１５分くらい」というので、全く問題ないと答えしばらく店の中で待たせてもらうことにした。

１５分はあっという間に過ぎ、席に案内されメニューを見る。

ここはやっぱりマグロ丼だと思ったが、ゲソ天盛り合わせも気になるので両方頼もうとしたら、店のおばさんが「ゲソ天はおまけに付くから、足りなかったらまた注文すればいい」と言ってくれたので、マグロ丼１０００円を注文。

マグロ丼

考えてみたら朝慌ただしく家を出たので、起きてから何も食べていなかったことに気がついた。

いい汗をかいた後のマグロは最高！おばさんからは味が足りなかったら醤油をかけてと言われたが、その必要はまったくなかった。

マグロ丼は定番メニューだが、マグロカツ定食もボリュームがあって人気らしいので次は試してみよう。

春の三浦半島はのどか

宮川公園付近

三浦半島の突端には２基の風力発電機が回っている。これは実験施設らしいが風力発電機の下は宮川公園という公園になっている。

天気がいいせいかロードバイクに乗ったサイクリストが沢山走っている。でもフォールディング・バイクに乗っているのは私くらいだ。

劔崎灯台に寄り道をする

劔崎灯台パノラマ

（劔崎灯台から太平洋を望む）
９月に来た時は余裕がなかったが、フォールディング・バイクのような小径車はちょっとおもしろそうなところがあれば気軽に寄り道ができるのがいい。

気になっていた劔（つるぎ）崎灯台に至る細い道を行く。途中からは自転車を押していくような石畳の道になるがやっとのことで灯台にたどり着くと太平洋が一望できる。

劔崎灯台は１８７１年から東京湾の入り口を守っている。今から１４５年前からだ。

フジトモ商店でお約束のシュークリーム

フジトモ商店のシュークリーム

灯台から戻って少し行くと、台地の上に「ヤマザキショップ フジトモ商店」がある。昨年シュークリームが疲れた体の栄養補給に最適だったのを思い出し同じように買ってしまった。

急遽ショートカット

ここから海岸線まで一気に降りて三浦海岸駅付近まで走ってから三崎口駅まで戻る予定だったが、予想以上にゆっくり走ったのと夕方家の用事があったことを思い出したので、台地の上を三崎口駅までショートカットして戻ることにした。予定を簡単に変えられるのが自転車旅のいいところだ。

台地の上から見た景色

キャベツ畑の中の道からさっき通ってきた海岸線がよく見える。

実際のコース

再び三崎口駅へ。距離は２８．６kmと決して長くはなかったが充実の２時間だった。

帰りも輪行

帰りは三崎口駅から上大岡駅まで京急線に乗り、横浜市営地下鉄に乗り換えて湘南台駅まで行った。そして湘南台駅から自転車で自宅までこいで帰ってきた。

乗り換えは１回だけで、家にたどり着いのは１５時半。休みの日の自転車はいつも暗くなってからやっと家に辿り着くというのがお決まりのパターンだったが、今日はまだ全然明るいうちに返ってくることができた。

輪行でサイクリングに行くというのがこんなに楽しいとは、つまり目的地についてからのんびり自転車で見て回るということがどんなに楽しいか、実際にやってみて実感することができた。

これはやみつきになりそうだ！

Flashback Dropの検証④（最終回）

Flashback Dropのまとめ

Flashback Dropに関して、基本機能と容量管理について簡単な検証で確認をしたが最後におさらいをする。

  • Flashback DropはRECYCLEBIN初期化パラメータがON（デフォルト）の場合に有効な機能であり、OFFの場合はOracle9iまでと同じ動作となる。
  • テーブルをPURGEオプションなしでDropすると、テーブルは物理的に削除されずにリサイクルビンで管理される。これはリサイクルビン用の表領域に移動されるのではなく、同じ表領域のままデータ・ディクショナリから見えなくなるような仕組みで管理されることを意味する。
  • リサイクルビンで管理されているテーブルは「BIN$」で始まる名前にRenameされる。
  • テーブルに紐付くインデックスおよびPK制約もリサイクルビンで管理される対象であり、同様に「BIN$」で始まる名前にRenameされる。
  • FK制約はDropとともに削除される。
  • FLASHBACK TABLE <テーブル名> TO BEFORE DROP文でリサイクルビンにあるテーブルを削除直前に復元することができる。（名前は特に指定しないかぎり元に戻る）
  • 同時にインデックスとPK制約も復元される。（ただし名前は「BIN$」で始まる名前のまま。）従って一意制約は有効であり重複行をInsertしようとするとエラーになる。
  • FK制約は削除されているので復元されることはない。
  • リサイクルビンで管理されているオブジェクトは、セグメントを解放しない状態で表領域内に存在している。
  • 療養域の空きがなくなるまでセグメントは解放されないが、新たなオブジェクトのために領域が必要になると、削除時のSCNが古いセグメントから解放される。
  • この動作はユーザの操作を必要とせず自動的に行われる。

Flashback Dropは使える機能なのか？

上で述べたとおりFlashback Dropはデフォルトで使用できる機能であるが、積極的にオフにすべきなのだろうか？

検証した限り、私にはオフにする正当な理由が見つからない。というかオフにしてはいけない機能だと思う。

ところが、最近Amazon RDSでOracle11gR2 SE1環境をお試しで構築したところ、Recyclebin初期化パラメータが「OFF」になっていた。

パラメータグループを作成し、デフォルト値の「ON」に変更することは可能なはずなので問題はないのだが、なぜこのような設定値になっているのかは不明だ。

現行踏襲の呪縛

この検証シリーズは、JPOUG Tech Talk Night #6のライトニングトークで披露させていただいたネタなのだが、その中でこの「現行踏襲の呪縛」という言葉に対する反応が意外によかった。

つまり、アップグレードで多くの新機能が使えるようになっても、「新たなバグを引きたくない。」というような消極的な理由から、新機能を使わないということが現場では非常に多い。

「新機能にむやみに飛びつくのは素人だ。」と言わんばかりにベテランDBAが結果として間違った方向にミスリードし、小さな問題を大きくしてしまうとすればこれほど残念なことはない。

  • マニュアルをよく読んで誤解をしない。
  • 自分で検証して内部動作・仕様を確認する。

こういうことを怠って、自分の思い込みだけで判断するから、技術の進歩を素直に認めない空気が醸成されるのだ。

もう２０年近く前のことで私がまだ経験の浅いエンジニアだったころ、Oracle Master の保有率が国内トップクラスというある開発会社に転職した時の話である。

「ウチの会社では外部キー制約は使わないことになっているから」と言われて面食らった。

リレーショナル・データベースというのものを正しく理解せずに実装しているシステムがどんな結末をたどるか、ということをこの会社にいる間に身をもって経験した。

ある地方におけるプロジェクトが炎上し、何人もの在京エンジニアが投入されたが皆討ち死にするような状況となった末、その会社は遠方で苦しんでいるメンバーに対し、残りの社員全員で励ましの寄せ書きを送ることにした。

今思えばとんでもないブラックな会社だったと思うが、強烈な違和感を持った私はやがて別の会社に転職をした。

その決断は間違っていなかったと確信している。

おまけ

大事なことを忘れたのだが、この検証はWindows7 64bit環境で以下のバージョンで実施した。

SQL> select banner from v$version;

BANNER
------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit Production

PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

10gではメタデータがリサイクルビンに残っていた？

古い話だが、Flashback Dropが出た10gでは不思議な仕様だった。

以下は、オペレーション・ログからの抜粋だが、リサイクルビンの中で一番古いオブジェクトのUSER_RECYCLEBIN.SPACEが「0」のものがあることに気がついた。

試しにこのテーブルをフラッシュバックしたところ、テーブル構造（メタデータ）が復元されたにも関わらずデータが０件だった。

SQL> select ORIGINAL_NAME,TYPE,SPACE from user_recyclebin
  2  where CREATETIME = (select min(CREATETIME) from user_recyclebin);

ORIGINAL_NAME                    TYPE                           SPACE
-------------------------------- ------------------------- ----------
TABLE********                    TABLE                              0
INDEX************                INDEX                              0
PK_*************                 INDEX                              0

SQL> flashback table TABLE******** to before drop rename to TABLE********_RCV;

フラッシュバックが完了しました。

SQL> desc TABLE********_RCV
 名前                                        NULL?    型
 ----------------------------------------- -------- ------------------
 COL1                                      NOT NULL NUMBER(22)
 COL2                                               NUMBER(22)
 COL3                                               NUMBER(22)
 COL4                                               DATE
 COL5                                               DATE
 COL6                                               NUMBER(22)
 COL7                                               DATE

SQL> select * from TABLE********_RCV;

レコードが選択されませんでした。

よくよく確認したところ、メタデータが５万件以上もリサイクルビンに溜まっていた。

どうやら夜間バッチ処理の中でDROPを沢山発行していたようだった。（9iからのスクリプトでPURGEオプションなしでDROPを実行していた。）

これをPURGE RECYCLEBINコマンドで一気に削除したのだが、わずか数秒で終わってしまった。

今では見ることができない不思議な現象だった。

終わり

Flashback Dropの検証③

容量管理はどうなっているか？

前回はFlashback Dropの基本的な動作を確認した。

テーブルを削除すると「リサイクルビン」という特別な場所に移動されるわけではなく、同じ表領域にセグメントとして存在しつつもエクステント情報から見えなくなるだけであることがわかった。

また、削除されたテーブルとそれに紐づくインデックスとPK制約は、BIN$で始まる名前にRenameされる。
そして、Flashback Dropによって削除前に戻されたテーブルは、元の名前に戻るが（任意の名前にすることは可能）インデックスとPK制約はBIN$で始まる名前から元に戻ることがないこともわかった。

それでは削除されたオブジェクトが「リサイクルビン」セグメントとして表領域の中に溜まっていった場合、有限な表領域はいずれ一杯になってしまうはずである。
WindowsやMacOSの場合明示的にゴミ箱を空にしないとディスク領域は解放されないが、OracleのリサイクルビンもPERGEコマンドを発行しないと解放されないのであろうか？

マニュアルをよく読めば答えは書いてあるのだが、実際に確かめてみよう。

JPOUG20160223.001

初期状態

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

EMP表をコピーしてEMP1表を作成する

CTAS(Create Table As Select)でEMP表からEMP1表を作成する。

SQL> create table EMP1 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_SEG%
----------
        70


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


レコードが選択されませんでした。

EMP1表にインデックスを作成する

SQL> create unique index PK_EMP1 on EMP1 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_SEG%
----------
        80


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP1                          EMP1


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- -----------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


レコードが選択されませんでした。

インデックスPK_EMP1を作成したところまでを図にすると以下のようになる。

JPOUG20160223.018

EMP1表をDropする

SQL> drop table EMP1;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_SEG%
----------
        80


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

JPOUG20160223.019
エクステントの空き領域が初期状態と同じ40%になっているが、セグメントの空き領域が20%となっていることに注目してもらいたい。

EMP2表とインデックスを作成する

SQL> create table EMP2 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES


SQL> create unique index PK_EMP2 on EMP2 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP2                          EMP2


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

JPOUG20160223.020
この時点で、セグメント使用率が100%に達していることがわかる。

EMP2表をDropする

ここでEMP2表を削除して表領域使用率を初期状態と同じ60%にしてみよう。

SQL> drop table EMP2;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

JPOUG20160223.021
表領域としては40%の空きが確保できたが、セグメントは100%使用されている状態である。

この状態でさらにオブジェクトを作成してどうなるかを確認しよう。

EMP3表を作成する

ここからはオブジェクトを1つずつ作成する。

SQL> create table EMP3 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

まず、EMP1に該当するリサイクルビン・セグメントが解放され、EMP1に紐づくPK_EMP1も解放される。

これはDROPSCNの小さい、つまり先にDropされたものから解放される仕様となっているので、正確にはPK_EMP1 → EMP1の順に解放される。

20%分が解放され10%分が消費されたので、セグメントは差し引き90%使用されていることになる。

PK_EMP3を作成する

SQL> create unique index PK_EMP3 on EMP3 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

JPOUG20160223.022

図はリサイクルビンの２つのオブジェクトを解放し、新たな２つのオブジェクトを作成したところである。

EMP4およびPK_EMP4を作成する

SQL> create table EMP4 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_EXT%
----------
        90


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90


レコードが選択されませんでした。

SQL> create unique index PK_EMP4 on EMP4 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_EXT%
----------
       100


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3
PK_EMP4                          EMP4


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                80              80          100


レコードが選択されませんでした。

JPOUG20160223.023

EMP2、PK_EMP2に該当するリサイクルビン・オブジェクトが解放され新たなオブジェクトが作成された。表領域の使用率は100%である。

ここまで、明示的なPURGE RECYCLEBINコマンドは一度も発行していない。つまりリサイクルビン・オブジェクトにより表領域中のセグメントが100%使用されていたとしても、Oracleは古いものから適宜解放し、新たなオブジェクトを作成する領域を自動的に確保する。

PK_EMP4のみ削除する

テーブルを削除するとテーブルに紐づくインデックスも自動的にリサイクルビンで管理されることはわかったが、インデックスのみ削除した場合はどうなるだろうか？

SQL> drop index PK_EMP4;

索引が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_EXT%
----------
        90


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90


レコードが選択されませんでした。

JPOUG20160223.024

インデックスのみを削除してもリサイクルビンでは管理されない。単純に削除されて領域も解放される。

これは考えてみれば簡単なのだが、テーブルがあればインデックスは何度でも再作成可能なためである。

今日はここまで