alter table move 和shrink space的相关测试

scott@PRIMARY> begin
  2  dbms_stats.gather_table_stats('SCOTT','HEAP_ADDRESSES',cascade=>true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

scott@PRIMARY> col index_name format a25;
scott@PRIMARY> col table_name format a25;
scott@PRIMARY> select index_name,table_name,status from user_indexes;

INDEX_NAME                TABLE_NAME                STATUS
------------------------- ------------------------- ----------------
SYS_C005354               HEAP_ADDRESSES_BAK        VALID
BIG_TABLE_PK              BIG_TABLE                 VALID
SYS_IOT_TOP_51281         IOT_ADDRESSES             VALID
SYS_C005352               HEAP_ADDRESSES            VALID
EMP_PK                    EMPT                      VALID
PK_EMP                    EMP                       VALID
PK_DEPT                   DEPT                      VALID

已选择7行。

scott@PRIMARY> set autotrace traceonly;
scott@PRIMARY> set autotrace trace stat;
scott@PRIMARY> select * from heap_addresses;

已选择245935行。

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      18138  consistent gets
          0  physical reads
       3840  redo size
    5344288  bytes sent via SQL*Net to client
     180737  bytes received via SQL*Net from client
      16397  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     245935  rows processed

scott@PRIMARY>
这里有18138个一致性读,下一步删除数据.
scott@PRIMARY> set autotrace off;
scott@PRIMARY> delete from heap_addresses;

已删除245935行。

scott@PRIMARY> commit;

提交完成。

scott@PRIMARY>
为了测试准确,下面要做一步块清除(delayed block cleanout)
scott@PRIMARY> begin
  2  dbms_stats.gather_table_stats('SCOTT','HEAP_ADDRESSES',cascade=>true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

scott@PRIMARY>
继续查询,观察一致性读
scott@PRIMARY> set autotrace traceonly;
scott@PRIMARY> set autotrace trace stat;
scott@PRIMARY> select * from heap_addresses;

未选定行

统计信息
----------------------------------------------------------
         74  recursive calls
          0  db block gets
       3514  consistent gets
        572  physical reads
     124748  redo size
        497  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@PRIMARY>
仍然还有3514个一致性读,move表
scott@PRIMARY> alter table heap_addresses move;

表已更改。

scott@PRIMARY> select * from heap_addresses;

未选定行

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        497  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@PRIMARY> set autotrace off;
scott@PRIMARY> select index_name,table_name,status from user_indexes;

INDEX_NAME                TABLE_NAME                STATUS
------------------------- ------------------------- ----------------
SYS_C005354               HEAP_ADDRESSES_BAK        VALID
BIG_TABLE_PK              BIG_TABLE                 VALID
SYS_IOT_TOP_51281         IOT_ADDRESSES             VALID
SYS_C005352               HEAP_ADDRESSES            VALID
EMP_PK                    EMPT                      VALID
PK_EMP                    EMP                       VALID
PK_DEPT                   DEPT                      VALID

已选择7行。

scott@PRIMARY>
注意当move完以后,有可能会造成索引状态变成UNUSABLE,仅仅是有可能,出现以后需要rebuild索引.
整个测试中间我没有去查询HWM,实际上也不用查询,HWM主要副作用是会引起查询语句多一些一致性读.
以上测试主要测试目的是看HWM的回收情况以及是否需要rebuild index.

继续测试shrink space
scott@PRIMARY> set autotrace traceonly;
scott@PRIMARY> set autotrace trace stat;
scott@PRIMARY> select * from heap_addresses;

已选择245935行。

统计信息
----------------------------------------------------------
          2  recursive calls
          1  db block gets
      17886  consistent gets
        223  physical reads
        176  redo size
    5344288  bytes sent via SQL*Net to client
     180737  bytes received via SQL*Net from client
      16397  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     245935  rows processed

scott@PRIMARY> delete from heap_addresses;

已删除245935行。

scott@PRIMARY> analyze table heap_addresses compute statistics for table for all
 indexes for all indexed columns;

表已分析。

scott@PRIMARY> select * from heap_addresses;

未选定行

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1571  consistent gets
          0  physical reads
          0  redo size
        497  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@PRIMARY> alter table heap_addresses shrink space;
alter table heap_addresses shrink space
*
第 1 行出现错误:
ORA-10635: Invalid segment or tablespace type

scott@PRIMARY> alter table heap_addresses nocompress;

表已更改。

scott@PRIMARY> alter table heap_addresses shrink space;

表已更改。

scott@PRIMARY> select * from heap_addresses;

未选定行

统计信息
----------------------------------------------------------
        205  recursive calls
          0  db block gets
         45  consistent gets
          1  physical reads
          0  redo size
        497  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@PRIMARY>
shring space使用起来有诸多限制,十分不方便:
Restrictions on the shrink_clause, 10gR2
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
2. Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
3. This clause does not shrink mapping tables of index-organized tables,even if you specify CASCADE.
4. You cannot specify this clause for a compressed table.
5. You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

1. 对cluster，cluster table，或具有Long类型列的对象 不起作用。
2. 不支持具有function-based indexes 或 bitmap join indexes的表
3. 不支持mapping 表或index-organized表。
4. 不支持compressed 表

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16628454/viewspace-607762/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16628454/viewspace-607762/