不要乱动dual表

      今天在itpub上看到一个帖子，在删除表的时候出现问题：

SQL> drop table t4;
drop table t4
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

      通常来说 drop table 失败的原因都是权限不足，但这次却报递归SQL出错，咋看让人摸不着头脑，随着版主的分析终于揭开了这个谜底——动了 dual 表。

      以下是我的实验过程重演这个问题。

 

      首先以一个测试用户（我这里是scott）创建一个普通表：

scott@ora10g(oracle01) SQL> create table tab01(f01 int); 

Table created.

      以 sys 用户向 dual 插入一条数据，并提交：

sys@ora10g(oracle01) SQL> insert into dual values('Y');

1 row created.

sys@ora10g(oracle01) SQL> commit; 

Commit complete.

      由于一些内部的实现，以下是 dual 比较怪异的情况：

sys@ora10g(oracle01) SQL> select * from dual ; 

D
-
X

sys@ora10g(oracle01) SQL> select count(*) from dual  ;      

  COUNT(*)
----------
         1

sys@ora10g(oracle01) SQL> begin 
  2  for x in (select * from dual) loop
  3    dbms_output.put_line(x.dummy);
  4  end loop;
  5  end;
  6  /
X
Y

      可以看到只有匿名过程返回的结果才是两条数据。

      此时以scott用户删除之前创建的测试表 tab01，即可触发帖子里面的问题：

scott@ora10g(oracle01) SQL> drop table tab01 ; 
drop table tab01
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

      做一个级别为12的10046跟踪，尝试找出问题：

scott@ora10g(oracle01) SQL> alter session set tracefile_identifier='drop_table_error'; 

Session altered.

scott@ora10g(oracle01) SQL> alter session set events'10046 trace name context forever,level 12';

Session altered.

scott@ora10g(oracle01) SQL> drop table tab01 ; 
drop table tab01
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

scott@ora10g(oracle01) SQL> alter session set events '10046 trace name context off';

      经过 tkprof 处理后，可以找到为一个引用了 dual 的地方：

select dummy 
from
 dual where  ora_dict_obj_type = 'TABLE'
......
......
Rows     Row Source Operation
-------  ---------------------------------------------------
      2  FILTER  (cr=212 pr=2 pw=0 time=32261 us)
      2   TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us)

       删除了 dual 表中“多余”行后却是：

select dummy 
from
 dual where  ora_dict_obj_type = 'TABLE'
......
......
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FILTER  (cr=107 pr=0 pw=0 time=17082 us)
      1   TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us)

      当然 drop table 也没有再出错了。