ORACLE 11G新特性之列添加操作

nainai1

案例回顾 　　在介绍ORACLE 11G新特性之前，我们先来看一个ORACLE10G的案例：
案例描述：
在正常业务期间，对一张大数据量的表添加了一个字段，并且采用default字段默认字段非空，从而导致表被锁定，undo资源被大量消耗，由于表涉及到业务数据，长时间锁定导致业务用户无法修改表，并且由于数据库资源消耗严重导致数据库已经不能正常操作，接下来客户主动掐掉了回话，导致数据库回滚，数据库表现为响应时间慢。回滚结束后数据库资源释放，系统正常。重新添加字段，默认字段为空，添加成功。
案例分析：
针对以上案例，分析如下几点：
首先，作为在正常的业务期间对表做大变更，特别是表结构的调整，这是不允许的，特别是一张大表。
其次添加字段时要考虑谨慎，不要添加相关的default参数，这也是引起本次事故的原因，导致该表被直接锁定，业务无法正常访问。
案例解决方法：
对于以上案例，如果避免以上问题，也就不会发生该事故。
最好办法应该是先添加列，在业务空闲的时候执行对列的修改操作：
SQL>>table_name add col_name data_type;
SQL> atler table table_name modify col_name default default_value;
 
本案例中的解决方案是ORACLE 11G之前的做法，在ORACLE  11G以后，oracle对于《添加修改列》有了新的方法。
　　 
　　
　　 
ORACLE 11G 新特殊描述　　
在ORACLE 11g中发出如下语句：
alter table table_name add col_namedata_type default 'XX' not null；            .  
ORACLE11g直接修改数据字典将已有行的default值更新，无需修改实际的表记录。
ORACLE11g会将默认值写到数据字典(sys.col$.default$)中，这样，当我们添加了一个新列时，对于以前原有的数据，oracle采用数据字典中的信息来表达，而对于新添加进来的列，则采用默认值写入，这样我们就可以将一个新列定义为非空并具有默认值，同时不会导致任何redo和undo开销，并且相应的空间也能得到节省，本次测试旨在对该新特性有一个基本的了解。  
　　新特性测试举例
　　
会话一：
会话二：
　　SQL> create table test(
　　A  number);
　　Table created.
　　SQL> insert into test values(1);
　　1 row created.
　　SQL> insert into test values(2);
　　1 row created.
SQL> conn frank/frank
Connected.
SQL> select * from test;
no rows selected
SQL> desc test;
 Name                                     Null?      Type
 ——— – ————————-
 A        NUMBER
SQL>>
ERROR at line 1:
ORA-00054: resource busy andacquire with NOWAIT specified or timeout expired
SQL>>
ERROR at line 1:
ORA-00054: resource busy andacquire with NOWAIT specified or timeout expired
 
无论是添加not null或者不添加，报错，由于会话1的事务还没有结束，表处于锁定状态，提交会话1
 
SQL> commit;
Commit complete.
 
 
SQL>>
Table>
此时我们可以在会话1查询：
SQL> select * from test;
         A          B
————————————         1         10
        2         10
 
　　 
测试例子分析　　
　　分析上述的例子，“会话一”默认字段被添加成功，我们在查询时，oracle内部其实对于已经存在的数据不再做修改，它将从数据字典(ecol$ 存储十六进制)获取默认值并将其返回给用户，我们可以通过ecol$转化十进制对应基表（col$）看一下存储的数据：
　　SQL> selectobject_name,object_id from dba_objects where object_name='TEST';
　　OBJECT_NAM  OBJECT_ID
　　—————— ——————
　　TEST            18458
　　SQL> selectOBJ#,COL#,NAME,DEFAULT$ from col$ where OBJ#=18458;
　　      OBJ#      COL# NAME       DEFAULT$
　　————————————————————————
　　     18458          1 A
　　     18458          2 B          10
　　通过10046事件跟踪以上SQL我们发现数据其实真正被插入的是ecol$基表：
　　我们可以通过10046事件看到，默认的default=10被写入了ecol$基表(ecol$基表是col$基表在11g中的体现，在11G中数据以16进制的形式插入在ecol$基表中，我们可以在col中查询到对应的10进制值)中，当我们做查询时。oracle其实是从这一部分取出值来应用。
　　 
ORACLE 11G新特性分析　　
　　在ORACLE 11G中，以下操作均可同时进行DML操作，而不用担心因为锁表而导致操作无法进行:
1.       添加一个非空的列（NOT NULL），并且默认default值，（11G中新特性的做法）
2.       添加一个空值，并且没有default参数（10g中添加列做法）
3.       添加一个虚拟列时候
　　在添加列时，使用not null和不使用not null是有区别的，我们分别对如下2个SQL进行10046事件跟踪分析：
ü  alter table test add(b numberdefault 10);
ü  alter table test add(b numberdefault 10 not null);
　　SQL语句：alter table test add(b number default 10);        
　　oracle是直接将数据插入到原表中，也就是没有应用新特性:
　　PARSING IN CURSOR#47128791364856 len=24 dep=1 uid=0 oct=6 lid=0 tim=1334559603376483hv=1128945042 ad='760c31d8' sqlid='87stdfj1nnpck'
　　update"TEST" set "B"=10（可以看到是直接插入update语句）
　　SQL语句：alter table test add(b number default 10 not null);       
　　应用了ORACLE 11G的新特性:
　　PARSING IN CURSOR#47316071119904 len=37 dep=1 uid=0 oct=2 lid=0 tim=1334559827310374hv=4050124187 ad='70158410' sqlid='cqrnq6vsqgzcv'
　　insert intoecol$ values (:1, :2, :3)（插入到eco$基表中）
　　 
视图与列添加的关系　　
　　视图是基于表格而存在的，当表格添加列后，相应的视图是否也自动添加列呢？
　　Oracle 11g针对该新特性明确解释，对于针对该表的视图，如果在添加列后，那么相应的视图并不会自动的添加新列。视图查询结果仍是原始数据
　　测试如下:
SQL>select * from test2;
         A          B
—————————
         1         10
         2         10
　　我们创建视图：
SQL>create or replace view test2_vw as  select* from test2;
Viewcreated.
　　那么我们查询视图：
SQL>select * from test2_vw;
         A          B
 —————————
         1         10
         2         10
　　 
　　而当我们对原表创建新列：
SQL>alter table test2 add(d varchar2(10) default 'abcde' not null);
　　此时我们再查询原表：
SQL>select * from test2;
         A          B          D            
      --------- ----------- --      -----------       
         1         10        abcde          
         2         10     abcde            
　　而我们查询视图：
SQL>select * from test2_vw;
         A          B
 ————————————
         1         10
         2         10
返回依旧是原表结果。
　　结论：ORACLE 11g 后可以直接修改数据字典将已有行的default值更新而无需修改实际的表记录，他已经可以做到实时应用而不会因为表很大而造成性能问题。
　　
　　------------------------------------------------------------------------------------

原博客地址：http://blog.itpub.net/23732248/
原作者：应以峰 (frank-ying)
-------------------------------------------------------------------------------------