關于Oracle的Dual表

有朋友問到關于Oracle的Dual表問題，并且提到了Tom的一個鏈接：http://asktom.oracle.com/pls/asktom/f？p=100：11：0：：：：P11_QUESTION_ID：1562813956388 很多人關心的是Dual的內部實現，這Oracle自然是不會披露的，不過我們可以從一些有限的資料獲得關于Dual的印象：

There is internalized code that makes this happen. Code checks that ensure that a table scan of SYS.DUAL only returns one row. Svrmgrl behaviour is incorrect but this is now an obsolete product.

The base issue you should always remember and keep is: DUAL table should always have 1 ROW. Dual is a normal table with one dummy column of varchar2(1).This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other prebuilt or application functions. If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception. If DUAL has more than 1 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception.

So DUAL should ALWAYS have 1 and only 1 row.

前兩句話最為關鍵，實際上我們也容易猜到，Oracle通過內部代碼來實現對于DUAL的訪問和控制，并且通過Internal Code使得這個表與眾不同。

Tom提到在Close了Database之后，可以看到這個表的內存地址及展現，這說明這個表的結構并不單純：

sql> select * from dual; D-X

sql> alter database close;

Database altered.

sql> select * from dual;

ADDR; INDX;INST_ID D-------- ---------- ---------- -0A4F8858; 0; 1 X

注意不要更改Dual表的內容，否則可能引起數據庫的問題。

如果該表意外刪除，可以通過設置初始化參數replication_dependency_tracking = FALSE，重啟數據庫來重建該表。