ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11g中关于表添加字段default属性研究

11g中关于表添加字段default属性研究

原创 Linux操作系统 作者:yingyifeng306 时间:2012-01-13 21:24:47 0 删除 编辑

在客户现场碰到一个问题,客户在业务期间,对一张非常大的表添加了一个字段,并且采用default字段默认非空,从而导致表被锁定,undo被大量消耗,资源被大量占用,接下来客户主动掐掉了回话,导致数据库回滚,数据库直接无法连接。回滚结束后数据库资源释放,系统正常。重新添加字段,默认字段为空,添加成功。回想了一下,其实有几点是不正确的,首先,最好不要在正常的业务期间对表做大变更,特别是一张大表。其次添加字段时要考虑谨慎,不要添加相关的default参数,这也是引起本次事故的原因,导致该表被直接锁定,业务无法正常访问。最好的办法应该是先添加列,在业务空闲的时候执行对列的修改:alter table table_name add col_name data_type;
atler table table_name modify col_name default default_value;
在后来我想如果回滚时间非常久,那数据库资源一直得不到释放,那数据库不是一直无法连接了,于是在网上找找看看有什么相关的应急办法。找到11G新特性中关于这个问题的解决办法。
在11g中,可以直接修改数据字典将已有行的default值更新 而无需修改实际的表记录。在10g中一旦添加某表并使用default参数,数据库就会锁定该表,并更新所有的行。而在11g中
我们采用alter table table_name add col_name data_type default 'XX' not null;时,oracle会将默认值写到数据字典(sys.col$.default$)中,这样,当我们添加了一个新列时,对于以前原有的数据,oracle采用数据字典中的信息来表达,而对于新添加进来的列,则采用默认值写入,这样我们就可以将一个新列定义为非空并具有默认值,同时不会导致任何重做和undo开销。本次测试旨在对该新特性有一个基本的了解。不做深入分析。
testing:
会话一:
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);
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);

(回话被hang住)

会话一:
SQL> commit;
Commit complete.
SQL>
会话二:
SQL> alter table test add(b number default 10 not null);
Table altered.

此时我们可以在回话一种查询一下:
SQL> select * from test;
         A          B
———- ———-
         1         10
         2         10
默认字段被添加进入,我们在查询时,oracle内部其实对于已经存在的数据是不在做修改,它将从数据字典(sys.col$.default$)获取默认值并将其返回给用户,我们可以看一下存储的数据:
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

我们可以看到,默认的default=10被写入了ecl$基表中,当我们做查询时。oracle其实是从这一部分取出值来应用。

本次数据库实验环境是11gR2 我们可以看出如果有其他事务未作提交,那么当我添加具有default的值的列式如果是不是not null 的新列,是会报ORA-00054资源繁忙错误的,而如果添加not null时,则数据库会hang住。(这和11gR1不一样,在11gR1(11.1.0.6)中测试时,是可以成功的,可能对于R2做了改变),此时我们提交回话一的事务,则回话2获得资源,会话也成功。
也就是说11g 后可以直接修改数据字典将已有行的default值更新 而无需修改实际的表记录,他已经可以做到实时应用而不会因为表很大而造成性能问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23732248/viewspace-714916/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITpub论坛高可用版主,擅长研究Oracle 内部原理、新特性、高可用和性能调优等,多年来一直保持着对新事务旺盛的求知欲。热切关注 Oracle 和其它相关技术

注册时间:2011-10-12

  • 博文量
    64
  • 访问量
    1348714