由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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25116248/viewspace-1266847/