測試表的空間壓縮與表空間的關係

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.

根據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---