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

浏览数：24 / 时间：2015年06月12日

案例回顾

在介绍ORACLE 11G新特性之前，我们先来看一个ORACLE 10G的案例：

案例描述：

在正常业务期间，对一张大数据量的表添加了一个字段，并且采用default字段默认字段非空，从而导致表被锁定，undo资源被大量消耗，由于表涉及到业务数据，长时间锁定导致业务用户无法修改表，并且由于数据库资源消耗严重导致数据库已经不能正常操作，接下来客户主动掐掉了回话，导致数据库回滚，数据库表现为响应时间慢。回滚结束后数据库资源释放，系统正常。重新添加字段，默认字段为空，添加成功。

案例分析：

针对以上案例，分析如下几点：

首先，作为在正常的业务期间对表做大变更，特别是表结构的调整，这是不允许的，特别是一张大表。

其次添加字段时要考虑谨慎，不要添加相关的default参数，这也是引起本次事故的原因，导致该表被直接锁定，业务无法正常访问。

案例解决方法：

对于以上案例，如果避免以上问题，也就不会发生该事故。

最好办法应该是先添加列，在业务空闲的时候执行对列的修改操作：

SQL> alter table 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_name data_type default ‘XX‘ not null； .

ORACLE 11g直接修改数据字典将已有行的default值更新，无需修改实际的表记录。

ORACLE 11g会将默认值写到数据字典(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> alter table test add(b number default 10); ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter table test add(b number default 10 not null); ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 无论是添加not null或者不添加，报错，由于会话1的事务还没有结束，表处于锁定状态，提交会话1

SQL> commit; Commit complete.
	SQL> alter table test add(b number default 10 not null); Table altered.
此时我们可以在会话1查询： SQL> select * from test; A B ———————————— 1 10 2 10

测试例子分析

分析上述的例子，“会话一”默认字段被添加成功，我们在查询时，oracle内部其实对于已经存在的数据不再做修改，它将从数据字典(ecol$ 存储十六进制)获取默认值并将其返回给用户，我们可以通过ecol$转化十进制对应基表（col$）看一下存储的数据：

SQL> select object_name,object_id from dba_objects where object_name=‘TEST‘;

OBJECT_NAM OBJECT_ID

—————— ——————

TEST 18458

SQL> select OBJ#,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 number default 10);

ü alter table test add(b number default 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=1334559603376483 hv=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=1334559827310374 hv=4050124187 ad=‘70158410‘ sqlid=‘cqrnq6vsqgzcv‘

insert into ecol$ 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;

View created.

那么我们查询视图：

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)
-------------------------------------------------------------------------------------