修改表增加字段默认值default
2013-09-28 10:11:51 我来说两句 作者：海鲨Oracle
收藏 我要投稿
修改表增加字段默认值default
对个生产库的表增加1个字段. 字段类型是INT 型, 表数据有2千万条,
?
1 a lter table table_name add xxoo number(4) default 0 ;
因此 不仅要修改字典, 还要刷新全部数据.
1） 在ALTER sql中有带缺省值，ORACLE 会直接刷新全部的记录。
2） 在ALTER sql中没有带缺省值，ORACLE 只会影响到后来的记录。
?
1 alter table table_name add xxoo number(4) default null;
2 Table altered
3
4 Executed in 0.062 seconds
带有default null 就可以了
?
1 alter table table_name add xxoo number(4) default 0;
2
3 Table altered
4 Executed in 1.625 seconds
原来的话 要更新所有的行, 会导致UNDO 段占用
使用语句Alter table a add test number(10) default 0;更新一个大表中字段时，表有四个分区，数据达到几十亿行，增加一个字段竟然要几个小时的时间，修改语句加上Nologging ，怎么没有作用呢？去找是不是哪有锁了呢，使用语句 select *
from dba_locks where lock_id1=33784;发现Session_id为14的一直在执行，那么他在执行什么呢！查询一下吧。 使用语句：
?
select a.sid,a.username,c.SQL_TEXT from v$session a, dba_locks b,v$sqlarea c 2 where b.lock_id1=33784 and a.SID=b.session_id 3 and a.SQL_ADDRESS=c.ADDRESS; 1
哦，原来他在Update Test 字段值为0. 至此总结到，原来Alter 之后做的竟然是Update ，也明白了为什么Undo 表空间会爆涨。去掉Default 0，呵呵，很快就OK 了。
建议没有必要时慎用Default
添加具有默认值的列
尽管对该特性感到满意，Jill 还在考虑与第一个问题相关的另一个问题。她希望添加 TAX_CODE 列，但该列不能为空。显然，当她向非空表添加非空列时，还必须指定默认值“XX”。因此，她编写了以下 SQL ：
alter table sales add tax_code varchar2(20) default 'XX' not null;
但她在这里停下了。SALES 表十分巨大，大约有 4 亿行。她知道，在执行该语句时，Oracle 会立即添加该列，但在将控制权返回给她之前将更新所有行中的值“XX”。更新 4 亿行不仅要花费很长时间，还要填充还原段、生成大量重做任务并产生极大的性能开销。因此，Jill 必须在“安静时段”（即，停机期间）才能进行此更改。Oracle 数据库 11g 中有更好的方法吗？
当然有。上述语句将不会对表中的所有记录执行更新。尽管对于列值将自动设为“XX”的新记录来说，这不是问题，但当用户选择现有记录的该列时，这将返回 NULL ，是吗？
实际上并非如此。当用户选择现有记录的列时，Oracle 将从数据字典获取默认值并将其返回给用户。这样，您就实现了一箭双雕：可以将一个新列定义为非空并具有默认值，同时不会导致任何重做和还原开销。真棒！
oracle 中针对大表增加有默认值的字段的操作方法
ALTER TABLE tab_large add col_new Number(9) Default 1;
该语句会对所有记录进行填值操作，对于现场tab_large中存在3000W 条左右
数据，该步骤需要执行数小时。
可以将该语句改为：
ALTER TABLE tab_large add col_new Number(9);
ALTER TABLE tab_large modify col_new number(9) default 1;
该语句只会对新增的记录进行填值操作，只需要数秒。
如果需要对原来的值进行操作，可以在升级后进行update 操作，这样能够节
省大量的升级时间。
修改表增加字段默认值default
2013-09-28 10:11:51 我来说两句 作者：海鲨Oracle
收藏 我要投稿
修改表增加字段默认值default
对个生产库的表增加1个字段. 字段类型是INT 型, 表数据有2千万条,
?
1 a lter table table_name add xxoo number(4) default 0 ;
因此 不仅要修改字典, 还要刷新全部数据.
1） 在ALTER sql中有带缺省值，ORACLE 会直接刷新全部的记录。
2） 在ALTER sql中没有带缺省值，ORACLE 只会影响到后来的记录。
?
1 alter table table_name add xxoo number(4) default null;
2 Table altered
3
4 Executed in 0.062 seconds
带有default null 就可以了
?
1 alter table table_name add xxoo number(4) default 0;
2
3 Table altered
4 Executed in 1.625 seconds
原来的话 要更新所有的行, 会导致UNDO 段占用
使用语句Alter table a add test number(10) default 0;更新一个大表中字段时，表有四个分区，数据达到几十亿行，增加一个字段竟然要几个小时的时间，修改语句加上Nologging ，怎么没有作用呢？去找是不是哪有锁了呢，使用语句 select *
from dba_locks where lock_id1=33784;发现Session_id为14的一直在执行，那么他在执行什么呢！查询一下吧。 使用语句：
?
select a.sid,a.username,c.SQL_TEXT from v$session a, dba_locks b,v$sqlarea c 2 where b.lock_id1=33784 and a.SID=b.session_id 3 and a.SQL_ADDRESS=c.ADDRESS; 1
哦，原来他在Update Test 字段值为0. 至此总结到，原来Alter 之后做的竟然是Update ，也明白了为什么Undo 表空间会爆涨。去掉Default 0，呵呵，很快就OK 了。
建议没有必要时慎用Default
添加具有默认值的列
尽管对该特性感到满意，Jill 还在考虑与第一个问题相关的另一个问题。她希望添加 TAX_CODE 列，但该列不能为空。显然，当她向非空表添加非空列时，还必须指定默认值“XX”。因此，她编写了以下 SQL ：
alter table sales add tax_code varchar2(20) default 'XX' not null;
但她在这里停下了。SALES 表十分巨大，大约有 4 亿行。她知道，在执行该语句时，Oracle 会立即添加该列，但在将控制权返回给她之前将更新所有行中的值“XX”。更新 4 亿行不仅要花费很长时间，还要填充还原段、生成大量重做任务并产生极大的性能开销。因此，Jill 必须在“安静时段”（即，停机期间）才能进行此更改。Oracle 数据库 11g 中有更好的方法吗？
当然有。上述语句将不会对表中的所有记录执行更新。尽管对于列值将自动设为“XX”的新记录来说，这不是问题，但当用户选择现有记录的该列时，这将返回 NULL ，是吗？
实际上并非如此。当用户选择现有记录的列时，Oracle 将从数据字典获取默认值并将其返回给用户。这样，您就实现了一箭双雕：可以将一个新列定义为非空并具有默认值，同时不会导致任何重做和还原开销。真棒！
oracle 中针对大表增加有默认值的字段的操作方法
ALTER TABLE tab_large add col_new Number(9) Default 1;
该语句会对所有记录进行填值操作，对于现场tab_large中存在3000W 条左右
数据，该步骤需要执行数小时。
可以将该语句改为：
ALTER TABLE tab_large add col_new Number(9);
ALTER TABLE tab_large modify col_new number(9) default 1;
该语句只会对新增的记录进行填值操作，只需要数秒。
如果需要对原来的值进行操作，可以在升级后进行update 操作，这样能够节
省大量的升级时间。