不要亂動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 也沒有再出錯了。