oracle中dual表相关的知识

1、  根据查看DUAL是什么：

SELECT T.OWNER,T.OBJECT_NAME,T.OBJECT_TYPE,T.STATUS

FROM DBA_OBJECTS T

WHERE T.OBJECT_NAME LIKE '%DUAL%'

     OWNER  OBJECT_NAME    OBJECT_TYPE     STATUS

1     SYS DUAL     TABLE    VALID

2     PUBLIC  DUAL     SYNONYM    VALID

可以看出存在sys的dual表，给它建的公共同义词名为dual

2、《SQL Reference》上的解释

DUAL is a table automatically created by Oracle along with the data dictionary.

DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all

users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one

row with a value ’X’. Selecting from the DUAL table is useful for computing a

constant expression with the SELECT statement. Because DUAL has only one row,

the constant is returned only once. Alternatively, you can select a constant,

pseudocolumn, or expression from any table, but the value will be returned as

many times as there are rows in the table.

2、  结构：

SQL> desc dual

Name  Type        Nullable Default Comments

----- ----------- -------- ------- --------

DUMMY VARCHAR2(1) Y                        

SQL> select sysdate from dual;

SYSDATE

-----------

2009-1-8 10

4、一个有趣的测试：

查询：

SQL> SELECT * FROM DUAL;

DUMMY

-----

X

1

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2

删除：

SQL> delete from dual;

1 row deleted

       查询：

SQL> select * from dual;

DUMMY

-----

1

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2

5、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

6.在删除时使用编辑数据才能保证查到最终的数据。

       使用delete和select for update只能锁定到X行，其余的行无法查询到

7、用来测试应用服务器是否与数据库保持连接：

Dual has another use when you try to test if you are connected with the DB remotely by JDBC in your AS such as Weblogic. This is a common table for any schema.