有朋友问到关于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重启数据库来重建该表
