由ora-30036引出的問題,給大表新增列的時候,不要設定預設值

wangyiou1988發表於2014-09-10
      由ora-30036引出的問題,給大表新增列的時候,不要設定預設值

今天同事在執行一個指令碼的時候,報了個錯,我一看,這是undo表空間滿了啊,然後我就單獨執行指令碼里內容,執行第一行執行了半個小時,也沒執行完,強制中斷後hang在那,造成了鎖表,常規的殺程式不好使,最後殺os程式才解決。第一行的內容是:
alter table BSVCBUSCANDATA add ISSHOW CHAR(1)  default '0'; 
後來在半夜裡我又執行了一下,結果執行了2個小時也沒執行完,期間我不斷的查詢undo表空間的使用,發現已經超過了50%,最後還是強制中斷,hang在那不動了。

SELECT a.tablespace_name as tablespace_name,
       to_char(b.total
/1024/1024,999999.99as Total,
       to_char((b.total
-a.free)/1024/1024,999999.99as Used,
       to_char(a.free
/1024/1024,999999.99as Free,
       to_char(
round((total-free)/total,4)*100,999.99as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
     (
SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
  
AND a.tablespace_name='UNDOTBS1'
ORDER BY a.tablespace_name;


        我檢視了一下,發現 BSVCBUSCANDATA是一個大表,有300G左右,undo表空間有32G,重新修改 BSVCBUSCANDATA, 空間怎麼都不夠啊,最後肯定就滿了啊,就報了那個錯,我一開始想到的方法是刪除表裡的歷史資料,後來請教同事,同事說因為有預設default 0,所以才會重寫,這樣執行1天都執行不完,沒有default 0,不設定預設值,這一列馬上就新增上了。後來一試果然成功了。

       總結:當我們在遇到undo表空間滿了時候,不要貿然的增加undo表空間尺寸,一定要搞清楚內因,為什麼會滿。在生產中,對一個大表進行操作的時候,一定要考慮好代價,特別是大規模DML工作,給表新增一列,不僅造成了資料庫的壓力,也造成了鎖表,而且這種鎖表透過在資料庫層面的kill,根本無濟於事,必須在os層面殺死。更重要的是:在生產中為一個海量大表新增一列的時候,千萬不要設定預設值。

附:
給undo表空間大小重新加大點的語句
  alter database datafile '' resize 10G;
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25116248/viewspace-1266847/,如需轉載,請註明出處,否則將追究法律責任。

相關文章