oracle10g_alter table shrink space_compact_cascade回收空间测试(一)

SQL> exec dbms_stats.gather_table_stats('scott','t_test_partition_1');

PL/SQL 过程已成功完成。

SQL> select initial_extent From user_tables where table_name='T_TEST_PARTITION_1
';

INITIAL_EXTENT
--------------

问题:为何初始区无值呢

-----------建立测试表
SQL> create table t_hwm(a int);

表已创建。

--------查询测试表对应段的分配信息,说明此时未给测试表分配空间
SQL> select segment_name,blocks,retention,freelists,buffer_pool,flash_cache from
 user_segments where segment_name='T_HWM';

未选定行

SQL> desc user_tables;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(20)
 INSTANCES                                          VARCHAR2(20)
 CACHE                                              VARCHAR2(10)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 COMPRESS_FOR                                       VARCHAR2(12)
 DROPPED                                            VARCHAR2(3)
 READ_ONLY                                          VARCHAR2(3)
 SEGMENT_CREATED                                    VARCHAR2(3)
 RESULT_CACHE                                       VARCHAR2(7)

---查询测试的相关信息,
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM

----插入数据至测试表
SQL> insert into t_hwm select level from dual connect by level<1e4;

已创建9999行。

SQL> commit;

提交完成。

---查询测试表信息,无信息因为未分析表
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 过程已成功完成。

----分析表再次查询即有数据
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM                                  20            0       9999

SQL> select segment_name,blocks from user_segments where segment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

    BLOCKS
----------
T_HWM
        24

----查询测试表的区分配信息
SQL> select segment_name,segment_type,extent_id,blocks from user_extents where s
egment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE        EXTENT_ID     BLOCKS
------------------ ---------- ----------
T_HWM
TABLE                       0          8

T_HWM
TABLE                       1          8

T_HWM
TABLE                       2          8

 

---------查询测试表数据
SQL> select count(*) from t_hwm;

  COUNT(*)
----------
      9999

----删除测试表部分数据
SQL> delete from t_hwm where rownum<=1000;

已删除1000行。

SQL> commit;

提交完成。

---------删除部分数据后查询测试表的区分配情况,未发生变化
SQL> select segment_name,segment_type,extent_id,blocks from user_extents where s
egment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE        EXTENT_ID     BLOCKS
------------------ ---------- ----------
T_HWM
TABLE                       0          8

T_HWM
TABLE                       1          8

T_HWM
TABLE                       2          8

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 过程已成功完成。

---分析表后查询测试表段未发生变化
SQL> select segment_name,blocks from user_segments where segment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

    BLOCKS
----------
T_HWM
        24

----分析表后查询表的相关信息,未发生变化
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM                                  20            0       8999

SQL>

---运用oracle10g shrink space子句对hwm之下已删除的空间进行释放
----shrink space要开启表的行移功功能,因为行的rowid会发生变更
SQL> alter table t_hwm shrink space;
alter table t_hwm shrink space
*
第 1 行出现错误:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table t_hwm enable row movement;

表已更改。

SQL> alter table t_hwm shrink space;

表已更改。

 

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 过程已成功完成。

-----如下说明shrink space操作生效,blocks由原20变更为14,释放了6个block

SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM                                  14            0       8999

------查询现在测试表的记录数
SQL> select count(*) from t_hwm;

  COUNT(*)
----------
      8999

---删除测试表的部分数据
SQL> delete from t_hwm where rownum<1000;

已删除999行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 过程已成功完成。

----删除数据后的表占用blocks仍为14,未释放出来
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM                                  14            0       8000

SQL>

-------测试shrink space compact选项的用法
SQL> alter table t_hwm shrink space compact;

表已更改。

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 过程已成功完成。

-----运行shrink及选项compact后表的blocks仍为14,
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM                                  14            0       8000

----再次运行shrink子句
SQL> alter table t_hwm shrink space;

表已更改。

--此处略去分析表语句,blocks反而由原14增加为21,怪怪了?原因何在?
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM                                  21            0       8000

 

-------运行insert多次插入数据至测试
SQL> insert into t_hwm select level from dual connect by level<=2e6;

已创建2000000行。

SQL> commit
  2  ;

提交完成。

SQL> insert into t_hwm select level from dual connect by level<=2e6;

已创建2000000行。

SQL> commit;

提交完成。

