测试表的空间压缩与表空间的关系

  当一个大表的数据用delete删除后，其空间并没有回收，有时候我们通过alter table的shrink space子句实现空间的回收。

下面是shrink子句的文档解释：

shrink_clause 

The shrink clause lets you manually shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

子句后面有两个可选项：[COMPACT]|[CASCADE]

COMPACT

If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

For an index or index-organized table, specifying ALTER [INDEX | TABLE] ... SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ... COALESCE. The shrink_clause can be cascaded (please refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not wish to release the unused space, then you can use the appropriate COALESCE clause.

CASCADE

If you specify CASCADE, then Oracle Database performs the same operations on all dependent objects oftable, including secondary indexes on index-organized tables.

Restrictions on the shrink_clause

• You cannot specify this clause for a cluster, a clustered table, or any object with a LONGcolumn.

• Segment shrink is not supported for LOB segments even if CASCADE is specified.

• Segment shrink is not supported for tables with function-based indexes.

• This clause does not shrink mapping tables or overflow segments of index-organized tables, even if you specify CASCADE.

• 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.

根据shrink的要求，使表所在的表空间段管理模式为自动才能进行空间的回收，下面做一个测试：

1、先建两个不同的表空间：

SQL> create tablespace test01 datafile 'd:\oradata\orcl\test01.dbf' size 20M
 2  extent management local
 3  segment space management manual;

表空间已创建。

SQL> create tablespace test02 datafile 'd:\oradata\orcl\test02.dbf' size 20M
 2  extent management local
 3  segment space management auto;

表空间已创建。

2、在不同的表空间建一样大小的表

SQL> create table big01 tablespace test01
 2  as select * from all_objects;

表已创建。

SQL> create table big02 tablespace test02
 2  as select * from all_objects;

表已创建。 

SQL> select table_name,tablespace_name from dba_tables where table_name='BIG01'; 

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
BIG01                          TEST01

SQL> select table_name,tablespace_name from dba_tables where table_name='BIG02';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
BIG02                          TEST02

3、查看空间的使用情况

SQL> set line 120
SQL> select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
 2  ,       decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
 3  ,       decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
 4                 100 - ceil(tsf.free_mb/tsu.used_mb*100)) "%used"
 5  from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
 6          from    dba_data_files group by tablespace_name union all
 7          select  tablespace_name || '  **TEMP**'
 8          ,       sum(bytes)/1024/1024 used_mb
 9          from    dba_temp_files group by tablespace_name) tsu
10  ,       (select tablespace_name, sum(bytes)/1024/1024 free_mb
11          from    dba_free_space group by tablespace_name) tsf
12  where   tsu.tablespace_name = tsf.tablespace_name (+) and tsu.tablespace_name in ('TEST01','TEST02')
13  order   by 4;

TABLESPACE_NAME                             size MB    free MB      %used
---------------------------------------- ---------- ---------- ----------
TEST01                                           20         14         30
TEST02                                           20         14         30

4、删除所有数据

SQL> delete from big01;

已删除50663行。

SQL> commit;

提交完成。

SQL> delete from big02;

已删除50664行。

SQL> commit;

提交完成。

SQL> select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
 2  ,       decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
 3  ,       decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
 4                 100 - ceil(tsf.free_mb/tsu.used_mb*100)) "%used"
 5  from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
 6          from    dba_data_files group by tablespace_name union all
 7          select  tablespace_name || '  **TEMP**'
 8          ,       sum(bytes)/1024/1024 used_mb
 9          from    dba_temp_files group by tablespace_name) tsu
10  ,       (select tablespace_name, sum(bytes)/1024/1024 free_mb
11          from    dba_free_space group by tablespace_name) tsf
12  where   tsu.tablespace_name = tsf.tablespace_name (+) and tsu.tablespace_name in ('TEST01','TEST02')
13  order   by 4;

TABLESPACE_NAME                             size MB    free MB      %used
---------------------------------------- ---------- ---------- ----------
TEST01                                           20         14         30
TEST02                                           20         14         30

5、因为在回收空间时必须使表enable行移动

SQL> alter table big01 enable row movement;

表已更改。

SQL> alter table big02 enable row movement;

表已更改。

6、下面对两个表进行空间回收

SQL> alter table big01 shrink space;
alter table big01 shrink space
*
第 1 行出现错误:
ORA-10635: Invalid segment or tablespace type


SQL> alter table big02 shrink space;

表已更改。

SQL> select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
 2  ,       decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
 3  ,       decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
 4                 100 - ceil(tsf.free_mb/tsu.used_mb*100)) "%used"
 5  from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
 6          from    dba_data_files group by tablespace_name union all
 7          select  tablespace_name || '  **TEMP**'
 8          ,       sum(bytes)/1024/1024 used_mb
 9          from    dba_temp_files group by tablespace_name) tsu
10  ,       (select tablespace_name, sum(bytes)/1024/1024 free_mb
11          from    dba_free_space group by tablespace_name) tsf
12  where   tsu.tablespace_name = tsf.tablespace_name (+) and tsu.tablespace_name in ('TEST01','TEST02')
13  order   by 4;

TABLESPACE_NAME                             size MB    free MB      %used
---------------------------------------- ---------- ---------- ----------
TEST02                                           20         20          0
TEST01                                           20         14         30

可以发现在用shrink space时，必须使它处在自动段空间管理的表空间中才成功。

---End---

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

转载于:http://blog.itpub.net/9932141/viewspace-677388/