Oracle Foundation of Dual

Excerpt from: http://www.linuxidc.com/Linux/2012-07/66288.htm

1th
Dual is not an abbreviation but a complete word. Dual noun means logarithm, when doing adjectives refers to two heavy, two yuan.

2nd
The dual table in Oracle is a single-row, single-column virtual table.

3rd
The dual table is a table that Oracle automatically creates with the data dictionary, which has only 1 columns dummy, the data type is VERCHAR2 (1), and the dual table has only one data ' X ', and Oracle has an internal logic guarantee that there is always only one data in the dual table.

4th
The dual table is primarily used to select system variables or to find the value of an expression.

5th
A simple example of the most common select Sysdate from daul
Oracle's Select syntax is limited to select * | [Column1 [as ALIAS1], column2 [as ALIAS2]] From table
So without a table name there is no way to query, and the time date is not stored in any table, so the concept of this dual virtual table is introduced.

Use of the dual table

Dual is an actual table in Oracle that can be read by any user, often in a SELECT statement block without a target table.

View current Connected Users
Select User from Dual;
SYSTEM

View current date, time
Select Sysdate from dual;
2012-1-24 on 1

Date conversion
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;
2012-01-24 15:02:47

Use as a calculator
Select 1+2 from dual;
3

CREATE VIEW sequence values
Create sequence AAA increment by 1 start with 1;
Select Aaa.nextval from dual;
1

Select Aaa.currval from dual;
1

Dual is a row-by-column table, and if you perform insert,delete,truncate operations inside, it can cause many programs to go wrong. The results are also different for tools such as sql*plus, PL/SQL Dev, and so on. False we insert want to dual insert a piece of data, what will be the result?

INSERT into dual values (' Y ');

SELECT * from dual;
DUMMY
-----
X
Y

Select Sysdate from dual;
Sysdate
-----------
2012-07-15
2012-07-15

This is the time to return two records, which can cause problems. Oracle throws a Too_many_rows (ORA-01422) exception in a stored procedure that obtains time or other information by using the Select Sysdate into V_sysdate from dual.

Therefore, it is very dangerous for the system to ensure that there is only one record in the dual table and that the Update,insert,delete permission of the dual table cannot be freely released.

If you accidentally remove the dual, it can be restored.

Oracle Foundation of Dual