日別アーカイブ: 2016/03/06

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

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

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

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

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

今日はここまで