SQL> select count(*) from t_hwm;

  COUNT(*)
----------
   4008000

-----删除测试表部分数据
SQL> delete from t_hwm where rownum<=200000;

已删除200000行。

SQL> commit;

提交完成。

---如下分别在不同会话运行shrink space子句,并在另一个会话监控操作期间持锁情况,以便分析对于此表并发dml的影响
SQL> alter table t_hwm shrink space;

表已更改。

SQL> select object_id from dba_objects where object_name='T_HWM';

 OBJECT_ID
----------
    135014

------未运行shrink之前的持锁情形
sysdba_session>select sid,type,id1,id2,lmode,request from v$lock where sid=193;

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
       193 AE        100          0          4          0

----运行shrink space之后的持锁情形,经对比,操作期间会对表持3级行级排它锁tm和事务锁tx
sysdba_session>/

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
       193 AE        100          0          4          0
       193 OD     135014          0          3          0
       193 SK          4   17078010          6          0
       193 TM     135014          0          3          0
       193 TX     393223      53462          6          0
       193 TX     393219      53461          6          0

已选择6行。

-------测试shrink compact持锁情形
测试语句略去,经测试同shrink子句的持锁一样

----小结:1,shrink子句仅适用于assm管理的表
         2,shrink马上释放高水位线
         3,shrink compact仅压实表段,为以后释放空间作好准备,但并不马上释放空间;
         4,shrink compact适用于分两步释放表空间,而不会像alter table shrink space消耗过多的时间
         5,cascade选项,会同时把依赖于表的相关对象进行回收空间
        补充:shrink可适用于表,分区表,子分区,索引,分区索引,iot,物化视图,lob segment
       

 

---------shrink功能的几条限制:

---如果包含long列的表,不能使用此功能,测试如下:
----添加long列
SQL> alter table t_hwm add b long;

表已更改。

SQL> update t_hwm set b=rowid;

已更新3608000行。

SQL> commit;

提交完成。

SQL> desc t_hwm;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 A                                                  NUMBER(38)
 B                                                  LONG

SQL> select count(*) from t_hwm;

  COUNT(*)
----------
   3608000

SQL>

SQL> delete from t_hwm where rownum<=200000;

已删除200000行。

SQL> commit;

提交完成。

---瞧,明确提示测试表有long列,不能回收空间了
SQL> alter table t_hwm shrink space;
alter table t_hwm shrink space
*
第 1 行出现错误:
ORA-10662: Segment has long columns

---删除列b
SQL> alter table t_hwm drop column b;

表已更改。

---clob及blob列的表可以回收空间,看下面的测试

SQL> alter table t_hwm add b clob;

表已更改。

SQL> update t_hwm set b='ab' where rownum<=2000;

已更新2000行。

SQL> commit;

提交完成。

SQL> delete from t_hwm where rownum<=100;

已删除100行。

SQL> commit;

提交完成。

---clob列可以进行回收空间,blob也同理,略去代码
--注:clob列的回收很是消耗时间和资源,有空要研究下clob列的存储和优化问题
SQL> alter table t_hwm shrink space;

表已更改。

SQL>

---oracle官方手册讲,对于函数索引及位图连接索引的表,不能进行回收,真是如此吗,见测试
00:00:46 SQL> desc t_hwm;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------

 A                                                  NUMBER(38)

00:01:21 SQL> create index idx_t_hwm_func on t_hwm(length(a));

索引已创建。

已用时间:  00: 00: 04.16
00:02:31 SQL> select count(*) from t_hwm;

  COUNT(*)
----------
   3407900

已用时间:  00: 00: 00.53
00:02:42 SQL> delete from t_hwm where rownum<=20;

已删除20行。

已用时间:  00: 00: 00.03
00:02:53 SQL> commit;

提交完成。

已用时间:  00: 00: 00.00

---如果表列建有函数索引,不能进行回收空间
00:02:55 SQL> alter table t_hwm shrink space;
alter table t_hwm shrink space
*
第 1 行出现错误:
ORA-10631: SHRINK clause should not be specified for this object

 

----------下面是3条关于回收子句的限制,不再一一测试,记录于此,供备查所用
 does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
 

You cannot specify this clause for a compressed table.
 

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.
 

已用时间:  00: 00: 00.02
00:03:06 SQL>

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

转载于:http://blog.itpub.net/9240380/viewspace-751862/