oracle 段空間管理問題

 

show_space過程內容：
create or replace procedure show_space
    ( p_segname in varchar2,
      p_owner   in varchar2 default user,
      p_type    in varchar2 default 'TABLE' )
    as
       l_free_blks                 number;
       l_total_blocks              number;
       l_total_bytes               number;
       l_unused_blocks             number;
       l_unused_bytes              number;
       l_LastUsedExtFileId         number;
       l_LastUsedExtBlockId        number;
     l_LAST_USED_BLOCK           number;
       procedure p( p_label in varchar2, p_num in number )
       is
       begin
           dbms_output.put_line( rpad(p_label,40,'.') ||
                                 p_num );
       end;
   begin
       dbms_space.free_blocks
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         freelist_group_id => 0,
         free_blks         => l_free_blks );
       dbms_space.unused_space
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         total_blocks      => l_total_blocks,
         total_bytes       => l_total_bytes,
         unused_blocks     => l_unused_blocks,
         unused_bytes      => l_unused_bytes,
         LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
         LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
         LAST_USED_BLOCK => l_LAST_USED_BLOCK );

       p( 'Free Blocks', l_free_blks );
       p( 'Total Blocks', l_total_blocks );
       p( 'Total Bytes', l_total_bytes );
       p( 'Unused Blocks', l_unused_blocks );
       p( 'Unused Bytes', l_unused_bytes );
       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
       p( 'Last Used Block', l_LAST_USED_BLOCK );
end;

create table bigcol(y varchar2(2048)) nologging;

執行show_space報錯：
call show_space('BIGCOL','LYON')
     *
第 1 行出現錯誤:
ORA-10618: Operation not allowed on this segment
ORA-06512: 在 "SYS.DBMS_SPACE", line 152
ORA-06512: 在 "SYS.SHOW_SPACE", line 21
ORA-06512: 在 line 1

發現這是由於表空間的ASSM方式引起的。
要能夠執行該指令碼，則需要在手動段空間管理模式下。
建立一個MSSM表空間：
SQL> create tablespace mantbs datafile 'E:\oracle\oradata\lyon\mantbs01.dbf' size 10m uniform. size 1m segment space management manual;
表空間已建立。

將使用者在mantbs上的空間配額修改為不限制：
SQL> alter user lyon quota unlimited on mantbs;
使用者已更改。

將表移動到該表空間下：
SQL> alter table bigcol move tablespace mantbs
  2  /
表已更改。

SQL> call show_space('BIGCOL',user);

呼叫完成。

SQL> show message;
SP2-0158: 未知的 SHOW 選項 "message"
SQL> set serveroutput on;
SQL> /
Free Blocks.............................0
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384
Last Used Ext FileId....................23
Last Used Ext BlockId...................137
Last Used Block.........................1

呼叫完成。
即可檢視該表佔用空間情況。

往bigcol表插入10000條資料：
SQL> insert into bigcol
  2  select dbms_random.string('X', 2048) from dual connect by rownum <= 10000;
insert into bigcol
            *
第 1 行出現錯誤:
ORA-01653: 表 LYON.BIGCOL 無法透過 128 (在表空間 MANTBS 中) 擴充套件

出現空間不夠問題
修改資料檔案大小，擴充套件表空間到100m：
SQL> alter database datafile 'E:\oracle\oradata\lyon\mantbs01.dbf' resize 100m;

資料庫已更改。

SQL> insert into bigcol
  2  select dbms_random.string('X', 2048) from dual connect by rownum <= 10000;

已建立10000行。

SQL> commit;

提交完成。

檢視錶使用空間情況：
SQL> call show_space('BIGCOL',user);
Free Blocks.............................4
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................118
Unused Bytes............................966656
Last Used Ext FileId....................23
Last Used Ext BlockId...................3337
Last Used Block.........................10

呼叫完成。
總計使用block 3456，未使用118，則已使用：3456-118 = 3338，即hwm為3338
用delete刪除所有記錄：
SQL> delete from bigcol;
已刪除10000行。
SQL> commit;
提交完成。
SQL> call show_space('BIGCOL',user);
Free Blocks.............................3337
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................118
Unused Bytes............................966656
Last Used Ext FileId....................23
Last Used Ext BlockId...................3337
Last Used Block.........................10
呼叫完成。

發現free blocks由原來的4個變為現在的3337。但是已使用的blocks：3456 - 118 = 3338，即hwm仍為3338。
檢視執行時間：
10:24:19 SQL> select count(*) from bigcol;

  COUNT(*)
----------
         0

已用時間:  00: 00: 00.84

截斷表：
10:24:25 SQL> truncate table bigcol;
表被截斷。
已用時間:  00: 00: 02.81
10:24:50 SQL> call show_space('BIGCOL',user);
Free Blocks.............................0
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384
Last Used Ext FileId....................23
Last Used Ext BlockId...................9
Last Used Block.........................1
呼叫完成。

發現已使用的blocks：128 - 127 = 1，即hwm收縮為1。
再次執行count查詢：
10:26:28 SQL> select count(*) from bigcol;

  COUNT(*)
----------
         0

已用時間:  00: 00: 00.01
在每次執行count之前都做了buffer cache的清理，已保證每次都是從disk讀取的。
發現兩次查詢的時間發生了明顯的變化，由原來的0.84s縮小為現在的0.01s。

參考了：
http://tolywang.itpub.net/post/48/307529

引用裡面對此的總結：
在9I中:

(1)如果MINEXTENT 可以使ALTER TABLE TABLENAME DEALLOCATE UNUSED將HWM以上所有沒使用的空間釋放
(2)如果MINEXTENT >HWM 則釋放MINEXTENTS 以上的空間。如果要釋放HWM以上的空間則使用KEEP 0。
ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0;
(3) TRUNCATE TABLE DROP STORAGE(預設值)命令可以將MINEXTENT 之上的空間完全釋放(交還給作業系統),並且重置HWM。

(4)如果僅是要移動HWM,而不想讓表長時間鎖住,可以用TRUNCATE TABLE REUSE STORAGE,僅將HWM重置。
(5)ALTER TABLE MOVE會將HWM移動,但在MOVE時需要雙倍的表空間,而且如果表上有索引的話,需要重構索引

(6)DELETE表不會重置HWM,也不會釋放自由的空間(也就是說DELETE空出來的空間只能給物件本身將來的INSERT/UPDATE使用,不能給其它的物件使用)

在ORACLE 10G:

(1)可以使用ALTER TABLE TEST_TAB SHRINK SPACE命令來聯機移動HWM,

(2)如果要同時壓縮表的索引,可以釋出:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE