当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

create table as select * from dba_objects为什么会有新建表的信息

上周五技术分享,nulldb(张中靖)兄提出了为什么在create table xx as select * from dba_objects时,新建的xx表会有新建的xx表的信息,也就是比如在操作前,dba_objects视图有10000条记录,在xx表创建完成时,xx表和dba_objects视图中都是10001条记录,在xx表中可以查询到xx表的信息,这在某种程度上违反了ORACLE的一致性,这个话题我测试了很久,以下是测试过程:

首先设置trace文件字符串,方便查找trace文件。

SQL> ALTER SESSION SET tracefile_identifier=wind;
会话已更改。

打开10046,追踪当前会话。

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
会话已更改。

创建测试表test。

SQL> create table test as select * from dba_objects;
表已创建。

关闭10046追踪。

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
会话已更改。

对比新建的test表和dba_objects视图数据。

SQL> select count(*) from dba_objects;
  COUNT(*)
----------
     65611
SQL> select count(*) from test;
  COUNT(*)
----------
     65611
SQL> select object_name from test where object_name='TEST';
OBJECT_NAME
------------
TEST

竟然一样多!按照oracle的一致性原则,test表应该比dba_objects少一条记录才对(不应该包含test表的信息才对),看下trace文件,在建表的时候,ORACLE都干了哪些事(因为建表的时候,oracle要将新表的数据插入到数据字典,所以主要看下递归调用的insert操作)。

2015-11-04-00001

在建表的时候,oracle一共向obj$、seg$、tab$、col$数据字典中插入数据,那么根据dba_segments、dba_tables建表会不会也多一条新表的数据呢?经过测试(测试部分省略,有兴趣的可以自己测下),根据dba_segments、dba_tables创建新表都会比这两张视图少一条记录,也就是不包含新建表的记录,这符合oracle的一致性,是正常的。

那么为什么偏偏使用dba_objects视图做基表创建表时就包含这张表的信息呢?分析了好久都没弄明白,不过可以肯定的是,load as select的时候,一定是在insert into obj$之后,在insert into seg$/tab$之前。

经过老杨(杨廷琨)和kamus(张乐奕)测试分析,得出了以下的结论。

  1. create table as select(ctas)是一个完整的事务。
  2. 在创建表时,insert into obj$会最先运行。
  3. 然后load as select向新表插入数据,此时因为dba_objects视图中已经有了test表的信息,所以在新创建的test表中,可以查到test表的信息,也就是dba_objects和test表的数据是一样的。
  4. 因为load as select操作需要分配test的段信息,所以紧接着insert into seg$操作。
  5. 此时如果create table as select操作失败,整个事务回滚,obj$、seg$都不存在test表的记录。如果create成功,再insert into tab$操作。
  6. 所以,只有create table as select * from dba_objects操作会包含新表的记录,from dba_segments和dba_tables时都不会在新表中看到自己的记录。

————————————-end—————————————-

 

本文固定链接: http://www.dbdream.com.cn/2013/08/create-table-as-select-from-dba_objects%e4%b8%ba%e4%bb%80%e4%b9%88%e4%bc%9a%e6%9c%89%e6%96%b0%e5%bb%ba%e8%a1%a8%e7%9a%84%e4%bf%a1%e6%81%af/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2013年08月28日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: create table as select * from dba_objects为什么会有新建表的信息 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

create table as select * from dba_objects为什么会有新建表的信息:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter