關於Oracle的Dual表

　　有朋友問到關於Oracle的Dual表問題並且提到了Tom的一個鏈接？p=P_QUESTION_ID

　　很多人關心的是Dual的內部實現這Oracle自然是不會披露的不過我們可以從一些有限的資料獲得關於Dual的印象

　　 There is internalized code that makes this happen Code checks that ensure that a table scan of SYSDUAL 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 ROW Dual is a normal table with one dummy column of varchar()
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 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception

　　So DUAL should ALWAYS have and only 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

AF                    X

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

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

From:http://tw.wingwit.com/Article/program/Oracle/201311/18703.html