由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.99) as Total,
       to_char((b.total-a.free)/1024/1024,999999.99) as Used,
       to_char(a.free/1024/1024,999999.99) as Free,
       to_char(round((total-free)/total,4)*100,999.99) as 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/，如需轉載，請註明出處，否則將追究法律責任。